Home  >  Article  >  Database  >  Oracle分区表的层次查询如何才能用到分区?

Oracle分区表的层次查询如何才能用到分区?

WBOY
WBOYOriginal
2016-06-07 16:08:171187browse

最近在调优Oracle分区表的层次查询时,发现用不到分区,做了一个实验,发现还是可以用的到的,只是写法上有些要求。

最近在调优Oracle分区表的层次查询时,,发现用不到分区,做了一个实验,发现还是可以用的到的,只是写法上有些要求。

drop table test;

create table test
(
  id  number primary key,
  parent_id number,
  name varchar2(20),
  code varchar2(4)
)
partition by list(code)
(
  partition p1 values('0301'),
  partition p2 values('0302'),
  partition p3 values('0303'),
  partition p4 values('0304'),
  partition p5 values('0305'),
  partition p6 values('0306'),
  partition p7 values('0307'),
  partition p8 values('0308'),
  partition p_default values (default)
);
insert into test values(1,0,'a1','0301');
insert into test values(2,1,'a2','0301');
insert into test values(3,2,'a3','0301');
insert into test values(4,3,'a4','0301');
insert into test values(5,0,'a5','0302');
insert into test values(6,5,'a6','0302');
insert into test values(7,6,'a7','0302');
insert into test values(8,7,'a8','0302');
insert into test values(9,8,'a9','0302');
insert into test values(10,0,'a10','0303');
insert into test values(11,0,'a11','0304');
insert into test values(12,0,'a12','0306');
insert into test values(13,0,'a13','0307');
insert into test values(14,0,'a14','0308');
insert into test values(15,10,'a15','0303');
insert into test values(16,11,'a16','0304');
insert into test values(17,12,'a17','0306');
insert into test values(18,13,'a18','0307');
insert into test values(19,14,'a19','0308');
commit;

exec dbms_stats.gather_table_stats(user,'test',cascade => true);

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


SQL> set autotrace traceonly
SQL> select * from test t
    start with t.id = 12
    connect by prior t.id = t.parent_id;
执行计划
----------------------------------------------------------
Plan hash value: 6144290
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                        |      |    19 |  798 |    16  (7)| 00:00:01 |      |      |
|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|      |      |      |            |          |      |      |
|  2 |  PARTITION LIST ALL                    |      |    19 |  285 |    15  (0)| 00:00:01 |    1 |    9 |
|  3 |    TABLE ACCESS FULL                    | TEST |    19 |  285 |    15  (0)| 00:00:01 |    1 |    9 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  1 - access("T"."PARENT_ID"=PRIOR "T"."ID")
      filter("T"."ID"=12)
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        55  consistent gets
          0  physical reads
          0  redo size
        557  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> select * from test t
    start with t.id = 12
            and t.code = '0306'
    connect by prior t.id = t.parent_id;
执行计划
----------------------------------------------------------
Plan hash value: 6144290
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                        |      |    19 |  798 |    16  (7)| 00:00:01 |      |      |
|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|      |      |      |            |          |      |      |
|  2 |  PARTITION LIST ALL                    |      |    19 |  285 |    15  (0)| 00:00:01 |    1 |    9 |
|  3 |    TABLE ACCESS FULL                    | TEST |    19 |  285 |    15  (0)| 00:00:01 |    1 |    9 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  1 - access("T"."PARENT_ID"=PRIOR "T"."ID")
      filter("T"."ID"=12 AND "T"."CODE"='0306')
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        55  consistent gets
          0  physical reads
          0  redo size
        557  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          2  rows processed

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn