1

I support a Groovy/Java application that uses Hibernate criteria queries. In one of the queries, it passes in a list of parameters for an IN clause that can be very long (between 1 and about 8000 items). This actually exceeds Oracle's limits, so we have to break up the list, e.g:

SELECT * from TABLE where ID IN (?, ?, ?, ...) OR IN (?, ?, ?, ...) OR ...

or in criteria-speak (in Groovy):

if (idList) {
    Junction orGroup = Restrictions.disjunction()
    idList.collate(1000)?.each { subList ->
        criteria.add(Restrictions.in('id', subList.collect { it }))
    }
    criteria.add(orGroup)
}

Having this very long list is ugly and our DBA says the varying number of bind parameters messes with Oracle's ability to optimize query execution and her ability to offer performance tuning suggestions.

I've attempted some obvious ways to reduce the number of bind parameters, such as replacing the list with a subquery that provides the same values, but the performance has always been worse. I suppose it was originally done in this ugly way for performance reasons (but back then, the list couldn't get as large).

The DBA is recommending I try using an "array bind" to pass in the list, which she says will result in an equivalent query with only one bind parameter. Is this possible in Hibernate? She says some other team had success doing that in the past, but doesn't know anything about how to do it on the application side. The queries being built here have a lot of variation based on user input, and I don't have the time to switch away from using Hibernate criteria to build them by hand using something like JDBC.

Kaypro II
  • 3,210
  • 8
  • 30
  • 41
  • Just in case it makes everything easier, where are those parameter values coming from? A user typing in values or the DB? – Andrew Sayer Oct 31 '21 at 12:23
  • They're coming from the DB. We've also looked at using hints to try to make things better, but that's very difficult and constrained by the way Hibernate is generating aliases. – Kaypro II Nov 02 '21 at 14:40
  • If the values are coming from the DB then you should be using a join. This sounds like you've the joins into the application ( https://stackoverflow.com/questions/97197/what-is-the-n1-selects-problem-in-orm-object-relational-mapping/97253#97253 ) – Andrew Sayer Nov 02 '21 at 15:26
  • No, this isn't an n+1 selects problem. The joins are being handled by Hibernate. The query is logically one select with a `WHERE ID IN ()`, but Oracle isn't using the best indexes that way. Breaking it into two ("subquery" + main query with a giant list of IDs) appears to lead to better indexes being used, but the query having a ridiculous number of bind parameters which causes lesser issues. – Kaypro II Nov 02 '21 at 19:59
  • Oracle will have a much easier time if you go back to using the subquery. You just need to figure out what’s preventing it from deciding that a particular index is preferable. My guess is you’ve got index stats that aren’t taking advantage of the `table_cache_blocks` stats preference so it thinks your joined rows are all over the place when in reality they’re fairly clustered – Andrew Sayer Nov 02 '21 at 21:08

0 Answers0