oracle 10.2.0.4執行一個樹查詢的問題

Oracle10204的一個樹查詢的問題：
建立測試環境：
-- Create table
create table TEST
(
  SEQ NUMBER,
  VAL VARCHAR2(6)
);
insert into test (SEQ, VAL) values (3, '001');

insert into test (SEQ, VAL) values (4, '002');

insert into test (SEQ, VAL) values (5, '003');

insert into test (SEQ, VAL) values (2, '004');

insert into test (SEQ, VAL) values (5, '005');

insert into test (SEQ, VAL) values (2, '006');

insert into test (SEQ, VAL) values (3, '007');

insert into test (SEQ, VAL) values (2, '008');

insert into test (SEQ, VAL) values (1, '009');

insert into test (SEQ, VAL) values (5, '010');

SQL> select * from test order by seq;

       SEQ VAL
---------- ------
         1 009
         2 008
         2 004
         2 006
         3 001
         3 007
         4 002
         5 010
         5 003
         5 005

已選擇10行。

 

現在目的是以seq進行分組，連線每個分組下的val字串，如上面seq=3的記錄有兩個對應的val值：001和007，現在將這兩個值合併，按理應該是："001||007”這樣的結果，但是實際的查詢情況卻出現了異常。
取seq=3的記錄進行測試，做查詢：
SQL> select aa.*, sys_connect_by_path(aa.val, '||') strval from (
  2  select t.*, row_number()over(partition by t.seq order by t.seq) rn
  3  from test t where t.seq = 3) aa
  4  start with aa.rn = 1
  5  connect by prior aa.rn = aa.rn - 1
  6  /

       SEQ VAL            RN STRVAL
---------- ------ ---------- --------------------
         3 001             1 ||001
         3 001             2 ||001||001
由以上查詢可以看出並非按照前面我們所預想的結果。那假如是對應４個val的情況呢？見以下查詢：
SQL> select aa.*, sys_connect_by_path(aa.val, '||') strval from (
  2  select t.*, row_number()over(partition by t.seq order by t.seq) rn
  3  from test t where t.seq = 5) aa
  4  start with aa.rn = 1
  5  connect by prior aa.rn = aa.rn - 1
  6  /

       SEQ VAL            RN STRVAL
---------- ------ ---------- --------------------
         5 003             1 ||003
         5 010             2 ||003||010
         5 005             3 ||003||010||005
卻發現此時是正常的。
這個問題現在只在oracle 10.2.0.4這個版本中發現，在10.2.0.1和9i的版本中都未發現這個。按我的理解這個應該是一個bug了，可惜沒有metalink的帳號，不然可以去查檢視了。

執行計劃如下：
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 1593190829

----------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |     4 |   180 |     6  (17)| 00:00:01 |
|*  1 |  CONNECT BY WITH FILTERING|      |       |       |            |          |
|*  2 |   VIEW                    |      |     4 |   376 |     6  (17)| 00:00:01 |
|*  3 |    WINDOW SORT PUSHED RANK|      |     4 |   248 |     6  (17)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL     | TF   |     4 |   248 |     5   (0)| 00:00:01 |
|*  5 |   HASH JOIN               |      |       |       |            |          |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
|   6 |    CONNECT BY PUMP        |      |       |       |            |          |
|   7 |    VIEW                   |      |     4 |   180 |     6  (17)| 00:00:01 |
|   8 |     WINDOW SORT           |      |     4 |   128 |     6  (17)| 00:00:01 |
|*  9 |      TABLE ACCESS FULL    | TF   |     4 |   128 |     5   (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("RN2"-1=PRIOR "RN2")
   2 - filter("RN2"=1)
   3 - filter(ROW_NUMBER() OVER ( PARTITION BY "C_CUSTNO" ORDER BY
              "C_CUSTNO")<=1)
   4 - filter("C_CUSTNO"='000000016643')
   5 - access("RN2"-1=PRIOR "RN2")
   9 - filter("C_CUSTNO"='000000016643')
可以看到Oracle首先做子查詢，然後做connect by形成一個view。然後在做一個子查詢並限制row_number的條件為<=1，然後再做rn2=1的過濾，在執行connect by操作。這段理解的不是很清楚，後面再摸索摸索。
問題原因目前還不知道，但是肯定是Oracle的執行計劃最佳化導致的，因為我如果採用了RBO提示(加了rule提示)，就出現的結果就和我們所想要的結果一致了：
SQL> select /*+rule*/aa.*, sys_connect_by_path(aa.val, '||') strval from (
  2  select t.*, row_number()over(partition by t.seq order by t.seq) rn
  3  from test t where t.seq =3) aa
  4  start with aa.rn = 1
  5  connect by prior aa.rn = aa.rn - 1
  6  /

       SEQ VAL            RN STRVAL
---------- ------ ---------- --------------------
         3 007             1 ||007
         3 001             2 ||007||001

檢視結果，兩個SQL除了hint不同，其他都相同，但是結果完全不同：
SQL> select /*+rule*/aa.*, sys_connect_by_path(aa.val, '||') strval from (
  2  select t.*, row_number()over(partition by t.seq order by t.seq) rn
  3  from test t where t.seq =3) aa
  4  start with aa.rn = 1
  5  connect by prior aa.rn = aa.rn - 1
  6  /

       SEQ VAL            RN STRVAL
---------- ------ ---------- --------------------
         3 007             1 ||007
         3 001             2 ||007||001

SQL> select aa.*, sys_connect_by_path(aa.val, '||') strval from (
  2  select t.*, row_number()over(partition by t.seq order by t.seq) rn
  3  from test t where t.seq =3) aa
  4  start with aa.rn = 1
  5  connect by prior aa.rn = aa.rn - 1
  6  /

       SEQ VAL            RN STRVAL
---------- ------ ---------- --------------------
         3 001             1 ||001
         3 001             2 ||001||001