-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 13 posts ] 
Author Message
 Post subject: Oracle limitation: large "where x in (....)'
PostPosted: Tue Jan 27, 2004 12:24 pm 
Beginner
Beginner

Joined: Wed Jan 21, 2004 10:15 am
Posts: 24
Location: Munich, Germany
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


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jan 27, 2004 7:59 pm 
Hibernate Team
Hibernate Team

Joined: Sun Sep 14, 2003 3:54 am
Posts: 7256
Location: Paris, France
Please submit to JIRA as an improvement.

_________________
Emmanuel


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jan 28, 2004 12:06 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 3:00 pm
Posts: 1816
Location: Austin, TX
FWIW, the limit on the number of element allowed in the IN clause is configurable in Oracle.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Nov 16, 2005 10:45 am 
Newbie

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


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 17, 2005 11:27 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 3:00 pm
Posts: 1816
Location: Austin, TX
It is done through a setting in your init.ora file, but I forget the name of the setting.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 17, 2005 11:39 am 
Beginner
Beginner

Joined: Wed Jan 21, 2004 10:15 am
Posts: 24
Location: Munich, Germany
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


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 17, 2005 2:47 pm 
Expert
Expert

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


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 18, 2005 3:39 am 
Beginner
Beginner

Joined: Wed Jan 21, 2004 10:15 am
Posts: 24
Location: Munich, Germany
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


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 18, 2005 6:43 am 
Expert
Expert

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


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 18, 2005 8:23 am 
CGLIB Developer
CGLIB Developer

Joined: Thu Aug 28, 2003 1:44 pm
Posts: 1217
Location: Vilnius, Lithuania
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.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 18, 2005 1:56 pm 
Newbie

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

_________________
............................................

Jens Scheffler
Senior Software Engineer
INIT Innovations in Transportation, Inc.

1400 Crossways Blvd. Suite 110
Chesapeake, VA 23320, USA


Top
 Profile  
 
 Post subject: Restrictions.in() method in Restrictions class...
PostPosted: Fri Apr 27, 2007 8:50 am 
Newbie

Joined: Mon Feb 20, 2006 1:40 am
Posts: 7
Location: Hyderabad
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.


Top
 Profile  
 
 Post subject: Using more than 1000 values in IN Expression
PostPosted: Mon Mar 24, 2008 7:20 am 
Newbie

Joined: Mon Mar 24, 2008 6:55 am
Posts: 1
Location: New DElhi
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

_________________
amrit amrit amrit


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 13 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.