Generic query for multicriteria search – part I: USE_CONCAT (OR Expansion)

By Franck Pachot

.
You have a multicriteria search screen on the EMPLOYEE table where you can enter an employee id, a department id, a manager id or a job id. Either you put the value you want to filter on, or you leave it null when you don’t want to filter on it. How will you code that? You can build the query on the fly with dynamic SQL or use a generic query like this one:

       SELECT *
       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))

This is good for the code maintainability, but having a one-fit-all query will not be optimial for each cases. Markus Winand (every database developer should read his book) describes the danger ot that in his website: Use The Index, Luke
In this post I’ll show the problem on the Oracle example schema HR and in the next post I’ll see how 11g Adaptive Cursor Sharing can help.
Here is the example to run on the HR schema. I declare the 4 variables in sqlplus:

SQL>  variable job_id varchar2(20)
SQL>  variable department_id number
SQL>  variable manager_id number
SQL>  variable employee_id number

All binds null

I assign null for all of them – meaning that I don’t want to filter anything:

SQL> exec :job_id:=null; :department_id:=null; :manager_id:=null; :employee_id:=null;
PL/SQL procedure successfully completed.

and I run my generic query:

SQL> 
     SELECT
     COUNT(*)
     FROM
       (SELECT 1
       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))
       )
SQL> /

  COUNT(*)
----------
       105

Let’s check the execution plan:

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last +outline'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID  5q737h3umyx3u, child number 0
-------------------------------------
SELECT  COUNT(*) FROM   (SELECT 1   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))   )

Plan hash value: 3424141370

------------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Starts | E-Rows | A-Rows |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |      1 |        |      1 |
|   1 |  SORT AGGREGATE                 |               |      1 |      1 |      1 |
|   2 |   CONCATENATION                 |               |      1 |        |    105 |
|*  3 |    FILTER                       |               |      1 |        |    105 |
|*  4 |     TABLE ACCESS BY INDEX ROWID | EMPLOYEES     |      1 |    105 |    105 |
|*  5 |      INDEX FULL SCAN            | EMP_EMP_ID_PK |      1 |    107 |    107 |
|*  6 |    FILTER                       |               |      1 |        |      0 |
|*  7 |     TABLE ACCESS BY INDEX ROWID | EMPLOYEES     |      0 |      1 |      0 |
|*  8 |      INDEX UNIQUE SCAN          | EMP_EMP_ID_PK |      0 |      1 |      0 |
------------------------------------------------------------------------------------

The CBO has used OR Expansion on the 4th predicate on employee_id. The execution has used the branch for all rows (the INDEX FULL SCAN) and the branch optimized to get only one employee (INDEX UNIQUE SCAN) has not been executed (Starts=0).

Do you wonder why the optimizer has chosen a full scan on the index instead of the table? Because the predicate implicitely select only rows where EMPLOYEE_ID is not null, and I’ve probably not enough statistics and/or constraint to let the optimizer know that all rows have an EMPLOYEE_ID. But that’s not the point here. I’m just using the HR schema as it is provided when creating the database with the example schemas.

Here are the predicates where we see that the FILTER operations – line 3 and 6 – are on the predicate about :EMPLOYEE_ID being null or not:

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(:EMPLOYEE_ID IS NULL)
   4 - filter(("DEPARTMENT_ID"=NVL(:DEPARTMENT_ID,"DEPARTMENT_ID") AND
              "MANAGER_ID"=NVL(:MANAGER_ID,"MANAGER_ID") AND "JOB_ID"=NVL(:JOB_ID,"JOB_ID")))
   5 - filter("EMPLOYEE_ID" IS NOT NULL)
   6 - filter(:EMPLOYEE_ID IS NOT NULL)
   7 - filter(("DEPARTMENT_ID"=NVL(:DEPARTMENT_ID,"DEPARTMENT_ID") AND
              "MANAGER_ID"=NVL(:MANAGER_ID,"MANAGER_ID") AND "JOB_ID"=NVL(:JOB_ID,"JOB_ID")))
   8 - access("EMPLOYEE_ID"=:EMPLOYEE_ID)

