与WHERE子句一起使用时优化Oracle CONNECT BY [英] Optimizing Oracle CONNECT BY when used with WHERE clause

问题描述

Oracle START WITH ... CONNECT BY子句.因此，WHERE约束将无助于优化CONNECT BY.

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.

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

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:

查询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更好.

While both queries did much better than original, on Oracle 10g Release 2, query B did performed much better than A.

对于CONNECT BY和WHERE子句，您是否有类似的性能优化来处理?您如何解释查询B比查询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?

推荐答案

查询A说，首先要从销售部门的经理开始，然后聘请其所有员工. Oracle不知道"查询返回的所有所有员工将在销售部门内，因此在执行CONNECT之前，它无法使用该信息来减少要使用的数据集.经过.

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.

查询B 明确地将要处理的数据集仅减少给Sales中的那些员工，然后Oracle可以在执行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.

这篇关于与WHERE子句一起使用时优化Oracle CONNECT BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！