空间管理您的位置: ITPUB个人空间 » space6212的个人空间 » 日志

一个与CONNECT BY相关的BUG

上一篇 / 下一篇 2007-06-28 00:00:00 / 个人分类：Oracle技术

今天遇到一个与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

导入论坛引用链接收藏分享给好友推荐到圈子管理举报

TAG:

查看全部评论