首页 / ORACLE / oracle分区表运行计划
oracle分区表运行计划
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了oracle分区表运行计划，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5811字，纯文字阅读大概需要9分钟。
内容图文
分区表有非常多优点，以大化小，一小化了，加上并行的使用，在loap中能往往能提高几十倍甚至几百倍的效果。当然表设计得不好也会适得其反。效果比普通表跟糟糕。 为了更好的使用分区表，这里看一下分区表的运行计划。 PARTITION RANGE ALL：扫描全部分区 PARTITION RANGE ITERATOR：扫描多个分区，小于全部个分区数量 PARTITION RANGE SINGLE：扫描单一的分区 KEY，表示运行时才知道哪个分区 看到keywordALL的时候就要注意了，扫描的是全部分区。
写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'))
oracle分区表运行计划
标签：
本文系统来源：http://www.cnblogs.com/lcchuguo/p/5056150.html
内容总结
以上是互联网集市为您收集整理的oracle分区表运行计划全部内容，希望文章能够帮你解决oracle分区表运行计划所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。