oracle层次查询的陷阱

今天开发组同事找到我，说一个简单的层次查询非常慢，业务就是有一个存设备表连接关系的表，从node1连入，从node2连出，现在要找出node2的连出顺序，sql类似于：

 SELECT LEVEL ID,
       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
select count(1) from CONN314316 where NODE2_ID=0;--454805

果然命中，罪魁祸NODE2_ID=0的记录。与开发沟通，按照业务来说，NODE2_ID=0可以不用管，那改起来就方便了，执行后不到1s：

SELECT LEVEL ID,
       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 and NODE2_ID<>0)
CONNECT BY NOCYCLE((PRIOR NODE2_ID = NODE2_ID)
               AND FNO = 316)
       and NODE1_ID >= 1
       and level = 2;