[hibernate-issues] [JIRA] (BVAL-751) Collection padding for Oracle queries

[Peter Holvenstot (JIRA)]

Peter Holvenstot ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%3Ad07cd030-113c-41eb-a09f-653610c94c52 ) *created* an issue

Bean Validation ( https://hibernate.atlassian.net/browse/BVAL?atlOrigin=eyJpIjoiOWYyN2FhMjZjMmE2NDliYjg3NzM5NjNmODliNzcwOTIiLCJwIjoiaiJ9 ) / Improvement ( https://hibernate.atlassian.net/browse/BVAL-751?atlOrigin=eyJpIjoiOWYyN2FhMjZjMmE2NDliYjg3NzM5NjNmODliNzcwOTIiLCJwIjoiaiJ9 ) BVAL-751 ( https://hibernate.atlassian.net/browse/BVAL-751?atlOrigin=eyJpIjoiOWYyN2FhMjZjMmE2NDliYjg3NzM5NjNmODliNzcwOTIiLCJwIjoiaiJ9 ) Collection padding for Oracle queries ( https://hibernate.atlassian.net/browse/BVAL-751?atlOrigin=eyJpIjoiOWYyN2FhMjZjMmE2NDliYjg3NzM5NjNmODliNzcwOTIiLCJwIjoiaiJ9 )

Issue Type: Improvement Assignee: Unassigned Created: 10/Jun/2020 13:47 PM Priority: Minor Reporter: Peter Holvenstot ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%3Ad07cd030-113c-41eb-a09f-653610c94c52 )

Oracle matches its query planning cache using an exact hash of the SQL string (with bind variable placeholders).

This means, for example, the query "select * from Person where person.firstName IN ('Larry', 'Curly')" will not hit the same query plan as the query "select * from Person where person.firstName IN ('Larry','Curly','Moe')".

The trivial solution to this is to pad all collections to the maximum size (1000 elements) by repeating one of the elements. For example: "select * from Person where person.FirstName IN ('Larry','Curly','Larry','Larry','Larry' .... )". It can be seen that this is logically equivalent to the first query yet we can fill any number of elements up to the maximum and hit the same query plan.

Having to manually alter every collection every place an "in" query is used is undesirable, and mucks up things because now the translated HQL string (when printed for debuggin) gets clumsy and has needless repetition. Could this be integrated into the Oracle dialect so that it happens automatically?

( https://hibernate.atlassian.net/browse/BVAL-751#add-comment?atlOrigin=eyJpIjoiOWYyN2FhMjZjMmE2NDliYjg3NzM5NjNmODliNzcwOTIiLCJwIjoiaiJ9 ) Add Comment ( https://hibernate.atlassian.net/browse/BVAL-751#add-comment?atlOrigin=eyJpIjoiOWYyN2FhMjZjMmE2NDliYjg3NzM5NjNmODliNzcwOTIiLCJwIjoiaiJ9 )

Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.core&referrer=utm_source%3DNotificationLink%26utm_medium%3DEmail ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailNotificationLink&mt=8 ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100128- sha1:0213fc8 )
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/hibernate-issues/attachments/20200610/6ff90d0a/attachment.html