oracle层次查询的圈套
日期：2014-05-16 浏览次数：20459 次
oracle层次查询的陷阱
SYS_CONNECT_BY_PATH(C.FID, '>') PATH,
C.fid,
c.fno,
c.node1_id,
c.node2_id
FROM CONN_device C
START WITH C.FNO = 314
CONNECT BY NOCYCLE((PRIOR NODE2_ID = NODE2_ID)
AND FNO = 316)
and NODE1_ID >= 1
and level = 2;
表结构和索引如下，为了保障公司隐私，定义做了下处理。
create table CONN_device
(
FID NUMBER(10) not null,
FNO NUMBER(5) not null,
NODE1_ID NUMBER(10),
NODE2_ID NUMBER(10)
);
BITIDX_CONN314316_FNO 是FNO的索引
BITIDX_CONN314316_NODE2 是NODE2_ID的索引
用10046 trace出来的内容（等了好一会）：
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 764 849.28 850.34 214 6199353 0 11446
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 766 849.28 850.35 214 6199353 0 11446
Rows Row Source Operation
------- ---------------------------------------------------
11446 CONNECT BY WITH FILTERING (cr=6199353 pr=214 pw=0 time=852238260 us)
38549 TABLE ACCESS BY INDEX ROWID CONN314316 (cr=123 pr=0 pw=0 time=38623 us)
38549 BITMAP CONVERSION TO ROWIDS (cr=3 pr=0 pw=0 time=63 us)
2 BITMAP INDEX FAST FULL SCAN BITIDX_CONN314316_FNO (cr=3 pr=0 pw=0 time=50 us)(object id 137347)
50661964 NESTED LOOPS (cr=6199230 pr=214 pw=0 time=2178484091 us)
40137 BUFFER SORT (cr=0 pr=0 pw=0 time=93254 us)
40137 CONNECT BY PUMP (cr=0 pr=0 pw=0 time=28 us)
50661964 FILTER (cr=6199230 pr=214 pw=0 time=227928248 us)
50661964 TABLE ACCESS BY INDEX ROWID CONN314316 (cr=6199230 pr=214 pw=0 time=227783163 us)
1068789015 INDEX RANGE SCAN BITIDX_CONN314316_NODE2 (cr=1980040 pr=82 pw=0 time=587199 us)(object id 137349)
0 TABLE ACCESS FULL CONN314316 (cr=0 pr=0 pw=0 time=0 us)
上面的计划看返回行数最大的那个1068789015，connect by原理是记录之间自关联，所以我怀疑是NODE2有大量重复数据。
select count(1) from CONN314316;--872342
今天开发组同事找到我，说一个简单的层次查询非常慢，业务就是有一个存设备表连接关系的表，从node1连入，从node2连出，现在要找出node2的连出顺序，sql类似于：
SYS_CONNECT_BY_PATH(C.FID, '>') PATH,
C.fid,
c.fno,
c.node1_id,
c.node2_id
FROM CONN_device C
START WITH C.FNO = 314
CONNECT BY NOCYCLE((PRIOR NODE2_ID = NODE2_ID)
AND FNO = 316)
and NODE1_ID >= 1
and level = 2;
表结构和索引如下，为了保障公司隐私，定义做了下处理。
create table CONN_device
(
FID NUMBER(10) not null,
FNO NUMBER(5) not null,
NODE1_ID NUMBER(10),
NODE2_ID NUMBER(10)
);
BITIDX_CONN314316_FNO 是FNO的索引
BITIDX_CONN314316_NODE2 是NODE2_ID的索引
用10046 trace出来的内容（等了好一会）：
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 764 849.28 850.34 214 6199353 0 11446
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 766 849.28 850.35 214 6199353 0 11446
Rows Row Source Operation
------- ---------------------------------------------------
11446 CONNECT BY WITH FILTERING (cr=6199353 pr=214 pw=0 time=852238260 us)
38549 TABLE ACCESS BY INDEX ROWID CONN314316 (cr=123 pr=0 pw=0 time=38623 us)
38549 BITMAP CONVERSION TO ROWIDS (cr=3 pr=0 pw=0 time=63 us)
2 BITMAP INDEX FAST FULL SCAN BITIDX_CONN314316_FNO (cr=3 pr=0 pw=0 time=50 us)(object id 137347)
50661964 NESTED LOOPS (cr=6199230 pr=214 pw=0 time=2178484091 us)
40137 BUFFER SORT (cr=0 pr=0 pw=0 time=93254 us)
40137 CONNECT BY PUMP (cr=0 pr=0 pw=0 time=28 us)
50661964 FILTER (cr=6199230 pr=214 pw=0 time=227928248 us)
50661964 TABLE ACCESS BY INDEX ROWID CONN314316 (cr=6199230 pr=214 pw=0 time=227783163 us)
1068789015 INDEX RANGE SCAN BITIDX_CONN314316_NODE2 (cr=1980040 pr=82 pw=0 time=587199 us)(object id 137349)
0 TABLE ACCESS FULL CONN314316 (cr=0 pr=0 pw=0 time=0 us)
上面的计划看返回行数最大的那个1068789015，connect by原理是记录之间自关联，所以我怀疑是NODE2有大量重复数据。
select count(1) from CONN314316;--872342
sel
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
