Oracle START WITH ... CONNECT BY
clause is applied before applying WHERE
condition in the same query. Thus, WHERE constraints won't help optimize CONNECT BY
.
For example, the following query will likely perform full table scan (ignoring selectivity on dept_id
):
SELECT * FROM employees
WHERE dept_id = 'SALE'
START WITH manager_id is null
CONNECT BY PRIOR employee_id = manager_id
I tried to improve performance in 2 ways:
query A:
SELECT * FROM employees
START WITH manager_id is null AND dept_id = 'SALE'
CONNECT BY PRIOR employee_id = manager_id
query B:
SELECT * FROM (
SELECT * FROM employees
WHERE dept_id = 'SALE'
)
START WITH manager_id is null
CONNECT BY PRIOR employee_id = manager_id
While both queries did much better than original, on Oracle 10g Release 2, query B did performed much better than A.
Did you have similar performance optimization to deal with with respect to CONNECT BY
and WHERE
clauses? How would you explain query B doing much better than query A?
Query A says start with managers in the Sales department and then get all their employees. Oracle doesn't "know" that all the employees returned be the query will be in the Sales department, so it can't use that information to reduce the set of data to work with before performing the CONNECT BY.
Query B explicitly reduces the set of data to be worked on to just those employees in Sales, which Oracle can then do before performing the CONNECT BY.