Oracle limitation: large "where x in (....)'

gruberc · **Posted:** Tue Jan 27, 2004 12:24 pm

Hello!

There is a problem with queries with named parameters that consist of big lists with Oracle. An example:

Code:

Collection idList = new LinkedList();
for (int i=0; i<1001; i++) {
    idList.add(new Integer(i));
}
Query query = session.createQuery("from Geneticelem as geneticelem where geneticelem.id in (:ids)");
query.setParameterList("ids", idList, Hibernate.INTEGER);
Iterator geneticelems = query.iterate();

This code results in an ORA-01795 exception, because not more than 1000 elements are allowed for "in".

In OJB, this is solved by splitting the "in" condition is split up in chunks: http://db.apache.org/ojb/api/org/apache/ojb/broker/query/Criteria.html#addIn(java.lang.String,%20java.util.Collection)

Can something like this also be implemented in Hibernate? I know, it's a limitation that will rarely be encountered, but still it's a limitation.

Thanks,
Christian

emmanuel · **Posted:** Tue Jan 27, 2004 7:59 pm

Please submit to JIRA as an improvement.

steve · **Posted:** Wed Jan 28, 2004 12:06 am

FWIW, the limit on the number of element allowed in the IN clause is configurable in Oracle.

wessoltanpur · **Joined:** Wed Nov 16, 2005 10:41 am **Posts:** 1

Steve,

Could you please give me a clue as to how I can configure the IN clause limit?

Cheers,
Wes

steve · **Posted:** Thu Nov 17, 2005 11:27 am

It is done through a setting in your init.ora file, but I forget the name of the setting.

gruberc · **Posted:** Thu Nov 17, 2005 11:39 am

I'm not sure if this option really exists. At least, I did not find anything about that. Besides that, Hibernate would then rely on a specific setting in the Oracle installation, which is not always feasible.
By the way, the Jira issue HB-655 that I issued was closed as "Won't fix" without any further comments last year...

Christian

snpesnpe · **Joined:** Sat Jun 12, 2004 4:49 pm **Posts:** 915

no, there isn't this option in oracle
you can see all options in sqlplus with
show parameter
and this option doesn't exists

I don't know your design, but it can be good use EXISTS instead IN for limit constraint, but and for performance reason

for your example it is enough

from Geneticelem as geneticelem where geneticelem.id between 0 and 1001

but it is example only (probably)

another solution is that you split list to more chunk and add OR expression - it is for specific
query only and it isn't to hard

regards

gruberc · **Posted:** Fri Nov 18, 2005 3:39 am

Yes, there are a few ways to circumvent this problem (use more than one query, or connect more IN lists with OR). As the problem is more than a year old, I already use them by myself in my application. But I would have liked this issue to be handled by Hibernate instead of increasing the code size and complexity in the application. And as I mentioned, OJB did this job very well. But now that Hibernate 3.1 will be released soon, I don't have much hope for this to happen...

Christian

snpesnpe · **Joined:** Sat Jun 12, 2004 4:49 pm **Posts:** 915

Christian,
It is really egzotic request.I work with relational database (mostly oracle) from 1985 and limit was stronger before (in rel 7 was 256(, but i has problem never
use database for bigger lists - i think that in with big list is slow, too

baliukas · **Posted:** Fri Nov 18, 2005 8:23 am

It must be better to use subquery or join than to send list from server to client and back again, this limitation makes sence and it exists for a very good reason.

jscheffler · **Joined:** Tue Nov 15, 2005 1:54 pm **Posts:** 6

see also
http://forum.hibernate.org/viewtopic.php?t=950246&highlight=

It's not Oracle only ;-)

Sreenivas Shreenath · **Posted:** Fri Apr 27, 2007 8:50 am

We implemented a solution for this problem by implementing the Criterion interface separately:

Code:

public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
//      String[] columns = criteriaQuery.getColumnsUsingProjection(criteria, propertyName);
//      String singleValueParam = StringHelper.repeat( "?, ", columns.length-1 )  + "?";
//      if ( columns.length>1 ) singleValueParam = '(' + singleValueParam + ')';
//      String params = values.length>0 ?
//         StringHelper.repeat( singleValueParam + ", ", values.length-1 ) + singleValueParam :
//            "";
//         String cols = StringHelper.join(", ", columns);
//         if ( columns.length>1 ) cols = '(' + cols + ')';
//         return cols + " in (" + params + ')';
      String[] columns = criteriaQuery.getColumnsUsingProjection(criteria, propertyName);
      String singleValueParam = StringHelper.repeat( "?, ", columns.length-1 )  + "?";
      if ( columns.length>1 ) singleValueParam = '(' + singleValueParam + ')';
      
      /**
       * If the values are greater than 1000,
       * write separate in expressions with 
       * or conditions.
       */
      final StringBuffer totalIn = new StringBuffer();
      int iStart = 0;
      if (values.length > 999) {
         while (iStart != values.length) {
            String cols = StringHelper.join(", ", columns);
            if ( columns.length>1 ) cols = '(' + cols + ')';
            String params = StringHelper.repeat( singleValueParam + ", ", Math.min(1000, values.length - iStart) - 1 ) 
                                                               + singleValueParam;
            totalIn.append(cols + " in (" + params + ')');

            iStart += Math.min(1000, values.length - iStart);
            
            if (iStart != values.length) {
               totalIn.append(" or ");
            }
         }
         return "("+totalIn.toString()+")";
      }
      
      String params = values.length>0 ?
      StringHelper.repeat( singleValueParam + ", ", values.length-1 ) + singleValueParam :
         "";
      String cols = StringHelper.join(", ", columns);
      if ( columns.length>1 ) cols = '(' + cols + ')';
      return cols + " in (" + params + ')';
   }

It would be good if we could incorporate something like this in the Restrictions class in the in() method itself. I will also post it in JIRA.

prabhjyoth · **Posted:** Mon Mar 24, 2008 7:20 am

I have used multiple in expressions joined by or to solve this problem. But this is giving me performance issues. What is the best solution to this problem. I have heard about using sub-select to tackle this problem but have no clue how it is to be used.

Pls also suggest the best way to handle this problem