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

0

热门评论