Oracle分区表执行计划

Oracle分区表有很多好处，以大化小，一小化了，加上并行的使用，在loap中能往往能提高几十倍甚至几百倍的效果。当然表设计得不好也会适得其反，效果比普通表跟糟糕。
为了更好的使用分区表，这里看一下分区表的执行计划。
PARTITION RANGE ALL：扫描所有分区
PARTITION RANGE ITERATOR：扫描多个分区，小于所有个分区数量
PARTITION RANGE SINGLE：扫描单一的分区
KEY，表示执行时才知道哪个分区看到关键字ALL的时候就要注意了，扫描的是全部分区。写sql的时候在where条件中能充分利用分区字段来限制的话最好，这样能起到分区裁剪的作用，没必要的分区就不用扫描了。SQL> create table t1
2 partition by range（created）（
3 partition p1 values less than （to_date（"20140101","yyyymmdd"））,
4 partition p2 values less than （to_date（"20140201","yyyymmdd"））,
5 partition p3 values less than （to_date（"20140301","yyyymmdd"））,
6 partition p4 values less than （to_date（"20140401","yyyymmdd"））,
7 partition p5 values less than （to_date（"20140501","yyyymmdd"））,
8 partition p6 values less than （to_date（"20140601","yyyymmdd"））,
9 partition p7 values less than （to_date（"20140701","yyyymmdd"））,
10 partition p8 values less than （to_date（"20140801","yyyymmdd"））,
11 partition p9 values less than （to_date（"20140901","yyyymmdd"））,
12 partition p10 values less than （to_date（"20141001","yyyymmdd"））,
13 partition p11 values less than （to_date（"20141101","yyyymmdd"））,
14 partition p12 values less than （to_date（"20141201","yyyymmdd"））,
15 partition p13 values less than （maxvalue）
16 ）
17 as select * from dba_objects where created>=to_date（"20131001","yyyymmdd"）;

--PARTITION RANGE ALL：扫描所有分区
SQL> explain plan for select count（*） from t1;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost （%CPU）| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 106 （1）| 00:00:02 | | |
| 1 | SORT AGGREGATE | | 1 | | | | |
| 2 | PARTITION RANGE ALL| | 41973 | 106 （1）| 00:00:02 | 1 | 13 |
| 3 | TABLE ACCESS FULL | T1 | 41973 | 106 （1）| 00:00:02 | 1 | 13 |
---------------------------------------------------------------------------------------PARTITION RANGE ITERATOR：扫描多个分区，小于所有个分区数量
SQL> explain plan for select * from t1 where created>=to_date（"20141101","yyyymmdd"）;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13121 | 2267K| 39 （6）| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR| | 13121 | 2267K| 39 （6）| 00:00:01 | 12 | 13 |
|* 2 | TABLE ACCESS FULL | T1 | 13121 | 2267K| 39 （6）| 00:00:01 | 12 | 13 |
------------------------------------------------------------------------------------------------- --PARTITION RANGE SINGLE：扫描单一的分区
SQL> explain plan for select * from t1 where created>=to_date（"20141217","yyyymmdd"）;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 947 | 163K| 28 （0）| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 947 | 163K| 28 （0）| 00:00:01 | 13 | 13 |
|* 2 | TABLE ACCESS FULL | T1 | 947 | 163K| 28 （0）| 00:00:01 | 13 | 13 |
-------------------------------------------------------------------------------------------------KEY，表示执行时才知道哪个分区
SQL> explain plan for select * from t1 where created>=sysdate-1;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 947 | 163K| 33 （16）| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR| | 947 | 163K| 33 （16）| 00:00:01 | KEY | 13 |
|* 2 | TABLE ACCESS FULL | T1 | 947 | 163K| 33 （16）| 00:00:01 | KEY | 13 |
-------------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:
--------------------------------------------------- 2 - filter（"CREATED">=SYSDATE@！-1）SQL> variable x varchar2;
SQL> explain plan for select * from t1 where created>=to_date（:x,"yyyymmdd"）;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2099 | 362K| 107 （2）| 00:00:02 | | |
| 1 | PARTITION RANGE ITERATOR| | 2099 | 362K| 107 （2）| 00:00:02 | KEY | 13 |
|* 2 | TABLE ACCESS FULL | T1 | 2099 | 362K| 107 （2）| 00:00:02 | KEY | 13 |
-------------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:
--------------------------------------------------- 2 - filter（"CREATED">=TO_DATE（:X,"yyyymmdd"））更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址