value limitation in an IN clause Oracle
<p>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 <code>WHEN - IN</code> clause and also over 1000+ values in a <code>WHERE - IN</code> clause. Basically it would look like the following:</p>

<pre><code>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] );
</code></pre>

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

<pre><code>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] );
</code></pre>

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

<p>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.</p>

<p>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.</p>

<p>Some of the info I found in my research:</p>

<p><a href="https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables003.htm#i1006400" rel="nofollow noreferrer">ORACLE documentation</a></p>

<p><a href="https://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause">StackOverflow thread</a></p>

<p><a href="https://stackoverflow.com/questions/4722220/sql-in-clause-1000-item-limit?lq=1">[StackOverflow thread 2]</a></p>

<p>Another solution I found was using tuples instead, as mentioned in <a href="https://stackoverflow.com/questions/4722220/sql-in-clause-1000-item-limit">THIS thread</a> (which I haven't tried) because as another user mentions performance seems greatly affected.</p>

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