Oracle分區表執行計畫

來源:互聯網
上載者:User

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

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.