We currently come across more and more statements like this one (source The Hitchhiker’s Guide to the EXPLAIN PLAN Part 36: The sisters of the mother of all SQL antipatterns - Oracle - Oracle …).
The author here Conditional WHERE clauses in SQL - Avoid Smart Logic describes it as " is one of the worst performance anti-patterns of all."
WHERE (job_id = NVL(:job_id, job_id))
AND (department_id = NVL(:department_id, department_id))
AND (manager_id = NVL(:manager_id, manager_id))
AND (employee_id = NVL(:employee_id, employee_id))
For the developer it is easier to write statements like that, for the optimizer it is nightmare.
There are two ways to deal with that:
- change the statement , which might be impossible for the DBA on the receiving end
- Use the REOPT hint with the option ALWAYS. That would mean that for every call the statement will be reoptimized according to the current bind variables. That idea is shown here: Conditional WHERE clauses in SQL - Avoid Smart Logic
However the REOPT hint or similar does not exist in Oracle. We need it. If you in double please read the websites referenced here.