Oracle 有表连接的connect by 的优化

程序员文章站 2022-06-11 19:40:16

...

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

说明

有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

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'

优化过程：

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

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

基本信息

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

表上记录数

另外表也收集过统计信息了。

表上的列：

SQL> desc DMS_DATA_RELA
Name Type Nullable Default Comments
------------------------------ ------------- -------- ------- --------
RELA_ID CHAR(32)
IN_DATA_ID VARCHAR2(200) Y
IN_DATA_NAME VARCHAR2(200) Y
IN_DATA_SOURCE_ID VARCHAR2(200) Y
IN_DATA_SOURCE_CODE VARCHAR2(200) Y
IN_OBJ_CODE VARCHAR2(200) Y
IN_VER_BEGIN_ID VARCHAR2(200) Y
IN_VER_BEGIN_DATA_SOURCE_ID VARCHAR2(200) Y
IN_VER_BEGIN_DATA_SOURCE_CODE VARCHAR2(64) Y
IN_VER_BEGIN_DATA_OBJ_CODE VARCHAR2(64) Y
OUT_DATA_ID VARCHAR2(200) Y
OUT_DATA_NAME VARCHAR2(200) Y
OUT_DATA_SOURCE_ID VARCHAR2(200) Y
OUT_DATA_SOURCE_CODE VARCHAR2(200) Y
OUT_OBJ_CODE VARCHAR2(200) Y
OUT_VER_BEGIN_ID VARCHAR2(200) Y
OUT_VER_BEGIN_DATA_SOURCE_ID VARCHAR2(200) Y
OUT_VER_BEGIN_DATA_SOURCE_CODE VARCHAR2(64) Y
OUT_VER_BEGIN_DATA_OBJ_CODE VARCHAR2(64) Y
RELA_TYPE_CODE VARCHAR2(200) Y
MIDDLE_OBJ_CODE VARCHAR2(64) Y
LAST_CURENT_FLAG CHAR(1) Y
CREATE_USER_NAME VARCHAR2(200) Y
CREATE_USER_REAL_NAME VARCHAR2(200) Y
CREATE_TIME TIMESTAMP(6) Y
UPDATE_USER_NAME VARCHAR2(200) Y
UPDATE_USER_REAL_NAME VARCHAR2(200) Y
UPDATE_TIME TIMESTAMP(6) Y
DELETE_FLAG CHAR(1) Y
ORDER_NUM NUMBER(10) Y

另外也有人提到清理索引后，效果会好。首先，索引有些非技术原因不让清理。另外我弄了1个新环境，没有过多索引，试过不同的列上建不同的索引，效果也是一样的。单就此条SQL来说，过多的索引应该影响不大。

有人问索引状态，也一并附上：
SQL> select table_name, index_name,index_type,statusfrom user_indexes where table_name='DMS_DATA_RELA';
TABLE_NAME INDEX_NAME INDEX_TYPE STATUS
------------------------------------------------------------ --------------------------- --------
DMS_DATA_RELA OUT_VER_BEGIN_ID_INDEX NORMAL VALID
DMS_DATA_RELA IN_DATA_ID_INDEX NORMAL VALID
DMS_DATA_RELA IN_DATA_NAME_INDEX NORMAL VALID
DMS_DATA_RELA IN_DATA_SOURCE_CODE_INDEX NORMAL VALID
DMS_DATA_RELA IN_DATA_SOURCE_ID_INDEX NORMAL VALID
DMS_DATA_RELA IN_OBJ_CODE_INDEX NORMAL VALID
DMS_DATA_RELA IN_VER_BEGIN_ID_INDEX NORMAL VALID
DMS_DATA_RELA OUT_DATA_NAME_INDEX NORMAL VALID
DMS_DATA_RELA OUT_DATA_SOURCE_ID_INDEX NORMAL VALID
DMS_DATA_RELA OUT_OBJ_CODE_INDEX NORMAL VALID
DMS_DATA_RELA RELA_TYPE_CODE_INDEX NORMAL VALID
DMS_DATA_RELA DELETE_FLAG_INDEX NORMAL VALID
DMS_DATA_RELA LAST_CURENT_FLAG_INDEX NORMAL VALID
DMS_DATA_RELA CREATE_TIME_INDEX FUNCTION-BASED NORMAL VALID
DMS_DATA_RELA OUT_DATA_ID_INDEX NORMAL VALID
DMS_DATA_RELA PK_DMS_DATA_RELA NORMAL VALID
16 rows selected

我试过物化视图

CREATE MATERIALIZED VIEW mv_dms_ddr

管理

试过Nested Loop

有人提到nested loop，我前面测过了，效果不怎么好，，现在再把nl的执行计划附上。执行计划是代入变量测的。

代入变量值，不加nl的hint

set autot traceonly

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 = '20130131036703_syspro_o_wbs'

and d.last_curent_flag = '1')

CONNECT BY r.IN_VER_BEGIN_ID = PRIORr.OUT_VER_BEGIN_ID

已用时间: 00: 00: 06.45

执行计划

