title: Most efficient way to deal with ORA-01795:maximum number of expressions in a list is 1000 in hibernate
body: <p>I have to perform a select on which I have more than 1000 elements via hibernate, and then I received the error "ORA-01795:maximum number of expressions in a list is 1000" when I'm using the Oracle brand.</p>
  
  <pre><code>SELECT * FROM matable WHERE column IN (?,?,...) (&gt;1000 items) 
  </code></pre>
  
  <p>I found many solutions : 
  Split the list with OR</p>
  
  <blockquote>
  <pre><code>where A in (a,b,c,d,e,f)
  </code></pre>
    
    <p>becomes</p>
  
  <pre><code>where (A in (a,b,c) OR a in (d,e,f)) ...
  </code></pre>
  </blockquote>
  
  <p>Create a table with UNION ALL</p>
  
  <blockquote>
  <pre><code>SELECT * FROM maintable
  JOIN (
      SELECT v1 a FROM DUAL UNION ALL
      SELECT v2 a FROM DUAL UNION ALL
      SELECT v3 a FROM DUAL UNION ALL
      ...
      SELECT v2000 a FROM DUAL) tmp
  
      on tmp.a = maintable.id
  </code></pre>
  </blockquote>
  
  <p>Using tuples to get rid of the limit</p>
  
  <blockquote>
    <p><code>where (column,0) in ((1,0),(2,0),(3,0),(4,0), ... ,(1500,0))</code></p>
  </blockquote>
  
  <p>Using a temporary table..</p>
  
  <blockquote>
  <pre><code>where A in SELECT item FROM my_temporary_table
  </code></pre>
  </blockquote>
  
  <p>References <a href="https://stackoverflow.com/questions/5253851/ora-01795-maximum-number-of-expressions-in-a-list-is-1000">here</a> and <a href="https://stackoverflow.com/questions/9682646/caused-by-java-sql-sqlexception-ora-01795-maximum-number-of-expressions-in-a">there</a> and also <a href="https://community.oracle.com/thread/1089625" rel="nofollow noreferrer">there</a>.</p>
  
  <p>My question is the following : what is the best practice to deal with this issue? By best practice I mean the most performant, but not only for Oracle;  if I use hibernate, I don't want to create and manage a different code for each brand of database (I'm concerned by Oracle, MS SQL and PostGre only).</p>
  
  <p>My first reaction would have been to use a temporary table, but I don't know what has the most impact. </p>