Oracle 有表连接的connect by 的优化

说明

有1个有表连接，还有connect by 的SQL，整了好几天才优化成功，感觉像游戏中打死了只大boss一样。现将这个过程整理一下。

优化前：

 

SELECT r.OUT_VER_BEGIN_IDdataID

  FROM DMS_DATA_RELA r, DMS_OBJ o

 WHERE r.DELETE_FLAG = '0'

   AND r.RELA_TYPE_CODE = 'parent'

   AND r.OUT_OBJ_CODE = o.OBJ_CODE

   AND o.DELETE_FLAG = '0'

   AND o.OPEN_STATE = '1'

   AND r.IN_OBJ_CODE != 'o_in'

 START WITH r.IN_DATA_ID in

            (SELECT d.OUT_DATA_ID

               FROM DMS_DATA_RELA d

              where d.OUT_VER_BEGIN_ID = :1

                and d.last_curent_flag = '1')

CONNECT BY r.IN_VER_BEGIN_ID = PRIOR r.OUT_VER_BEGIN_ID

 

优化后：

我把这个SQL先做了connect by 循环，然后再与另1个表做了连接，效果超好，我从李华值 《海量数据库解决方案》3.2.5 找到相关例子，并有这样的说明   ： ”如果查询条件中的列位于同一表中时，并没有必要优先执行表连接“

select dataID

  from(SELECT r.OUT_VER_BEGIN_ID dataID, r.out_obj_code

         FROM DMS_DATA_RELA r

        WHERE r.DELETE_FLAG = '0'

          AND r.RELA_TYPE_CODE = 'parent'

          AND r.IN_OBJ_CODE != 'o_in'

         STARTWITH r.IN_DATA_ID in

                   (SELECT d.OUT_DATA_ID

                       FROM DMS_DATA_RELA d

                      where d.OUT_VER_BEGIN_ID=:1

                        and d.last_curent_flag= '1')

       CONNECT BY r.IN_VER_BEGIN_ID = PRIOR r.OUT_VER_BEGIN_ID) e,

      DMS_OBJ o

 wheree.OUT_OBJ_CODE = o.OBJ_CODE

   ANDo.DELETE_FLAG = '0'

   ANDo.OPEN_STATE = '1'

 

优化过程：

曾中途一筹莫展时，到刘大的论坛求助过，下面是地址。

http://t.askmaclean.com/thread-3381-1-1.html

下面过程是基于以上的整理。有基本信息和试过的方法

基本信息

基本环境
操作系统：windows server 2008 r2 enterprise

SQL> select * from V$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise EditionRelease 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE       10.2.0.4.0        Production
TNS for 64-bit Windows: Version 10.2.0.4.0- Production
NLSRTL Version 10.2.0.4.0 - Production



开发中的库，在做测试时，发现有1SQL比较消耗资源，我想把他优化一下。

SQL如下：
SELECT r.OUT_VER_BEGIN_ID dataID
  FROM DMS_DATA_RELA r, DMS_OBJ o
WHERE r.DELETE_FLAG = '0'
   AND r.RELA_TYPE_CODE ='parent'
   AND r.OUT_OBJ_CODE =o.OBJ_CODE
   AND o.DELETE_FLAG = '0'
   AND o.OPEN_STATE = '1'
   AND r.IN_OBJ_CODE != 'o_in'
START WITH r.IN_DATA_ID in
           (SELECT d.OUT_DATA_ID
              FROM DMS_DATA_RELA d
             where d.OUT_VER_BEGIN_ID = :1
               and d.last_curent_flag = '1')
CONNECT BY r.IN_VER_BEGIN_ID = PRIORr.OUT_VER_BEGIN_ID

执行计划： （来自awrsqrpt)

0  SELECT STATEMENT       8045 (100)   
1     FILTER           
2       CONNECT BY WITHFILTERING            
3        FILTER            
4          COUNT            
5            HASH JOIN    717K 111M 8045 (1)00:01:37  
6              TABLE ACCESSFULL  DMS_OBJ  41  656  3 (0)00:00:01  
7              TABLE ACCESSFULL  DMS_DATA_RELA  717K 100M 8037 (1)00:01:37  
8          TABLE ACCESS BY INDEX ROWIDDMS_DATA_RELA  1  91  4 (0) 00:00:01  
9            INDEX RANGE SCAN  OUT_VER_BEGIN_ID_INDEX  1   3 (0) 00:00:01  
10        HASH JOIN            
11          CONNECT BY PUMP           
12          COUNT            
13            HASH JOIN    717K 111M 8045 (1)00:01:37  
14              TABLE ACCESSFULL  DMS_OBJ  41  656  3 (0)00:00:01  
15              TABLE ACCESSFULL  DMS_DATA_RELA  717K 100M 8037 (1)00:01:37  

随便找个变量单独执行这条SQL试了下，大概能执行6秒。

表上记录数
SQL> select count(*) from dms_data_rela;
  COUNT(*)
----------
    858470

SQL> select count(*) from dms_obj;
  COUNT(*)
----------
        41


表上索引：（索引较多，如果不影响此条SQL的查询性能，先不考虑这些索引合不合理，因为有些非技术因素）

SQL> selectindex_name,column_name,table_name from dba_ind_columns where table_name='DMS_DATA_RELA' order by index_name;
INDEX_NAME                    COLUMN_NAME         TABLE_NAME
-------------------------------------------------- ------------------------------
CREATE_TIME_INDEX             SYS_NC00031$        DMS_DATA_RELA
DELETE_FLAG_INDEX             DELETE_FLAG         DMS_DATA_RELA