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