今天遇到一個與CONNECT BY相關的BUG

資料庫版本是solaris 8 + oracle 9204

SQL> SELECT T2.ID
2 FROM Mis2_stat_all T2
3 WHERE t2.plat_id in
4 (SELECT ID FROM (SELECT ID
5 FROM PLT_PLAT MD
6 START WITH ID in
7 (SELECT m.PLAT_ID
8 FROM MIS2_USR_PLAT m, plt_plat p
9 WHERE USER_ID = 'BUSI10000000000098426422'
10 and m.plat_id = p.id
11 and (p.plat_class = '3' or
12 p.id = 'FR20T0000020000000000132'))
13 CONNECT BY PRIOR ID = PLAT_FATHER)
14 )
15 and id=50666180
16 ;

ID
----------
50666180
50666180

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=57)
1 0 NESTED LOOPS (Cost=6 Card=1 Bytes=57)
2 1 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'MIS2_STAT_ALL'
(Cost=3 Card=1 Bytes=31)

3 2 INDEX (UNIQUE SCAN) OF 'PK_MIS2_STAT_ALL_ID' (UNIQUE)
(Cost=2 Card=7866838)

4 1 VIEW (Cost=3 Card=1 Bytes=26)
5 4 CONNECT BY (WITH FILTERING)
6 5 NESTED LOOPS
7 6 NESTED LOOPS (Cost=71 Card=6 Bytes=612)
8 7 HASH JOIN (Cost=71 Card=6 Bytes=462)
9 8 TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card
=107 Bytes=2889)

10 8 TABLE ACCESS (FULL) OF 'MIS2_USR_PLAT' (Cost=6
7 Card=6 Bytes=300)

11 7 INDEX (UNIQUE SCAN) OF 'PK_PLT_PLAT' (UNIQUE)
12 6 TABLE ACCESS (BY USER ROWID) OF 'PLT_PLAT'
13 5 HASH JOIN
14 13 CONNECT BY PUMP
15 13 TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card=424
Bytes=21200)

16 5 NESTED LOOPS (Cost=69 Card=1 Bytes=77)
17 16 TABLE ACCESS (BY INDEX ROWID) OF 'PLT_PLAT' (Cost=
2 Card=1 Bytes=27)

18 17 INDEX (UNIQUE SCAN) OF 'PK_PLT_PLAT' (UNIQUE) (C
ost=1 Card=424)

19 16 TABLE ACCESS (FULL) OF 'MIS2_USR_PLAT' (Cost=67 Ca
rd=1 Bytes=50)

這個查詢返回2條資料，但問題是ID是主鍵，不應該返回ID相同的兩條資料。
--下面可以證明ID是主鍵
SQL> SELECT COLUMN_NAME FROM USER_CONS_COLUMNS WHERE constraint_name=(select constraint_name from user_constraints where table_name='MIS2_STAT_ALL' AND CONSTRAINT_TYPE='P');

COLUMN_NAME
------------------------------
ID

這是一個bug，與yangtingkun遇到的問題類似（http://yangtingkun.itpub.net/post/468/106206），但yangtingkun遇到的問題是distinct不起作用，我遇到的問題是主鍵做in操作返回多條相同鍵值的記錄，加了distinct可以解決問題（和yangtingkun遇到的問題相反）。
解決方法有兩種：
1、加distinct
SQL> SELECT T2.ID
2 FROM Mis2_stat_all T2
3 WHERE t2.plat_id in
4 (SELECT ID FROM (SELECT DISTINCT ID
5 FROM PLT_PLAT MD
6 START WITH ID in
7 (SELECT m.PLAT_ID
8 FROM MIS2_USR_PLAT m, plt_plat p
9 WHERE USER_ID = 'BUSI10000000000098426422'
10 and m.plat_id = p.id
11 and (p.plat_class = '3' or
12 p.id = 'FR20T0000020000000000132'))
13 CONNECT BY PRIOR ID = PLAT_FATHER)
14 )
15 and id=50666180
16 ;

ID
----------
50666180

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=57)
1 0 NESTED LOOPS (Cost=12 Card=1 Bytes=57)
2 1 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'MIS2_STAT_ALL'
(Cost=3 Card=1 Bytes=31)

3 2 INDEX (UNIQUE SCAN) OF 'PK_MIS2_STAT_ALL_ID' (UNIQUE)
(Cost=2 Card=7866838)

4 1 VIEW (Cost=9 Card=1 Bytes=26)
5 4 SORT (UNIQUE) (Cost=9 Card=424 Bytes=21200)
6 5 CONNECT BY (WITH FILTERING)
7 6 NESTED LOOPS
8 7 NESTED LOOPS (Cost=71 Card=6 Bytes=612)
9 8 HASH JOIN (Cost=71 Card=6 Bytes=462)
10 9 TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Ca
rd=107 Bytes=2889)

11 9 TABLE ACCESS (FULL) OF 'MIS2_USR_PLAT' (Cost
=67 Card=6 Bytes=300)

12 8 INDEX (UNIQUE SCAN) OF 'PK_PLT_PLAT' (UNIQUE)
13 7 TABLE ACCESS (BY USER ROWID) OF 'PLT_PLAT'
14 6 HASH JOIN
15 14 CONNECT BY PUMP
16 14 TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card=4
24 Bytes=21200)

17 6 NESTED LOOPS (Cost=69 Card=1 Bytes=77)
18 17 TABLE ACCESS (BY INDEX ROWID) OF 'PLT_PLAT' (Cos
t=2 Card=1 Bytes=27)

19 18 INDEX (UNIQUE SCAN) OF 'PK_PLT_PLAT' (UNIQUE)
(Cost=1 Card=424)

20 17 TABLE ACCESS (FULL) OF 'MIS2_USR_PLAT' (Cost=67
Card=1 Bytes=50)
對比這個執行計劃與前面出錯的可以發現，正確的執行計劃比錯誤的多了 SORT (UNIQUE) 這一步驟。這個BUG就是因為沒有排重而導致返回多條資料。

2、去掉一層無用的巢狀
SQL> SELECT T2.ID
2 FROM Mis2_stat_all T2
3 WHERE t2.plat_id in
4 (SELECT ID
5 FROM PLT_PLAT MD
6 START WITH ID in (SELECT m.PLAT_ID
7 FROM MIS2_USR_PLAT m, plt_plat p
8 WHERE USER_ID = 'BUSI10000000000098426422'
9 and m.plat_id = p.id
10 and (p.plat_class = '3' or
11 p.id = 'FR20T0000020000000000132'))
12 CONNECT BY PRIOR ID = PLAT_FATHER)
13 and id = 50666180;

ID
----------
50666180

一個與CONNECT BY相關的BUG

相關文章