Oracle分区表的层次查询如何才能用到分区？
sbtmbj00 2015-02-13
最近在调优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
SQL> 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 processed
SQL> 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 processed
SQL> 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")
