CREATE TABLE TEST1
(
A CHAR(16BYTE),
B CHAR(16BYTE)
);
insert into  test1 (A,B) VALUES ('1','A');
insert into  test1 (A,B) VALUES ('1','B');
insert into  test1 (A,B) VALUES ('2','C');
insert into  test1 (A,B) VALUES ('2','D');
insert into  test1 (A,B) VALUES ('2','E');
insert into  test1 (A,B) VALUES ('2','C');
SQL> select * from v$version;
BANNER
------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

SQL>  set linesize 200
SQL>  select  A,
  2   MAX(sys_connect_by_path(B, ';')) result
  3     from (select A,
  4                  B,
  5                  (row_number() over(order by A, B desc) + dense_rank()
  6                   over(order by A)) rn,
  7                  max(B) over(partition by A) qs
  8             from (SELECT DISTINCT B,A FROM TEST1)) test1
  9    start with B = qs  
10   connect by rn - 1 = prior rn
11    group by A
12  /
A                RESULT
---------------- -----------------------------------------------------------------------------------
1                ;B               ;A
2                ;E               ;D               ;C               ;C

这里C出现了2次,说明了
SELECT DISTINCT B,A FROM TEST1里的distinct没有起到作用

10g测试:

SQL> col result for a100
SQL> set line 200
SQL> select  A,
  2  MAX(sys_connect_by_path(B, ';')) result
  3    from (select A,
  4                 B,
  5                 (row_number() over(order by A, B desc) + dense_rank()
  6                  over(order by A)) rn,
  7                 max(B) over(partition by A) qs
  8            from (SELECT DISTINCT B,A FROM TEST1)) test1
  9   start with B = qs  
10  connect by rn - 1 = prior rn
11   group by A
12  /
A                RESULT
---------------- -------------------------------------------------------------
1                ;B               ;A
2                ;E               ;D               ;C

10g里得到的结果是正确的

9i的执行计划:
SQL> set autot trace exp
SQL>  select  A,
  2   MAX(sys_connect_by_path(B, ';')) result
  3     from (select A,
  4                  B,
  5                  (row_number() over(order by A, B desc) + dense_rank()
  6                   over(order by A)) rn,
  7                  max(B) over(partition by A) qs
  8             from (SELECT DISTINCT B,A FROM TEST1)) test1
  9    start with B = qs  
10   connect by rn - 1 = prior rn
11    group by A
12  /
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=4 Card=2 Bytes=132
          )
   1    0   SORT (GROUP BY) (Cost=4 Card=2 Bytes=132)
   2    1     CONNECT BY (WITH FILTERING)
   3    2       FILTER
   4    3         COUNT
   5    4           VIEW (Cost=4 Card=6 Bytes=396)
   6    5             WINDOW (SORT) (Cost=4 Card=6 Bytes=204)
   7    6               TABLE ACCESS (FULL) OF 'TEST1' (Cost=2 Card=6
          Bytes=204)
   8    2       HASH JOIN
   9    8         CONNECT BY PUMP
  10    8         COUNT
  11   10           VIEW (Cost=4 Card=6 Bytes=396)
  12   11             WINDOW (SORT) (Cost=4 Card=6 Bytes=204)
  13   12               TABLE ACCESS (FULL) OF 'TEST1' (Cost=2 Card=6
          Bytes=204)

10g的执行计划:

