I am using Oracle as a database.
I have a filter that is generating code like this in the SQL (two fields, ZIP and Last Name). Filters are “Starts With”:
LOWER(t1.ZIP) LIKE ? ESCAPE ? AND LOWER(t1.LNAME) LIKE ? ESCAPE ?
I know that the fields are upper case, and I have an index in the database. However, since the SQL contains a function (lower) on each of the fields, Oracle doesn’t use the index. Since the table contains about 47 million rows, a full table scan is way too slow.
I have set @CaseConversion on the fields, so the system “knows” they are upper case fields.
I got around it by creating Custom filters where I force the JPQL to be:
ZIP - t1.ZIP like concat(?,’%’)
Last Name - t1.lname like concat(upper(?),’%’)
Now the database can use the index and the filter is much faster. The downside is that the user can no longer choose the type of filter (starts with vs equals vs contains).
Is there any way to fix the SQL that the filter and/or EclipseLink is generating?