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).

Answer 3

In PL/SQL you could use a collection type. You could create your own like this:

create type string_table is table of varchar2(100);

Or use an existing type such as SYS.DBMS_DEBUG_VC2COLL which is a table of VARCHAR2(1000).

Now you can declare a collection of this type for each of your lists, populate it, and use it in the query - something like this:

declare
   strings1 SYS.DBMS_DEBUG_VC2COLL := SYS.DBMS_DEBUG_VC2COLL();
   strings2 SYS.DBMS_DEBUG_VC2COLL := SYS.DBMS_DEBUG_VC2COLL();

   procedure add_string1 (p_string varchar2) is
   begin
      strings1.extend();
      strings1(strings.count) := p_string;
   end;

   procedure add_string2 (p_string varchar2) is
   begin
      strings2.extend();
      strings2(strings2.count) := p_string;
   end;
begin
   add_string1('1');
   add_string1('2');
   add_string1('3');
   -- and so on...
   add_string1('2500');

   add_string2('1');
   add_string2('2');
   add_string2('3');
   -- and so on...
   add_string2('1400');

   for r in (
    select user_id
         , case when user_id in table(strings2) then 1 else 0 end as indicator
         , item_id
      from user_table
     where item_id in table(strings1)
    )
   loop
      dbms_output.put_Line(r.user_id||' '||r.indicator);
   end loop;
end;
/

Answer 4

You can use below example to understand Global temporary tables and the type of GTT.

CREATE GLOBAL TEMPORARY TABLE GTT_PRESERVE_ROWS (ID NUMBER) ON COMMIT PRESERVE ROWS;
INSERT INTO GTT_PRESERVE_ROWS VALUES (1);
COMMIT;
SELECT * FROM GTT_PRESERVE_ROWS;
DELETE FROM GTT_PRESERVE_ROWS;
COMMIT;
TRUNCATE TABLE GTT_PRESERVE_ROWS;
DROP TABLE GTT_PRESERVE_ROWS;--WONT WORK IF YOU DIDNOT TRUNCATE THE TABLE OR THE TABLE IS BEING USED IN SOME OTHER SESSION

CREATE GLOBAL TEMPORARY TABLE GTT_DELETE_ROWS (ID NUMBER) ON COMMIT DELETE ROWS;
INSERT INTO GTT_DELETE_ROWS VALUES (1);
SELECT * FROM GTT_DELETE_ROWS;
COMMIT;
SELECT * FROM GTT_DELETE_ROWS;
DROP TABLE GTT_DELETE_ROWS;

However as you mentioned you receive the input in an excel file so you can simply create a table and load data in that table. Once the data is loaded you can use the data in IN clause of your query.

select * from employee where empid in (select empid from temptable);

Answer 5

 create temporary table userids (userid int);
 insert into userids(...)

then a join or in subquery

 select ...
 where user_id in (select userid from userids);
 drop temporary table userids;