Use of NVL in where clause
Hi
I have a big application where the developers used the following technique: in some screens/reports, the user may or not inform the parameters. If the user inform the parameters, only the data that correspond to them are retrieved, otherwise all data is retrieved.
To achieve this, they coded this way the Selects:
where column = nvl(:parameter, column)
I thought that oracle optimizer would be smart enough to identify this situation and convert adequately to:
where column = :parameter
...when the parameter is not null, and ignore it when it is null.
But unfortunaltely it does not, and always do a full scan, despite there is a index in the column...
Any clue to make oracle more "smart" (server-side solution), or the app must be modified?
Thanks!
Luis Cabral