value limitation in an IN clause Oracle

Question

I work for a company that has a DW - ETL setup. I need to write a query that looks for over 2500+ values in an WHEN - IN clause and also over 1000+ values in a WHERE - IN clause. Basically it would look like the following:

SELECT
    ,user_id
    ,CASE WHEN user_id IN ('user_n', +2500 user_[n+1] ) THEN 1
    ELSE 0
    ,item_id
FROM user_table
    WHERE item_id IN ('item_n', +1000 item_[n+1] );

As you probably already know PL/SQL allows a maximum of 1000 values in an IN clause, so I tried adding OR - IN clauses (as suggested in other stackoverflow threads):

SELECT
    ,user_id
    ,CASE WHEN user_id IN ('user_n', +999 user_[n+1] )
     OR user_id IN ('user_n', +999 user_[n+1] )
     OR user_id IN ('user_n', +999 user_[n+1] ) THEN 1
     ELSE 0 END AS user_group
    ,item_id
FROM user_table
    WHERE item_id IN ('item_n', +999 item_[n+1] )
    OR item_id IN ('item_n', +999 item_[n+1] );

NOTE: i know the math is erroneous in the examples above, but you get the point

The problem is that queries have a maximum executing time of 120 minutes and the job is being automatically killed. So I googled what solutions I could find and it seems Temporary Tables could be the solution I'm looking for, but with all honesty none of the examples I found is clear enough on how to include the values I want in the table and also how to use this table in my original query. Not even the ORACLE documentation was of much help.

Another potential problem is that I have limited rights and I've seen other people mention that in their companies they don't have the rights to create temporary tables.

Some of the info I found in my research:

ORACLE documentation

StackOverflow thread

[StackOverflow thread 2]

Another solution I found was using tuples instead, as mentioned in THIS thread (which I haven't tried) because as another user mentions performance seems greatly affected.

Any guidance on how to use a Temporary Table or if anyone has another way of dealing with this limitation would be greatly appreciated.

Answer 1

Create a global temporary table so no undo logs are created

CREATE GLOBAL TEMPORARY TABLE <table_name> (
<column_name>  <column_data_type>,
<column_name>  <column_data_type>,
<column_name>  <column_data_type>)
ON COMMIT DELETE ROWS;

then depending on how the user list arrives import the data into a holding table and then run

select 'INSERT INTO global_temporary_table <column> values '
|| holding_table.column
||';' 
FROM holding_table.column; 

This gives you insert statements as output which you run to insert the data.

then

SELECT  <some_column>
FROM <some_table>
WHERE <some_value> IN
(SELECT <some_column> from <global_temporary_table>

Answer 2

Use a collection:

CREATE TYPE Ints_Table AS TABLE OF INT;
CREATE TYPE IDs_Table AS TABLE OF CHAR(5);

Something like this:

SELECT user_id,
       CASE WHEN user_id MEMBER OF Ints_Table( 1, 2, 3, /* ... */ 2500 )
            THEN 1
            ELSE 0
            END
      ,item_id
FROM  user_table
WHERE item_id MEMBER OF IDs_table( 'ABSC2', 'DITO9', 'KMKM9', /* ... */  'QD3R5' );

Or you can use PL/SQL to populate a collection:

VARIABLE cur REFCURSOR;

DECLARE
  t_users Ints_Table;
  t_items IDs_Table;

  f       UTL_FILE.FILE_TYPE;
  line    VARCHAR2(4000);
BEGIN
  t_users.EXTEND( 2500 );
  FOR i = 1 .. 2500 LOOP
    t_users( t_users.COUNT ) := i;
  END LOOP;

  // load data from a file
  f := UTL_FILE.FOPEN('DIRECTORY_HANDLE','datafile.txt','R');
  IF UTL_FILE.IS_OPEN(f) THEN
  LOOP
    UTL_FILE.GET_LINE(f,line);
    IF line IS NULL THEN EXIT; END IF;
    t_items.EXTEND;
    t_items( t_items.COUNT ) := line;
  END LOOP;

  OPEN :cur FOR
    SELECT user_id,
           CASE WHEN user_id MEMBER OF t_users
                THEN 1
                ELSE 0
                END
          ,item_id
    FROM  user_table
    WHERE item_id MEMBER OF t_items;
END;
/

PRINT cur;

Or if you are using another language to call the query then you could pass the collections as a bind value (as shown here).