执行计划
----------------------------------------------------------
Plan hash value: 3437455765
--------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |    82 |  5494 |     6 |
|   1 |  SORT GROUP BY             |       |    82 |  5494 |     6 |
|*  2 |   CONNECT BY WITH FILTERING|       |       |       |       |
|*  3 |    FILTER                  |       |       |       |       |
|   4 |     COUNT                  |       |       |       |       |
|   5 |      VIEW                  |       |    82 |  5494 |     6 |
|   6 |       WINDOW SORT          |       |    82 |  2952 |     6 |
|   7 |        VIEW                |       |    82 |  2952 |     4 |
|   8 |         SORT UNIQUE        |       |    82 |  2952 |     4 |
|   9 |          TABLE ACCESS FULL | TEST1 |    82 |  2952 |     1 |
|* 10 |    HASH JOIN               |       |       |       |       |
|  11 |     CONNECT BY PUMP        |       |       |       |       |
|  12 |     COUNT                  |       |       |       |       |
|  13 |      VIEW                  |       |    82 |  5494 |     6 |
|  14 |       WINDOW SORT          |       |    82 |  2952 |     6 |
|  15 |        VIEW                |       |    82 |  2952 |     4 |
|  16 |         SORT UNIQUE        |       |    82 |  2952 |     4 |
|  17 |          TABLE ACCESS FULL | TEST1 |    82 |  2952 |     1 |
|  18 |    COUNT                   |       |       |       |       |
|  19 |     VIEW                   |       |    82 |  5494 |     6 |
|  20 |      WINDOW SORT           |       |    82 |  2952 |     6 |
|  21 |       VIEW                 |       |    82 |  2952 |     4 |
|  22 |        SORT UNIQUE         |       |    82 |  2952 |     4 |
|  23 |         TABLE ACCESS FULL  | TEST1 |    82 |  2952 |     1 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("RN"-1=PRIOR "RN")
   3 - filter("B"="QS")
  10 - access("RN"-1=PRIOR "RN")
Note
-----
   - cpu costing is off (consider enabling it)

发现在9i里对select distinct b,a from test1 做了view merge，最后把sort unique搞丢了，因此导致了错误的结果,而在10g里oracle并没有做 view merge，所以得到的结果没有
错误，9i下的解决方法就是避免让oracle做先取唯一值,或者禁用view merge,下面给出2个解决
方法:
1.
SQL> select  A,
  2  MAX(sys_connect_by_path(B, ';')) result
  3    from (select A,
  4                 B,
  5                 (row_number() over(order by A, B desc) + dense_rank()
  6                  over(order by A)) rn,
  7                 max(B) over(partition by A) qs
  8            from (SELECT DISTINCT B,A FROM TEST1 group by b,a)) test1
  9   start with B = qs  
10  connect by rn - 1 = prior rn
11   group by A
12  /
A                RESULT
---------------- -------------------------------------------------------------
1                ;B               ;A
2                ;E               ;D               ;C

2.
SQL> select  A,
  2  MAX(sys_connect_by_path(B, ';')) result
  3    from (select /*+ no_merge(test1) */ A,
  4                 B,
  5                 (row_number() over(order by A, B desc) + dense_rank()
  6                  over(order by A)) rn,
  7                 max(B) over(partition by A) qs
  8            from (SELECT DISTINCT B,A FROM TEST1) test1) test1
  9   start with B = qs  
10  connect by rn - 1 = prior rn
11   group by A
12  /
A                RESULT
---------------- -------------------------------------------------------------------
1                ;B               ;A
2                ;E               ;D               ;C
SQL> set autot trace
SQL> /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=5 Card=2 Bytes=132
          )
   1    0   SORT (GROUP BY) (Cost=5 Card=2 Bytes=132)
   2    1     CONNECT BY (WITH FILTERING)
   3    2       FILTER
   4    3         COUNT
   5    4           VIEW (Cost=5 Card=6 Bytes=396)
   6    5             WINDOW (SORT) (Cost=5 Card=6 Bytes=216)
   7    6               VIEW (Cost=4 Card=6 Bytes=216)
   8    7                 SORT (UNIQUE) (Cost=4 Card=6 Bytes=204)
   9    8                   TABLE ACCESS (FULL) OF 'TEST1' (Cost=2 Car
          d=6 Bytes=204)
  10    2       HASH JOIN
  11   10         CONNECT BY PUMP
  12   10         COUNT
  13   12           VIEW (Cost=5 Card=6 Bytes=396)
  14   13             WINDOW (SORT) (Cost=5 Card=6 Bytes=216)
  15   14               VIEW (Cost=4 Card=6 Bytes=216)
  16   15                 SORT (UNIQUE) (Cost=4 Card=6 Bytes=204)
  17   16                   TABLE ACCESS (FULL) OF 'TEST1' (Cost=2 Car
          d=6 Bytes=204)

no_merge后的执行计划就和10g的一致了