There is another way to see that the plan has done OR expansion on the 4th predicate:

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE_LEAF(@"SEL$F5BB74E1_1")
      USE_CONCAT(@"SEL$F5BB74E1" 8 OR_PREDICATES(4))
      OUTLINE_LEAF(@"SEL$F5BB74E1_2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      INDEX(@"SEL$F5BB74E1_1" "EMPLOYEES"@"SEL$2" ("EMPLOYEES"."EMPLOYEE_ID"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$F5BB74E1_1" "EMPLOYEES"@"SEL$2")
      INDEX_RS_ASC(@"SEL$F5BB74E1_2" "EMPLOYEES"@"SEL$F5BB74E1_2" ("EMPLOYEES"."EMPLOYEE_ID"))
      END_OUTLINE_DATA
  */

This is the OR Expansion optimizer transformation. It rewrites the statement as if we did a UNION ALL where each branch is optimized for one case. The plan shows CONCATENATION instead of the UNION ALL but it’s the same. The filter predicate makes only one branche active and you see it in the execution statistics ‘Starts’ column.

query for one EMPLOYEE_ID

Now I bind a value to EMPLOYEE_ID:

SQL> exec :job_id:=null; :department_id:=null; :manager_id:=null; :employee_id:=0;
PL/SQL procedure successfully completed.

and execute the same query as above:

SQL> /

  COUNT(*)
----------
         0

We have the same plan as we reuse the same cursor (child number 0):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID  5q737h3umyx3u, child number 0
-------------------------------------
SELECT  COUNT(*) FROM   (SELECT 1   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))   )

Plan hash value: 3424141370

------------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Starts | E-Rows | A-Rows |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |      1 |        |      1 |
|   1 |  SORT AGGREGATE                 |               |      1 |      1 |      1 |
|   2 |   CONCATENATION                 |               |      1 |        |      0 |
|*  3 |    FILTER                       |               |      1 |        |      0 |
|*  4 |     TABLE ACCESS BY INDEX ROWID | EMPLOYEES     |      0 |    105 |      0 |
|*  5 |      INDEX FULL SCAN            | EMP_EMP_ID_PK |      0 |    107 |      0 |
|*  6 |    FILTER                       |               |      1 |        |      0 |
|*  7 |     TABLE ACCESS BY INDEX ROWID | EMPLOYEES     |      1 |      1 |      0 |
|*  8 |      INDEX UNIQUE SCAN          | EMP_EMP_ID_PK |      1 |      1 |      0 |
------------------------------------------------------------------------------------

And now the branch optimized for one EMPLOYEE_ID is used (Starts=1), thanks to OR Expansion. This is good. The OR Expansion transformation makes it optimal when only one column is involved in the predicate.

Query for all employees but specific values on other columns

Here are my variable bindings:

SQL> exec :job_id:='AC_MGR'; :department_id:=100; :manager_id:=0; :employee_id:=null;
PL/SQL procedure successfully completed.

I have predicates on all other columns (where I have indexes).

SQL> /

  COUNT(*)
----------
         0

But the same cursor is still shared:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID  5q737h3umyx3u, child number 0
-------------------------------------
SELECT  COUNT(*) FROM   (SELECT 1   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))   )

Plan hash value: 3424141370

------------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Starts | E-Rows | A-Rows |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |      1 |        |      1 |
|   1 |  SORT AGGREGATE                 |               |      1 |      1 |      1 |
|   2 |   CONCATENATION                 |               |      1 |        |      0 |
|*  3 |    FILTER                       |               |      1 |        |      0 |
|*  4 |     TABLE ACCESS BY INDEX ROWID | EMPLOYEES     |      1 |    105 |      0 |
|*  5 |      INDEX FULL SCAN            | EMP_EMP_ID_PK |      1 |    107 |    107 |
|*  6 |    FILTER                       |               |      1 |        |      0 |
|*  7 |     TABLE ACCESS BY INDEX ROWID | EMPLOYEES     |      0 |      1 |      0 |
|*  8 |      INDEX UNIQUE SCAN          | EMP_EMP_ID_PK |      0 |      1 |      0 |
------------------------------------------------------------------------------------

and this is not optimal because there is no branch for those predicates. The FULL SCAN branch has been used and rows have been filtered only after the table access.

Bind Sensitive

The statement is marked as bind sensitive (I have histograms on those columns) the the cursor never becomes bind aware. Here it s after hundreds of executions with different combination of values and nulls:

SQL> select child_number,executions,is_bind_sensitive,is_bind_aware from v$sql where sql_id='5q737h3umyx3u';

CHILD_NUMBER EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------------- -------------
           0        603 Y                 N       

Bind Aware

So now, I want to try if I can solve the issue by adding the BIND_AWARE hint in the statement, in order to have different execution plans when providing different combination of values or nulls. But this is for the next blog post…