索引优化系列十三--分区表各类聚合优化玄机

设计模式 前端之家
前端之家收集整理的这篇文章主要介绍了索引优化系列十三--分区表各类聚合优化玄机，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
@H_502_0@-- 范围分区示例

@H_502_0@drop table range_part_tab purge;

@H_502_0@--注意，此分区为范围分区

@H_502_0@

@H_502_0@--例子1

@H_502_0@create table range_part_tab (id number,deal_date date,area_code number,nbr number,contents varchar2(4000))

@H_502_0@ partition by range (deal_date)

@H_502_0@ (

@H_502_0@ partition p_201301 values less than (TO_DATE('2013-02-01','YYYY-MM-DD')),

@H_502_0@ partition p_201302 values less than (TO_DATE('2013-03-01',

@H_502_0@ partition p_201303 values less than (TO_DATE('2013-04-01',

@H_502_0@ partition p_201304 values less than (TO_DATE('2013-05-01',

@H_502_0@ partition p_201305 values less than (TO_DATE('2013-06-01',

@H_502_0@ partition p_201306 values less than (TO_DATE('2013-07-01',

@H_502_0@ partition p_201307 values less than (TO_DATE('2013-08-01',

@H_502_0@ partition p_201308 values less than (TO_DATE('2013-09-01',

@H_502_0@ partition p_201309 values less than (TO_DATE('2013-10-01',

@H_502_0@ partition p_201310 values less than (TO_DATE('2013-11-01',

@H_502_0@ partition p_201311 values less than (TO_DATE('2013-12-01',

@H_502_0@ partition p_201312 values less than (TO_DATE('2014-01-01',

@H_502_0@ partition p_201401 values less than (TO_DATE('2014-02-01',

@H_502_0@ partition p_201402 values less than (TO_DATE('2014-03-01',

@H_502_0@ partition p_max values less than (maxvalue)

@H_502_0@ )

@H_502_0@ ;

@H_502_0@

@H_502_0@alter table RANGE_PART_TAB modify nbr not null;

@H_502_0@--以下是插入2013年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下：

@H_502_0@insert into range_part_tab (id,deal_date,area_code,nbr,contents)

@H_502_0@ select rownum,

@H_502_0@ to_date( to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'),

@H_502_0@ ceil(dbms_random.value(591,599)),

@H_502_0@ ceil(dbms_random.value(18900000001,18999999999)),

@H_502_0@ rpad('*',400,'*')

@H_502_0@ from dual

@H_502_0@ connect by rownum <= 100000;

@H_502_0@commit;

@H_502_0@

@H_502_0@

@H_502_0@

@H_502_0@--以下是插入2014年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下：

@H_502_0@insert into range_part_tab (id,

@H_502_0@ to_date( to_char(sysdate,'*')

@H_502_0@ from dual

@H_502_0@ connect by rownum <= 100000;

@H_502_0@commit;

@H_502_0@

@H_502_0@

@H_502_0@

@H_502_0@create index idx_part_id on range_part_tab (id) ;

@H_502_0@create index idx_part_nbr on range_part_tab (nbr) local;

@H_502_0@

@H_502_0@--统计信息系统一般会自动收集，这只是首次建成表后需要操作一下，以方便测试

@H_502_0@exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'RANGE_PART_TAB',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ;

@H_502_0@

@H_502_0@

@H_502_0@set autotrace on

@H_502_0@set linesize 1000

@H_502_0@

@H_502_0@

@H_502_0@select max(nbr) max_nbr from range_part_tab partition(p_201305);

@H_502_0@执行计划

@H_502_0@------------------------------------------------------------------------------------------------------------

@H_502_0@| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop |

@H_502_0@------------------------------------------------------------------------------------------------------------

@H_502_0@| 0 | SELECT STATEMENT | | 1 | 8 | 2 (0)| 00:00:01 | | |

@H_502_0@| 1 | SORT AGGREGATE | | 1 | 8 | | | | |

@H_502_0@| 2 | PARTITION RANGE SINGLE | | 1 | 8 | 2 (0)| 00:00:01 | 5 | 5 |

@H_502_0@| 3 | INDEX FULL SCAN (MIN/MAX)| IDX_PART_NBR | 1 | 8 | 2 (0)| 00:00:01 | 5 | 5 |

@H_502_0@------------------------------------------------------------------------------------------------------------

@H_502_0@统计信息

@H_502_0@----------------------------------------------------------

@H_502_0@ 0 recursive calls

@H_502_0@ 0 db block gets

@H_502_0@ 2 consistent gets

@H_502_0@

@H_502_0@select max(nbr) max_nbr

@H_502_0@ from range_part_tab

@H_502_0@where deal_date >= TO_DATE('2013-05-01','YYYY-MM-DD')

@H_502_0@ and deal_date < TO_DATE('2013-06-01','YYYY-MM-DD');

@H_502_0@执行计划

@H_502_0@----------------------------------------------------------------------------------------------------------

@H_502_0@| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop |

@H_502_0@----------------------------------------------------------------------------------------------------------

@H_502_0@| 0 | SELECT STATEMENT | | 1 | 17 | 170 (0)| 00:00:03 | | |

@H_502_0@| 1 | SORT AGGREGATE | | 1 | 17 | | | | |

@H_502_0@| 2 | PARTITION RANGE SINGLE| | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 |

@H_502_0@| 3 | TABLE ACCESS FULL | RANGE_PART_TAB | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 |

@H_502_0@----------------------------------------------------------------------------------------------------------

@H_502_0@统计信息

@H_502_0@----------------------------------------------------------

@H_502_0@ 0 recursive calls

@H_502_0@ 0 db block gets

@H_502_0@ 568 consistent gets

@H_502_0@

@H_502_0@

@H_502_0@

@H_502_0@

@H_502_0@

@H_502_0@

@H_502_0@

@H_502_0@

@H_502_0@select count(*) max_nbr from range_part_tab partition(p_201305);

@H_502_0@执行计划

@H_502_0@------------------------------------------------------------------------------------------------

@H_502_0@| Id | Operation | Name | Rows | Cost (%cpu)| Time | Pstart| Pstop |

@H_502_0@------------------------------------------------------------------------------------------------

@H_502_0@| 0 | SELECT STATEMENT | | 1 | 8 (0)| 00:00:01 | | |

@H_502_0@| 1 | SORT AGGREGATE | | 1 | | | | |

@H_502_0@| 2 | PARTITION RANGE SINGLE| | 8716 | 8 (0)| 00:00:01 | 5 | 5 |

@H_502_0@| 3 | INDEX FAST FULL SCAN | IDX_PART_NBR | 8716 | 8 (0)| 00:00:01 | 5 | 5 |

@H_502_0@------------------------------------------------------------------------------------------------

@H_502_0@统计信息

@H_502_0@----------------------------------------------------------

@H_502_0@ 0 recursive calls

@H_502_0@ 0 db block gets

@H_502_0@ 29 consistent gets

@H_502_0@

@H_502_0@select count(*) max_nbr

@H_502_0@ from range_part_tab

@H_502_0@where deal_date >= TO_DATE('2013-05-01','YYYY-MM-DD');

@H_502_0@执行计划

@H_502_0@----------------------------------------------------------------------------------------------------------

@H_502_0@| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop |

@H_502_0@----------------------------------------------------------------------------------------------------------

@H_502_0@| 0 | SELECT STATEMENT | | 1 | 9 | 170 (0)| 00:00:03 | | |

@H_502_0@| 1 | SORT AGGREGATE | | 1 | 9 | | | | |

@H_502_0@| 2 | PARTITION RANGE SINGLE| | 22 | 198 | 170 (0)| 00:00:03 | 5 | 5 |

@H_502_0@| 3 | TABLE ACCESS FULL | RANGE_PART_TAB | 22 | 198 | 170 (0)| 00:00:03 | 5 | 5 |

@H_502_0@----------------------------------------------------------------------------------------------------------

@H_502_0@统计信息

@H_502_0@----------------------------------------------------------

@H_502_0@ 0 recursive calls

@H_502_0@ 0 db block gets

@H_502_0@ 568 consistent gets

@H_502_0@

@H_502_0@

@H_502_0@

@H_502_0@

@H_502_0@select sum(nbr) max_nbr from range_part_tab partition(p_201305);

@H_502_0@执行计划

@H_502_0@--------------------------------------------------------------------------------------------------------

@H_502_0@| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop |

@H_502_0@--------------------------------------------------------------------------------------------------------

@H_502_0@| 0 | SELECT STATEMENT | | 1 | 8 | 8 (0)| 00:00:01 | | |

@H_502_0@| 1 | SORT AGGREGATE | | 1 | 8 | | | | |

@H_502_0@| 2 | PARTITION RANGE SINGLE| | 8716 | 69728 | 8 (0)| 00:00:01 | 5 | 5 |

@H_502_0@| 3 | INDEX FAST FULL SCAN | IDX_PART_NBR | 8716 | 69728 | 8 (0)| 00:00:01 | 5 | 5 |

@H_502_0@--------------------------------------------------------------------------------------------------------

@H_502_0@统计信息

@H_502_0@----------------------------------------------------------

@H_502_0@ 0 recursive calls

@H_502_0@ 0 db block gets

@H_502_0@ 29 consistent gets

@H_502_0@

@H_502_0@select sum(nbr) max_nbr

@H_502_0@ from range_part_tab

@H_502_0@where deal_date >= TO_DATE('2013-05-01','YYYY-MM-DD');

@H_502_0@执行计划

@H_502_0@----------------------------------------------------------------------------------------------------------

@H_502_0@| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop |

@H_502_0@----------------------------------------------------------------------------------------------------------

@H_502_0@| 0 | SELECT STATEMENT | | 1 | 17 | 170 (0)| 00:00:03 | | |

@H_502_0@| 1 | SORT AGGREGATE | | 1 | 17 | | | | |

@H_502_0@| 2 | PARTITION RANGE SINGLE| | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 |

@H_502_0@| 3 | TABLE ACCESS FULL | RANGE_PART_TAB | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 |

@H_502_0@----------------------------------------------------------------------------------------------------------

@H_502_0@统计信息

@H_502_0@----------------------------------------------------------

@H_502_0@ 0 recursive calls

@H_502_0@ 0 db block gets

@H_502_0@ 568 consistent gets

@H_502_0@

@H_502_0@

@H_502_0@

@H_502_0@select distinct(nbr) from range_part_tab partition(p_201305);

@H_502_0@执行计划

@H_502_0@--------------------------------------------------------------------------------------------------------

@H_502_0@| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop |

@H_502_0@--------------------------------------------------------------------------------------------------------

@H_502_0@| 0 | SELECT STATEMENT | | 8660 | 69280 | 9 (12)| 00:00:01 | | |

@H_502_0@| 1 | HASH UNIQUE | | 8660 | 69280 | 9 (12)| 00:00:01 | | |

@H_502_0@| 2 | PARTITION RANGE SINGLE| | 8716 | 69728 | 8 (0)| 00:00:01 | 5 | 5 |

@H_502_0@| 3 | INDEX FAST FULL SCAN | IDX_PART_NBR | 8716 | 69728 | 8 (0)| 00:00:01 | 5 | 5 |

@H_502_0@--------------------------------------------------------------------------------------------------------

@H_502_0@统计信息

@H_502_0@----------------------------------------------------------

@H_502_0@ 0 recursive calls

@H_502_0@ 0 db block gets

@H_502_0@ 29 consistent gets

@H_502_0@ 0 physical reads

@H_502_0@ 0 redo size

@H_502_0@ 152890 bytes sent via sql*Net to client

@H_502_0@ 6741 bytes received via sql*Net from client

@H_502_0@ 577 sql*Net roundtrips to/from client

@H_502_0@ 0 sorts (memory)

@H_502_0@ 0 sorts (disk)

@H_502_0@ 8635 rows processed

@H_502_0@

@H_502_0@select distinct(nbr)

@H_502_0@ from range_part_tab

@H_502_0@where deal_date >= TO_DATE('2013-05-01','YYYY-MM-DD');

@H_502_0@执行计划

@H_502_0@----------------------------------------------------------------------------------------------------------

@H_502_0@| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop |

@H_502_0@----------------------------------------------------------------------------------------------------------

@H_502_0@| 0 | SELECT STATEMENT | | 22 | 374 | 171 (1)| 00:00:03 | | |

@H_502_0@| 1 | HASH UNIQUE | | 22 | 374 | 171 (1)| 00:00:03 | | |

@H_502_0@| 2 | PARTITION RANGE SINGLE| | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 |

@H_502_0@| 3 | TABLE ACCESS FULL | RANGE_PART_TAB | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 |

@H_502_0@----------------------------------------------------------------------------------------------------------

@H_502_0@统计信息

@H_502_0@----------------------------------------------------------

@H_502_0@ 0 recursive calls

@H_502_0@ 0 db block gets

@H_502_0@ 568 consistent gets

@H_502_0@ 0 physical reads

@H_502_0@ 0 redo size

@H_502_0@ 152886 bytes sent via sql*Net to client

@H_502_0@ 6741 bytes received via sql*Net from client

@H_502_0@ 577 sql*Net roundtrips to/from client

@H_502_0@ 0 sorts (memory)

@H_502_0@ 0 sorts (disk)

@H_502_0@ 8635 rows processed

@H_502_0@

@H_502_0@

@H_502_0@

@H_502_0@

@H_502_0@

@H_502_0@select count(*)

@H_502_0@ from range_part_tab

@H_502_0@where deal_date >= TO_DATE('2013-05-01 00:00:00','YYYY-MM-DD hh24:mi:ss')

@H_502_0@ and deal_date <= TO_DATE('2013-06-01 00:00:00','YYYY-MM-DD hh24:mi:ss');

@H_502_0@ COUNT(*)

@H_502_0@----------

@H_502_0@ 8635

@H_502_0@执行计划

@H_502_0@------------------------------------------------------------------------------------------------------------

@H_502_0@| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop |

@H_502_0@------------------------------------------------------------------------------------------------------------

@H_502_0@| 0 | SELECT STATEMENT | | 1 | 9 | 340 (1)| 00:00:05 | | |

@H_502_0@| 1 | SORT AGGREGATE | | 1 | 9 | | | | |

@H_502_0@| 2 | PARTITION RANGE ITERATOR| | 497 | 4473 | 340 (1)| 00:00:05 | 5 | 6 |

@H_502_0@|* 3 | TABLE ACCESS FULL | RANGE_PART_TAB | 497 | 4473 | 340 (1)| 00:00:05 | 5 | 6 |

@H_502_0@------------------------------------------------------------------------------------------------------------

@H_502_0@统计信息

@H_502_0@----------------------------------------------------------

@H_502_0@ 0 recursive calls

@H_502_0@ 0 db block gets

@H_502_0@ 1136 consistent gets

@H_502_0@

@H_502_0@select count(*)

@H_502_0@ from range_part_tab

@H_502_0@where deal_date >= TO_DATE('2013-05-01 00:00:00','YYYY-MM-DD hh24:mi:ss')

@H_502_0@ and deal_date < TO_DATE('2013-06-01 00:00:00','YYYY-MM-DD hh24:mi:ss');

@H_502_0@ COUNT(*)

@H_502_0@----------

@H_502_0@ 8635

@H_502_0@执行计划

@H_502_0@----------------------------------------------------------------------------------------------------------

@H_502_0@| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop |

@H_502_0@----------------------------------------------------------------------------------------------------------

@H_502_0@| 0 | SELECT STATEMENT | | 1 | 9 | 170 (0)| 00:00:03 | | |

@H_502_0@| 1 | SORT AGGREGATE | | 1 | 9 | | | | |

@H_502_0@| 2 | PARTITION RANGE SINGLE| | 22 | 198 | 170 (0)| 00:00:03 | 5 | 5 |

@H_502_0@| 3 | TABLE ACCESS FULL | RANGE_PART_TAB | 22 | 198 | 170 (0)| 00:00:03 | 5 | 5 |

@H_502_0@----------------------------------------------------------------------------------------------------------

@H_502_0@统计信息

@H_502_0@----------------------------------------------------------

@H_502_0@ 0 recursive calls

@H_502_0@ 0 db block gets

@H_502_0@ 568 consistent gets

猜你在找的设计模式相关文章

适配器模式-让不兼容的接口得以适配
适配器模式将一个类的接口转换成客户期望的另一个接口，使得原本接口不兼容的类可以相互合...
策略模式-定义一个算法族
策略模式定义了一系列算法族，并封装在类中，它们之间可以互相替换，此模式让算法的变化独...
设计模式之高质量代码
设计模式讲的是如何编写可扩展、可维护、可读的高质量代码，它是针对软件开发中经常遇到的...
模板方法模式-封装一套算法流程
模板方法模式在一个方法中定义一个算法的骨架，而将一些步骤延迟到子类中，使得子类可以在...
迭代器模式-统一集合的遍历方式
迭代器模式提供了一种方法，用于遍历集合对象中的元素，而又不暴露其内部的细节。
单例模式的五种实现方式及优缺点
单例模式（Singleton Design Pattern）保证一个类只能有一个实例，并提供一个全局访问点。
组合模式-统一的处理个别对象与组合对象
组合模式可以将对象组合成树形结构来表示“整体-部分”的层次结构，使得客户可以用一致的方...
装饰者模式-动态的包装原有对象的行为
装饰者模式能够更灵活的，动态的给对象添加其它功能，而不需要修改任何现有的底层代码。
观察者模式-将消息通知给观察者
观察者模式（Observer Design Pattern）定义了对象之间的一对多依赖，当对象状态改变的时候...
代理模式-访问对象的代理而非其本身
代理模式为对象提供一个代理，来控制对该对象的访问。代理模式在不改变原始类代码的情况下...
算法设计模式多媒体技术正则表达式ElasticsearchFlinkHadoopIDE
受约束摘*day25Java常用类库置信lamda留存持续录入年后正则表达式303.17regularexpre匹多模适20130322基础理论pathmunge涵义reectok需要转义的特资源分享validationex简明魔法里弄形如源码实现完备actionscript