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 processedSQL> 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 processedSQL> select * from test t
start with （t.id = 12
and t.code = "0306"）
connect by prior t.id = t.parent_id
and prior t.code = "0306";
执行计划
----------------------------------------------------------
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" AND PRIOR "T"."CODE"="0306"）
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 processedSQL> select * from test t
start with t.id = 12
connect by prior t.id = t.parent_id
and prior t.code = "0306";
执行计划
----------------------------------------------------------
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" AND PRIOR "T"."CODE"="0306"）
filter（"T"."ID"=12）
统计信息
----------------------------------------------------------
1 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
只有下面的写法才能用到分区，可以看到t.code = "0306"是关键
SQL> select * from test t
start with （t.id = 12
and t.code = "0306"）
connect by prior t.id = t.parent_id
and t.code = "0306";
执行计划
----------------------------------------------------------
Plan hash value: 3571852076
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 84 | 9 （34）| 00:00:01 | | |
|* 1 | CONNECT BY WITH FILTERING | | | | | | | |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| TEST | 1 | 15 | 1 （0）| 00:00:01 | 6 | 6 |
|* 3 | INDEX UNIQUE SCAN | SYS_C0010758 | 1 | | 0 （0）| 00:00:01 | | |
|* 4 | HASH JOIN | | 1 | 28 | 6 （17）| 00:00:01 | | |
| 5 | CONNECT BY PUMP | | | | | | | |
| 6 | PARTITION LIST SINGLE | | 2 | 30 | 4 （0）| 00:00:01 | KEY | KEY |
| 7 | TABLE ACCESS FULL | TEST | 2 | 30 | 4 （0）| 00:00:01 | 6 | 6 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information （identified by operation id）:
---------------------------------------------------
1 - access（"T"."PARENT_ID"=PRIOR "T"."ID"）
filter（"T"."CODE"="0306"）
2 - filter（"T"."CODE"="0306"）
3 - access（"T"."ID"=12）
4 - access（"connect$_by$_pump$_002"."prior t.id "="T"."PARENT_ID"）
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
16 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
4 sorts （memory）
0 sorts （disk）
2 rows processedSQL> select t.*, prior id, prior parent_id, prior t.name, prior t.code
from test t
start with t.id = 12
connect by prior t.id = t.parent_id
and t.code = "0306";
执行计划
----------------------------------------------------------
Plan hash value: 3043676987
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 84 | 9 （34）| 00:00:01 | | |
|* 1 | CONNECT BY WITH FILTERING | | | | | | | |
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| TEST | 1 | 15 | 1 （0）| 00:00:01 | ROWID | ROWID |
|* 3 | INDEX UNIQUE SCAN | SYS_C0010758 | 1 | | 0 （0）| 00:00:01 | | |
|* 4 | HASH JOIN | | 1 | 28 | 6 （17）| 00:00:01 | | |
| 5 | CONNECT BY PUMP | | | | | | | |
| 6 | PARTITION LIST SINGLE | | 2 | 30 | 4 （0）| 00:00:01 | KEY | KEY |
| 7 | TABLE ACCESS FULL | TEST | 2 | 30 | 4 （0）| 00:00:01 | 6 | 6 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information （identified by operation id）:
---------------------------------------------------
1 - access（"T"."PARENT_ID"=PRIOR "T"."ID"）
filter（"T"."CODE"="0306"）
3 - access（"T"."ID"=12）
4 - access（"connect$_by$_pump$_002"."prior t.id "="T"."PARENT_ID"）更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址