oracle分区表执行计划
来源:互联网 发布:java局域网循环聊天 编辑:程序博客网 时间:2024/06/10 00:26
分区表有很多好处,以大化小,一小化了,加上并行的使用,在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'))
1 0
- oracle分区表执行计划
- Oracle之分区表和普通表的查询执行计划对比
- ORACLE的执行计划
- ORACLE的执行计划
- oracle 执行计划表
- Oracle 执行计划
- oracle执行计划
- Oracle执行计划详解
- Oracle执行计划解释
- ORACLE执行计划
- ORACLE优化--执行计划
- oracle执行计划
- 查看oracle执行计划
- oracle执行计划
- 查看oracle执行计划
- Oracle执行计划详解
- Oracle 执行计划
- oracle执行计划
- enq 命令的帮助.
- Fuzzykmeans集群算法(cluster-reuters)
- Nginx HTTP处理模块工作原理
- Oracle之物化视图整理
- 8大排序算法图文讲解[转]
- oracle分区表执行计划
- kmeans集群算法(cluster-reuters)
- ios8.X中app store app存放目录
- android V4包冲突,遇到最奇葩的一个问题
- mingw环境下boost库的编译和使用
- 对待自己的小生命唯一正确的态度
- 域名注册规则
- spirng注解总结
- 贝叶斯分类(classify-20newsgroups)