How to tune a length where-in clause in hibernate

hemanth.srinivasan · **Joined:** Mon Aug 15, 2005 12:02 pm **Posts:** 1

I have a query that has a "where in" clause that takes in aroung 1000 parameters.I would like to know if there is any query tuning that can be done in this place?

example:
list1 = session.createCriteria(ABC.class)
.add(Expression.in("custId", custIds))
.list()
Since i have a dynamically generated list of custIds which can be any thing between 500-1000.When i run this query in a recurring fashion,
oracle throws an archive error.So I was just wondering of there is any sort of query tuning links that you could suggest.

kgignatyev · **Posted:** Mon Aug 15, 2005 3:37 pm

I would suggest trying to get rid of [in] query and replace it with one that does something else, like join with another table where selection criteria get applied.

Check also that ORACLE has enough space for logs, and that Query fits into query buffer size, I vaguely remember that there are some limitations on the size of one chunk of SQL server can chew at one time.