ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 10.2.0.4执行一个树查询的问题

oracle 10.2.0.4执行一个树查询的问题

原创 Linux操作系统 作者:regonly1 时间:2009-08-25 15:04:32 0 删除 编辑

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
由以上查询可以看出并非按照前面我们所预想的结果。那假如是对应4个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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-05-10

  • 博文量
    257
  • 访问量
    1021980