与 WHERE 子句一起使用时优化 Oracle CONNECT BY

Question

Oracle START WITH ... CONNECT BY 子句在同一查询中在应用WHERE 条件之前应用。 因此，WHERE 约束无助于优化 CONNECT BY。

例如，以下查询可能会执行全表扫描（忽略 dept_id 上的选择性）：

SELECT * FROM employees 
WHERE dept_id = 'SALE'
START WITH manager_id is null
CONNECT BY PRIOR employee_id = manager_id

我尝试通过两种方式提高性能：

查询 A：

SELECT * FROM employees 
START WITH manager_id is null AND dept_id = 'SALE'
CONNECT BY PRIOR employee_id = manager_id

查询 B：

SELECT * FROM (
               SELECT * FROM employees 
                WHERE dept_id = 'SALE'
              )
START WITH manager_id is null
CONNECT BY PRIOR employee_id = manager_id

虽然这两个查询的效果都比原始查询好得多，在 Oracle 10g 第 2 版上，查询 B 的性能确实比 A 好得多。

对于 CONNECT BY 和 WHERE 子句，您是否有类似的性能优化需要处理？ 您如何解释查询 B 比查询 A 做得更好？

Answer 1

查询 A 表示从销售部门的经理开始，然后获取他们的所有员工。 Oracle 不“知道”查询返回的所有员工都在销售部门，因此在执行 CONNECT 之前它无法使用该信息来减少要使用的数据集经过。

查询 B 显式将要处理的数据集减少到仅限销售部门的员工，Oracle 可以在执行 CONNECT BY 之前执行此操作。

Answer 2

这应该会提供最终的性能：

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' 

请注意，您确实需要索引和 AND 条件才能使优化发挥作用。

Answer 3

这是一个类似的查询，长话短说，使用嵌套 sql 比使用先前选项的双连接运行得更快。

'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'; 

所以我没有太多专业知识的建议是使用嵌套sql来过滤。

Answer 4

员工的指标有哪些？ 你最好有一个关于employeeid 的索引。 由于将employeeid 声明为主键，您可能确实拥有了一个。

使用 managerid 上的索引也可能会获得更好的性能。 尝试一下。 在插入新员工或重组管理关系时，必须与性能下降相平衡。