A couple of days ago Frank Nimphius published a new ADF Insider Essential video about Search Forms Customization where he also showed how to implement case insensitive searches. While the tip how to do this is fine, he did not mention the bottleneck involved in doing so. Yesterday, while writing up this blog I came across this blog ‘ADF ViewCriteria performance impact’ by Raman Nanda who summarized the same issue. The last statement in his blog is the starting of this blog:
Note:Also don’t choose to ignore null values for predicates that are required and create proper indexes on the table structure depending upon how you filter results. For ex: If predicate is upper(ename)=upper(:bvar) then create a index on upper(ename) .
Lets start with a look on a view criteria definition in JDeveloper 184.108.40.206.0. the image below shows the definition of a simple view criteria to search for employees who’s names start with a given bind variable. When you first add items to the view criteria both check boxes ‘Ignore Case’ and ‘Ignore NULL Values’ are checked.
A close look at the ‘View Object Where Clause’ part reveals that the ‘Ignore Case’ part of the query is not visible in JDev 220.127.116.11.0. This is a bug which I’m going to file in the near future. Before going into detail with the query lets uncheck the ‘Ignore NULL Values’ to see the final criteria:
Running the application module in the tester reveals the final query as (copied from the log window)
 SELECT Employees.EMPLOYEE_ID, Employees.FIRST_NAME, Employees.LAST_NAME, Employees.EMAIL, Employees.PHONE_NUMBER, Employees.HIRE_DATE, Employees.JOB_ID, Employees.SALARY, Employees.COMMISSION_PCT, Employees.MANAGER_ID, Employees.DEPARTMENT_ID, Employees.ACTION_COMMENT FROM EMPLOYEES Employees WHERE ( ( ( (UPPER(Employees.LAST_NAME) LIKE UPPER( :bindLN || '%') ) ) ) )  Bind params for ViewObject: [de.hahn.blog.vcinsesitivesearch.model.dataaccess.EmployeesView]VCISAppModule.EmployeesView1  Binding param "bindLN": K
Here you see that the ‘Ignore Case’ results in calling SQL UPPER'(…) on the bind parameter as well as on the row attribute. When you copy this query and run it in a SQL worksheet it returns the right results. However, in this bog we are more interested in the execution plan of the query. This is shown in the below image:
The interesting thing is that the result is reached by a full table scan, which you see as the option is ‘FULL’ for table access. This is not a problem if the table contains only a small number of rows, but if you work on large tables with 10000+ rows it’ll take ages (OK, it’s notable longer then you would expect) to execute.
If you only have defined the normal index on the LAST_NAME column
create index normal_ln_idx on employees (last_name);
the plan doesn’t change at all. This is exactly what Raman Nanda meant in his blog. You need to create a function based index on the LAST_NAME column. Here is the SQL to do so:
create index upper_ln_ix on employees (UPPER(last_name));
Running the query again after creation of the new index results in
As yo ucan see the table now is accessed via the ‘upper_ln_ix’ index we created. This speeds things up in large tables.
To summarize this blog: you should, as part of your testing, check the execution plans of the queries executed by your application. This can result in a huge improvement of the performance. You should ask your DBA to help you with this task. There are tools readily available to the DBA to help getting information about the queries executed by your application.