今天碰到一个问题，和以前的一篇文章中介绍的问题类似：http://yangtingkun.itpub.net/post/468/106206

在前面那篇文章中，我开始认为是DISTINCT造成的问题，不过根据今天碰到的问题，感觉似乎主要的问题出在树状查询上。

先简单模拟一下问题吧：

SQL> CREATE TABLE TEST (ID NUMBER PRIMARY KEY, FID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> INSERT INTO TEST VALUES (1, 0, 'OBJECT');

已创建 1 行。

SQL> INSERT INTO TEST VALUES (2, 1, 'TABLE');

已创建 1 行。

SQL> INSERT INTO TEST VALUES (3, 1, 'INDEX');

已创建 1 行。

SQL> COMMIT;

提交完成。

下面进行一个简单的查询：

SQL> SELECT ID
2 FROM TEST
3 START WITH FID = 1
4 CONNECT BY PRIOR FID = ID;

ID
----------
2
1
3
1

这个树状查询是从叶节点向上找到父节点，所以查询结果中父节点是重复的。

SQL> SELECT *
2 FROM TEST
3 WHERE ID IN
4 (
5 SELECT ID
6 FROM TEST
7 START WITH FID = 1
8 CONNECT BY PRIOR FID = ID
9 )
10 ;

ID FID NAME
---------- ---------- ------------------------------
1 0 OBJECT
2 1 TABLE
3 1 INDEX

如果使用IN子查询的方法，Oracle会去掉重复的记录。

但是如果在IN和子查询中间加上一层SELECT ID FROM：

SQL> SELECT *
2 FROM TEST
3 WHERE ID IN
4 (
5 SELECT ID
6 FROM
7 (
8 SELECT ID
9 FROM TEST
10 START WITH FID = 1
11 CONNECT BY PRIOR FID = ID
12 )
13 )
14 ;

ID FID NAME
---------- ---------- ------------------------------
2 1 TABLE
1 0 OBJECT
3 1 INDEX
1 0 OBJECT

重复记录出现了，从这里看出，IN并没有起作用。

对比两种情况的执行计划：

SQL> SET AUTOT ON EXP
SQL> SELECT *
2 FROM TEST
3 WHERE ID IN
4 (
5 SELECT ID
6 FROM TEST
7 START WITH FID = 1
8 CONNECT BY PRIOR FID = ID
9 )
10 ;

ID FID NAME
---------- ---------- ------------------------------
1 0 OBJECT
2 1 TABLE
3 1 INDEX

执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 VIEW OF 'VW_NSO_1'
3 2 SORT (UNIQUE)
4 3 CONNECT BY (WITH FILTERING)
5 4 NESTED LOOPS
6 5 TABLE ACCESS (FULL) OF 'TEST'
7 5 TABLE ACCESS (BY USER ROWID) OF 'TEST'
8 4 NESTED LOOPS
9 8 BUFFER (SORT)
10 9 CONNECT BY PUMP
11 8 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
12 11 INDEX (UNIQUE SCAN) OF 'SYS_C008164' (UNIQUE)
13 1 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
14 13 INDEX (UNIQUE SCAN) OF 'SYS_C008164' (UNIQUE)

SQL> SELECT *
2 FROM TEST
3 WHERE ID IN
4 (
5 SELECT ID
6 FROM
7 (
8 SELECT ID
9 FROM TEST
10 START WITH FID = 1
11 CONNECT BY PRIOR FID = ID
12 )
13 )
14 ;

ID FID NAME
---------- ---------- ------------------------------
2 1 TABLE
1 0 OBJECT
3 1 INDEX
1 0 OBJECT

执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 VIEW
3 2 CONNECT BY (WITH FILTERING)
4 3 NESTED LOOPS
5 4 TABLE ACCESS (FULL) OF 'TEST'
6 4 TABLE ACCESS (BY USER ROWID) OF 'TEST'
7 3 NESTED LOOPS
8 7 BUFFER (SORT)
9 8 CONNECT BY PUMP
10 7 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
11 10 INDEX (UNIQUE SCAN) OF 'SYS_C008164' (UNIQUE)
12 1 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
13 12 INDEX (UNIQUE SCAN) OF 'SYS_C008164' (UNIQUE)

二者的执行计划的唯一差别在于错误的情况丢掉了SORT (UNIQUE)这个步骤。

对比前面的那篇文章可以看出，造成问题的原因在于树状查询。当内层子查询为树状查询的时候，如果多层嵌套，Oracle可能在优化的时候，执行计划会丢掉排序唯一这个步骤，导致重复记录的出现。