oracle分區表執行計畫

標籤：oracle   執行計畫   分區表   表設計   sql最佳化   

分區表有很多好處，以大化小，一小化了，加上並行的使用，在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">[email protected]!-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分區表執行計畫