Optimizing Oracle CONNECT BY when used with WHERE clause

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.

This should give the ultimate performance:

CREATE INDEX i_employees_employee_manager_dept ON employees (employee_id,manager_id,dept_id);CREATE INDEX i_employees_manager_employee_dept ON employees (manager_id,employee_id,dept_id);SELECT * FROM employees  START WITH manager_id is null AND dept_id = 'SALE' CONNECT BY PRIOR employee_id = manager_id AND dept_id = 'SALE' 

Note that you do need both index and both AND conditions for the optimization to work.

This is a similar query, long story short it worked faster using the nested sql than the double connect by prior option.

'SELECT level, XMLElement("elemento", XMLAttributes(codigo_funcion as "Codigo",                                                    nombre_funcion as "Nombre",                                                    objetivos as "Objetivos",                                                     descripcion as "Descripción",                                                    ''rightHanging'' as "layout"))   FROM (           SELECT * FROM dithe_codigo_funcion            WHERE nodo_raiz = ''PEP''    )         START WITH codigo_funcion = ''PEP''    CONNECT BY PRIOR codigo_funcion = nivel_anterior'; 

So my recommendation without much expertise is to use the nested sql to filter.