CONNECT BY PRIOR语句优化案例

原语句文本 SELECT *
  FROM (SELECT DISTINCT A.AUTHID,A.AUTHCODE,A.SUPERCODE,A.AUTHNAME,A.MENUFLAG,A.DESCRIPTION,A.MODULEID,A.REGION,A.CREATOR,A.CREATEORG,
                        A.ISPUBLIC,A.CREATEDATE,A.STATUS,A.STATUSDATE,A.CREATETYPE,A.RIGHTGROUP,A.DISPLAYNO,A.SYSTEMID
          FROM  A
        CONNECT BY PRIOR A.SUPERCODE = A.AUTHCODE
         START WITH EXISTS (SELECT B.AUTHID FROM B WHERE A.AUTHID = B.AUTHID AND B.STAFFID = :B1
                     UNION ALL
                     SELECT C.AUTHID FROM C, D WHERE A.AUTHID = C.AUTHID AND C.ROLEID = D.ROLEID AND D.STAFFID = :B2)
                        )
 WHERE 1 = 1
   AND (INSTR(SYSTEMID, :B3) > 0 OR SYSTEMID IS NULL)   AND SUPERCODE = :B4   AND MODULEID <> 'ESOP_WEB'   AND MODULEID <> 'CMOP_WEB'
 ORDER BY DISPLAYNO, SUPERCODE, AUTHCODE;
从上到下简单解读：A表SUPERCODE列与AUTHCODE异列进行自关联，并且只输出符合exists内查询条件的结果，
最后的结果在通过外层where条件过滤
执行计划

计划执行步骤：

1、Id 9 通过条件"C"."AUTHID"=:B1 走的C表的IDX_UCP_ROLEPUTAUTH_AMID索引对C表进行查询

2、Id 10以C表查出来的ROLEID列及STAFFID列遍历D表主键索引PK_UCP_STAFFROLE，找出符合"C"."ROLEID"="D"."ROLEID" AND "D"."STAFFID"=:B2条件的数据

3、Id 6 "B"."STAFFID"=:B1 AND "B"."AUTHID"=:B1 条件走B表主键索引PK_T_UCP_PUTAUTH获得符合条件的数据并与通过第2步中的结果集做union all

4、Id 3 对表A异列连接后的结果通过第3步获得的结果筛选出所需结果集

5、Id 1 通过外层where条件对第4步获得的结果集进行筛选获得最终结果集

分析：

1、由于逻辑问题无法将最外层谓语条件改写到内层

2、SQL语句对表A是先connect with 再通过exists条件进行筛选，是否可以先筛选后连接？

改写后SQL

SELECT *
  FROM (SELECT DISTINCT A.AUTHID,A.AUTHCODE,A.SUPERCODE,A.AUTHNAME,A.MENUFLAG,A.DESCRIPTION,A.MODULEID,A.REGION,A.CREATOR,A.CREATEORG,
                        A.ISPUBLIC,A.CREATEDATE,A.STATUS,A.STATUSDATE,A.CREATETYPE,A.RIGHTGROUP,A.DISPLAYNO,A.SYSTEMID
          FROM  A
        CONNECT BY PRIOR A.SUPERCODE = A.AUTHCODE
         START WITH EXISTS(select 1 from (SELECT B.AUTHID FROM  B WHERE B.STAFFID = :B1

                     UNION ALL

                     SELECT C.AUTHID FROM  C, D  WHERE C.ROLEID = D.ROLEID AND D.STAFFID = :B2)

                     where A.AUTHID = AUTHID) )

WHERE 1 = 1
   AND (INSTR(SYSTEMID, :B3) > 0 OR SYSTEMID IS NULL)   AND SUPERCODE = :B4   AND MODULEID <> 'ESOP_WEB'   AND MODULEID <> 'CMOP_WEB'
 ORDER BY DISPLAYNO, SUPERCODE, AUTHCODE;

执行计划
执行步骤：
1、Id 11：通过条件"D"."STAFFID"=:B2访问表D的索引IX_UCP_STAFFROLE_STAFF
2、Id 12：以D表查出来的ROLEID列遍历C表主键索引PK_UCP_ROLEPUTAUTH，找出符合"C"."ROLEID"="D"."ROLEID"条件的数据
3、Id 8：通过"B"."STAFFID"=:B1条件走B表主键索引PK_T_UCP_PUTAUTH获得符合条件的数据并与通过第2步中的结果集做union all
4、Id 4：Tab_A先通过SQL语句中的A.AUTHID = AUTHID条件用第三步得到的数据与之进行HASH过滤
5、Id 3：将第4步中的结果集进行connect by
6、Id 1： 通过外层where条件对第5步获得的结果集进行筛选获得最终结果集 总结：
1、对于connect by ...start with语句的优化，先看看start with条件，看否通过条件先对connet的数据进行过滤
2、应尽量将union all上下的SQL语句中共有的条件提到外层，方面优化器对union all的处理 疑问：
为什么原SQL语句中的条件B.STAFFID = :B1在原执行计划中变成"C"."AUTHID"=:B1?
个人猜测，因为表B在访问的时候走的主键索引：PK_T_UCP_PUTAUTH，其索引键依次为（STAFFID ，MOUDLEID，AUTHID），加之原SQL中条件： A.AUTHID = B.AUTHI，A.AUTHID = C.AUTHID导致：

求大神考证！！！

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31345818/viewspace-2130199/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31345818/viewspace-2130199/