Skip to Main Content

Deal with smart logic: Implement the reopt(ALWAYS) Hint like DB2

Lothar FlatzMar 2 2015 — edited Jan 11 2016

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."




  FROM employees

  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:

  1. change the statement , which might be impossible for the DBA on the receiving end
  2. 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.

Post Details
Added on Mar 2 2015