CODEWITHSUNDEEP
javasqloracle-databasequery-optimization
instanceOfObject
instanceOfObject

Reputation: 2984

Optimize oracle query with IN clause

I have two queries where I am using in parameter and I am populating the PreparedStatement using setLong and setString operations.

Query 1

SELECT A, B FROM TABLE1 WHERE A in  (SELECT A FROM TABLE2 WHERE C in (?,?,?) )

Query 2

SELECT A, B FROM TABLE1 WHERE A in (?,?)

I am being told that it creates a unique query for each possible set size and pollutes Oracle's SQL cache. Also, oracle could choose different execution plans for each query here as size is not fixed.

What optimizations could be applied to make it better?

Would it be fine if I create in-clause list of size 50 and populate remaining ones using dummy/redundant variables?

If I am not wrong, select-statement in the in-clause will be difficult to optimize unless it is extracted out and used again as a list of statements.

Upvotes: 2

Views: 1300

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726619

I am being told that it creates a unique query for each possible set size and pollutes Oracle's SQL cache.

This is correct, assuming that the number of items in the IN list can change between requests. If the number of question marks inside the IN list remains the same, there would be no "pollution" of the cache.

Also, oracle could choose different execution plans for each query here as size is not fixed.

That is correct, too. It's a good thing, though.

What optimizations could be applied to make it better? Would it be fine if I create in-clause list of size 50 and populate remaining ones using dummy/redundant variables?

Absolutely. I used this trick many times: rather than generating a list of the exact size, I generated lists of length divisible by a certain number (I used 16, but 50 is also fine). If the size of the actual list wasn't divisible by 16, I added the last item as many times as it was required to reach the correct length.

The only optimization this achieves is the reduction of items in the cache of query plans.

Upvotes: 5

Related Questions