Oracle loap函数,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'))

http://www.dengb.com/oracle/930222.htmlwww.dengb.comtruehttp://www.dengb.com/oracle/930222.htmlTechArticleoracle分区表执行计划 分区表有很多好处，以大化小，一小化了，加上并行的使用，在loap中能往往能提高几十倍甚至几百倍的效果。当然表...