前端之家收集整理的这篇文章主要介绍了
Oracle 分区裁剪，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
explain plan for select count(*) from esb2_trans_log t
where t.trans_date >= sysdate - 3 / 1440;
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
1 Plan hash value: 287218081
2
3 ------------------------------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop |
5 ------------------------------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 1 | 8 | 878K (1)| 02:55:42 | | |
7 | 1 | SORT AGGREGATE | | 1 | 8 | | | | |
8 | 2 | PARTITION RANGE ITERATOR| | 177 | 1416 | 878K (1)| 02:55:42 | KEY | 9 |
9 |* 3 | TABLE ACCESS FULL | ESB2_TRANS_LOG | 177 | 1416 | 878K (1)| 02:55:42 | KEY | 9 |
10 ------------------------------------------------------------------------------------------------------------
11
12 Predicate Information (identified by operation id):
13 ---------------------------------------------------
14
15 3 - filter("T"."TRANS_DATE">=SYSDATE@!-.002083333333333333333333333333333333333333)
select * from table(dbms_xplan.display_cursor('btfkf4uww4zv4'));
PLAN_TABLE_OUTPUT
sql_ID btfkf4uww4zv4,child number 0
-------------------------------------
select count(*) from esb2_trans_log t where t.trans_date >= sysdate
- 3 / 1440
Plan hash value: 287218081
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 878K(100)| | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 177 | 1416 | 878K (1)| 02:55:42 | KEY | 9 |
|* 3 | TABLE ACCESS FULL | ESB2_TRANS_LOG | 177 | 1416 | 878K (1)| 02:55:42 | KEY | 9 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T"."TRANS_DATE">=SYSDATE@!-.002083333333333333333333333333333333333333)
explain plan for select *
from esb2_trans_log t
where t.trans_date >=
to_date('2018-05-11 08:00:00','yyyy-mm-dd hh24:mi:ss');
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
1 Plan hash value: 2926439834
2
3 -----------------------------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop |
5 -----------------------------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 155 | 32395 | 2 (0)| 00:00:01 | | |
7 | 1 | PARTITION RANGE ITERATOR| | 155 | 32395 | 2 (0)| 00:00:01 | 8 | 9 |
8 |* 2 | TABLE ACCESS FULL | ESB2_TRANS_LOG | 155 | 32395 | 2 (0)| 00:00:01 | 8 | 9 |
9 -----------------------------------------------------------------------------------------------------------
10
11 Predicate Information (identified by operation id):
12 ---------------------------------------------------
13
14 2 - filter("T"."TRANS_DATE">=TO_DATE(' 2018-05-11 08:00:00','syyyy-mm-dd hh24:mi:ss'))
分区表有很多好处，以大化小，一小化了，加上并行的使用，在loap中能往往能提高几十倍甚至几百倍的效果。当然表设计得不好也会适得其反，效果比普通表跟糟糕。
为了更好的使用分区表，这里看一下分区表的执行计划。
PARTITION RANGE ALL：扫描所有分区
PARTITION RANGE ITERATOR：扫描多个分区，小于所有个分区数量
PARTITION RANGE SINGLE：扫描单一的分区
KEY，表示执行时才知道哪个分区
