SQL 与 WHERE 子句一起使用时优化 Oracle CONNECT BY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1081570/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Optimizing Oracle CONNECT BY when used with WHERE clause
提问by topchef
Oracle START WITH ... CONNECT BYclause is applied beforeapplying WHEREcondition in the same query. Thus, WHERE constraints won't help optimize CONNECT BY.
在同一查询中应用条件之前应用OracleSTART WITH ... CONNECT BY子句。因此， WHERE 约束无助于优化.WHERECONNECT BY
For example, the following query will likely perform full table scan (ignoring selectivity on dept_id):
例如，以下查询可能会执行全表扫描（忽略对 的选择性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:
查询 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.
虽然这两个查询的性能都比原始查询好得多，但在 Oracle 10g 第 2 版上，查询 B 的性能确实比 A 好得多。
Did you have similar performance optimization to deal with with respect to CONNECT BYand WHEREclauses? How would you explain query B doing much better than query A?
您是否对CONNECT BYandWHERE子句进行了类似的性能优化？您如何解释查询 B 比查询 A 做得更好？
采纳答案by Tony Andrews
Query A says start with managers in the Sales department and then get all their employees. Oracle doesn't "know" that allthe 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.
查询 A 说从销售部门的经理开始，然后是他们所有的员工。Oracle 不“知道”查询返回的所有员工都在销售部门，因此在执行 CONNECT BY 之前，它无法使用该信息来减少要使用的数据集。
Query B explicitlyreduces the set of data to be worked on to just those employees in Sales, which Oracle can then do before performing the CONNECT BY.
查询 B显式地将要处理的数据集减少到只有 Sales 中的那些员工，然后 Oracle 可以在执行 CONNECT BY 之前执行这些操作。
回答by jluu
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 ANDconditions for the optimization to work.
请注意，您确实需要索引和两个AND条件才能使优化工作。
回答by Hernando
This is a similar query, long story short it worked faster using the nested sql than the double connect by prior option.
这是一个类似的查询，长话短说，它使用嵌套 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';
So my recommendation without much expertise is to use the nested sql to filter.
所以我没有太多专业知识的建议是使用嵌套的sql来过滤。
回答by Walter Mitty
What are the indexes on employees? You better have an index on employeeid. And you likely do have one as a consequence of declaring employeeid as the primary key.
员工的指标是什么？你最好有一个关于employeeid 的索引。由于将employeeid 声明为主键，您可能确实有一个。
You might get better performance with an index on managerid as well. Try it. This has to be balanced against slower performance when inserting new employees or reorganizing managing relationships.
您也可以使用 managerid 上的索引获得更好的性能。尝试一下。在插入新员工或重组管理关系时，这必须与较慢的性能进行平衡。
