Therefore, this time, only 2 Execution Plans are needed since both the first two queries and the last two ones have the same number of bind parameter values.

This is possible because Hibernate is now padding parameters until the next power of 2 number. So, for 3 and 4 parameters, 4 bind parameters are being used. For 5 and 6 parameters, 8 bind parameters are being used.

Cool, right?

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

If you’re using Oracle or SQL Server, then you can benefit from Execution Plan caching. The IN clause parameter padding feature increases the chance of reusing an already generated Execution Plan, especially when using a large number of IN clause parameters.

Subscribe to our Newsletter

* indicates required

Email Address *

10 000readers have found this blog worth following!

If you subscribeto my newsletter, you'll get:

A free sampleof my Video Course about running Integration tests at warp-speed using Docker and tmpfs

We are currently evaluating a different approach. We’re using a sightly different query:

SELECT p.* FROM post p WHERE p.ID = ANY(?)

And pass in a JDBC array.

There are some downsides with this approach
– not all databases support arrays
– Oracle does not support anonymous arrays, a vendor API is required to create arrays
– the standard syntax works only with Oracle 18c, a different syntax is needed for Oracle 12c