Using Where Clause with Hierarchical Queries

Using where clause in hierarchical queries may be confusing. Oracle first evaluates the joins in the query if there are any. Then, next step, selects rows in hierarchical order. Finally, applies remaining where clause conditions. In other words, rows are filtered by where clause after evaluating the hierarchy. Lets show this with an example on HR schema:

SQL> select level, e.employee_id, e.manager_id, e.department_id, e.first_name, e.last_name
 2  from employees e, departments d, jobs j
 3  where e.department_id = d.department_id
 4  and e.job_id = j.job_id
 5  and d.department_id = 100
 6  start with e.manager_id is null
 7  connect by prior e.employee_id = e.manager_id;

    LEVEL EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID FIRST_NAME           LAST_NAME
---------- ----------- ---------- ------------- -------------------- -------------------------
        3         108        101           100 Nancy                Greenberg
        4         109        108           100 Daniel               Faviet
        4         110        108           100 John                 Chen
        4         111        108           100 Ismael               Sciarra
        4         112        108           100 Jose Manuel          Urman
        4         113        108           100 Luis                 Popp

6 rows selected.

As you can see first row begins with level 3.
However, in my experience, most of the time when I wrote hierarchical queries, I needed the evaluation of the hierarchy to be performed after the where clause. In this case, I recommend inner queries with NO_MERGE hint. Using NO_MERGE hint will prevent Oracle SQL Optimizer to merge the inner query with the hierarchical one. Of course, to prevent merge, developers should be careful about not passing any references from inner query to the outer (hierarchical) one:

SQL> select level, e2.*
 2  from (select /*+ NO_MERGE */ e.employee_id, e.manager_id, e.department_id, e.first_name, e.last_name
 3        from employees e, departments d, jobs j
 4        where e.department_id = d.department_id
 5        and e.job_id = j.job_id
 6        and d.department_id = 100) e2
 7  start with e2.employee_id = 108  --I know the root employee of my query
 8  connect by prior e2.employee_id = e2.manager_id;

    LEVEL EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID FIRST_NAME           LAST_NAME
---------- ----------- ---------- ------------- -------------------- -------------------------
        1         108        101           100 Nancy                Greenberg
        2         109        108           100 Daniel               Faviet
        2         110        108           100 John                 Chen
        2         111        108           100 Ismael               Sciarra
        2         112        108           100 Jose Manuel          Urman
        2         113        108           100 Luis                 Popp

6 rows selected.

Same result except level values. This is because SQL Optimizer first evaluated inner query (joins and other where clause filters) and then evaluated the hierarchy. Furthermore, if you compare SQL plans of the two queries, you will see a full table access for the first one while second one has no full table access.

Uğur Koçak

Comments

Popular posts from this blog

Monitoring Oracle Database with Zabbix

Powerful Free Webinar Network for Oracle Developers