2013 年，一朋友咨询如何优化下面树形查询。

    1. select rownum, adn, zdn, 'cable'
    2.   from (select distinct connect_by_root(t.tdl_a_dn) adn, t.tdl_z_dn zdn
    3.           from AGGR_1 t
    4.          where t.tdl_operation <> 2
    5.            and exists (select 1
    6.                   from CABLE_1 a
    7.                  where a.tdl_operation <> 2
    8.                    and a.tdl_dn = t.tdl_z_dn)
    9.          start with exists (select 1
    10.                        from RESOURCE_FACING_SERVICE1_1 b
    11.                       where b.tdl_operation <> 2
    12.                         and t.tdl_a_dn = b.tdl_dn)
    13.         connect by nocycle prior t.tdl_z_dn = t.tdl_a_dn);

    执行计划如下。

    1. select * from table(DBMS_XPLAN.DISPLAY);
    1. Plan hash value: 1439701716
    3. -------------------------------------------------------------------------------------
    4. | Id |Operation                                   |Name                      |Rows |Bytes|
    5. -------------------------------------------------------------------------------------
    6. |  0 |SELECT STATEMENT                            |                          |31125|   59M|
    7. |  1 | COUNT                                      |                          |     |      
    8. |  2 |  VIEW                                      |                          |31125|   59M|
    9. |  3 |   HASH UNIQUE                              |                          |31125|   59M|
    10. |* 4 |    FILTER                                  |                          |     |      | 
    11. |* 5 |    CONNECT BY NO FILTERING WITH SW (UNIQUE)|                          |     |      |
    12. |  6 |      TABLE ACCESS FULL                     |AGGR_1                    | 171K| 4353K|
    13. |* 7 |      TABLE ACCESS FULL                     |RESOURCE_FACING_SERVICE1_1|    1|   18 |
    14. |* 8 |     TABLE ACCESS FULL                      |CABLE_1                   |    1|   14 |
    15. -------------------------------------------------------------------------------------
    17. Predicate Information (identified by operation id):
    18. ---------------------------------------------------
    20.    4 - filter("T"."TDL_OPERATION"<>2 AND  EXISTS (SELECT 0 FROM "CABLE_1" "A" WHERE "A"."TDL_DN"=:B1 
    21.                AND  "A"."TDL_OPERATION"<>2))
    22.    5 - access("T"."TDL_A_DN"=PRIOR "T"."TDL_Z_DN")
    23.               filter( EXISTS (SELECT 0 FROM "RESOURCE_FACING_SERVICE1_1" "B" WHERE "B"."TDL_DN"=:B1
    24.                AND "B"."TDL_OPERATION"<>2))  
    25.    7 - filter("B"."TDL_DN"=:B1 AND "B"."TDL_OPERATION"<>2)
    26.    8 - filter("A"."TDL_DN"=:B1 AND "A"."TDL_OPERATION"<>2)
    28. 25 rows selected.

    阅读过本章 Filter 优化经典案例的读者能很快发现：执行计划中，Id=4 是 Filter，Id=5 和 Id=8 是 Id=4 的儿子，这说明 Id=8 会被多次反复扫描，Id=8 走的是全表扫描，这显然不对。

    在进行 SQL 优化的时候，我们需要特别留意执行计划中的谓词过滤信息。执行计划中 Id=7 的谓词过滤中有绑定变量:B1，但是 SQL 语句中并没有绑定变量。大家是否还记得 5.5 节讲到:B1 表示传值。如果 SQL 语句本身没有绑定变量，但是执行计划中谓词过滤信息又有绑定变量**（:B1，:B2，B3..）**，这说明有绑定变量这步需要传值。典型的需要传值的有标量子查询、Filter 以及树形查询中 start with 子查询。当执行计划中某个步骤需要传值，这个步骤就会被扫描多次。执行计划中，Id=7 谓词有绑定变量，这说明 Id=7 与 Id=8 一样，要被多次扫描。另外请注意，执行计划中 Id=4 也有绑定变量，但是 Id=4 的绑定变量与 Id=8 是成对出现，Id=5 的绑定变量与 Id=7 也是成对出现，对于成对出现的绑定变量情况，关注有表对应的 Id 即可，这里有表对应的 Id 就是 7 和 8。

    通过上面分析，我们知道 SQL 的性能瓶颈在 Id=7 和 Id=8 这两步。对于树形查询，很难通过 SQL 改写减少 start with 子查询中表被多次扫描，所以只能想办法减少表被扫描的体积。我们可以创建下面两个索引来优化 SQL。

    1. create index idx_a on CABLE_1(tdl_dn,tdl_operation);
    3. create index idx_b on RESOURCE_FACING_SERVICE1_1(tdl_dn,tdl_operation);

    本案例也可以使用 with as 子句改写，然后将子查询生成临时表来进行优化，但是 with as 子句改写优化的性能没有创建索引优化的性能高，因为走索引可以进行 INDEX RANGE SCAN，而且不需要回表，而 with as 子句需要对临时表进行全表扫描。本案例的目的是让大家重视执行计划中的谓词信息！

    还有一个比较经典的案例，也需要关注谓词信息才能优化 SQL，但是限于 SQL 实在太长，我们无法在书中体现，有兴趣的读者可以查看博客：帮盖尔优化SQL。