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 |
-----------------------------------------------------------------------------------------------