Portal

How to make my search form or named query filter use my custom created indexes if the parameter is not mandatory ?

Customers usually write 

:PARAM is not null and Att = :PARAM

to apply a parameter only if it is provided by the user in the Search Form or the caller of the Named Query. But this kind of expression prevents using any custom index created (USR_xxx). Writing the following expression instead will help the optimizer using the index and render a result much faster : 

Att like nvl(:PARAM, ‘%’)

Morevover, it also performs well if some functions need to be applied to the parameter, like this : 

Att like nvl(upper(:PARAM), ‘%’)

Please note that this filter limits the results to records that have a non-null value for the attribute.