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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12932950/viewspace-613199/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12932950/viewspace-613199/