----------------------------------------------------------

Plan hash value: 3423681500

----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

| 0| SELECT STATEMENT | | 637K| 92M| 6643 (1)| 00:01:20 |

|* 1| FILTER | | | | | |

|* 2| CONNECT BY WITH FILTERING | | | | | |

|* 3| FILTER | | | | | |

| 4| COUNT | | | | | |

|* 5| HASH JOIN | | 637K| 92M| 6643 (1)| 00:01:20 |

| 6| TABLE ACCESS FULL | DMS_OBJ | 63 | 945 | 3 (0)| 00:00:01 |

| 7| TABLE ACCESS FULL | DMS_DATA_RELA | 637K| 83M| 6636 (1)| 00:01:20 |

|* 8| TABLE ACCESS BY INDEX ROWID|DMS_DATA_RELA | 1 | 82 | 4 (0)| 00:00:01 |

|* 9| INDEX RANGE SCAN | OUT_DATA_ID_INDEX | 1 | | 3 (0)| 00:00:01 |

|* 10 | HASH JOIN | | | | | |

| 11| CONNECT BY PUMP | | | | | |

| 12| COUNT | | | | | |

|* 13 | HASH JOIN | | 637K| 92M| 6643 (1)| 00:01:20 |

| 14| TABLE ACCESS FULL | DMS_OBJ | 63 | 945 | 3 (0)| 00:00:01 |

| 15| TABLE ACCESS FULL | DMS_DATA_RELA | 637K| 83M| 6636 (1)| 00:01:20 |

| 16| COUNT | | | | | |

|* 17 | HASH JOIN | | 637K| 92M| 6643 (1)| 00:01:20 |

| 18| TABLE ACCESS FULL | DMS_OBJ | 63 | 945 | 3 (0)| 00:00:01 |

| 19| TABLE ACCESS FULL | DMS_DATA_RELA | 637K| 83M| 6636 (1)| 00:01:20 |

|* 20 | TABLE ACCESS BY INDEX ROWID | DMS_DATA_RELA | 1 | 82 | 4 (0)| 00:00:01 |

|* 21 | INDEX RANGE SCAN |OUT_DATA_ID_INDEX | 1 | | 3 (0)| 00:00:01 |

----------------------------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

1- filter("R"."DELETE_FLAG"='0' AND"R"."RELA_TYPE_CODE"='parent' AND

"O"."DELETE_FLAG"='0' AND"O"."OPEN_STATE"='1' AND"R"."IN_OBJ_CODE"'o_in')

2- filter( EXISTS (SELECT 0 FROM "DMS_DATA_RELA" "D" WHERE"D"."OUT_DATA_ID"=:B1 AND

"D"."OUT_VER_BEGIN_ID"='20130131036703_syspro_o_wbs'AND "D"."LAST_CURENT_FLAG"='1'))

3- filter( EXISTS (SELECT 0 FROM "DMS_DATA_RELA" "D" WHERE"D"."OUT_DATA_ID"=:B1 AND

"D"."OUT_VER_BEGIN_ID"='20130131036703_syspro_o_wbs'AND "D"."LAST_CURENT_FLAG"='1'))

5-access("R"."OUT_OBJ_CODE"="O"."OBJ_CODE")

8-filter("D"."OUT_VER_BEGIN_ID"='20130131036703_syspro_o_wbs'AND

"D"."LAST_CURENT_FLAG"='1')

9- access("D"."OUT_DATA_ID"=:B1)

10- access("R"."IN_VER_BEGIN_ID"=NULL)

13-access("R"."OUT_OBJ_CODE"="O"."OBJ_CODE")

17-access("R"."OUT_OBJ_CODE"="O"."OBJ_CODE")

20-filter("D"."OUT_VER_BEGIN_ID"='20130131036703_syspro_o_wbs'AND

"D"."LAST_CURENT_FLAG"='1')

21- access("D"."OUT_DATA_ID"=:B1)

统计信息

----------------------------------------------------------

1 recursive calls

0 db block gets

2103709 consistent gets

0 physical reads

0 redo size

596 bytes sent via SQL*Net toclient

350 bytes received via SQL*Netfrom client

2 SQL*Net roundtrips to/fromclient

4 sorts (memory)

0 sorts (disk)

5 rows processed

更多详情见请继续阅读下一页的精彩内容：

相关标签： Oracle connect by Oracle优化 Ora

上一篇：急如何可以通过遍历数据库实现一行多张图片列表而且实现分页功能

下一篇： composer 慢成翔怎么办？

Oracle 有表连接的connect by 的优化

Oracle中三种表连接算法的总结

Oracle中三种表连接算法的总结

优化Oracle库表设计的若干方法

查看Oracle中是否有锁表的sql

Oracle删除当前用户下所有表的方法适用于有或没有删除权限

SQL优化之针对count、表的连接顺序、条件顺序、in及exist的优化

Oracle中三种表连接算法的总结

优化Oracle库表设计的若干方法

查看Oracle中是否有锁表的sql

Oracle查询表中字段里数据是否有重复的方法