索引优化系列十三--分区表各类聚合优化玄机
|
-- 范围分区示例 drop table range_part_tab purge; --注意，此分区为范围分区 --例子1 create table range_part_tab (id number,deal_date date,area_code number,nbr number,contents varchar2(4000)) partition by range (deal_date) ( partition p_201301 values less than (TO_DATE('2013-02-01','YYYY-MM-DD')), partition p_201302 values less than (TO_DATE('2013-03-01', partition p_201303 values less than (TO_DATE('2013-04-01', partition p_201304 values less than (TO_DATE('2013-05-01', partition p_201305 values less than (TO_DATE('2013-06-01', partition p_201306 values less than (TO_DATE('2013-07-01', partition p_201307 values less than (TO_DATE('2013-08-01', partition p_201308 values less than (TO_DATE('2013-09-01', partition p_201309 values less than (TO_DATE('2013-10-01', partition p_201310 values less than (TO_DATE('2013-11-01', partition p_201311 values less than (TO_DATE('2013-12-01', partition p_201312 values less than (TO_DATE('2014-01-01', partition p_201401 values less than (TO_DATE('2014-02-01', partition p_201402 values less than (TO_DATE('2014-03-01', partition p_max values less than (maxvalue) ) ; alter table RANGE_PART_TAB modify nbr not null; --以下是插入2013年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下： insert into range_part_tab (id,deal_date,area_code,nbr,contents) select rownum, to_date( to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'), ceil(dbms_random.value(591,599)), ceil(dbms_random.value(18900000001,18999999999)), rpad('*',400,'*') from dual connect by rownum <= 100000; commit; --以下是插入2014年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下： insert into range_part_tab (id, to_date( to_char(sysdate,'*') from dual connect by rownum <= 100000; commit; create index idx_part_id on range_part_tab (id) ; create index idx_part_nbr on range_part_tab (nbr) local; --统计信息系统一般会自动收集，这只是首次建成表后需要操作一下，以方便测试 exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'RANGE_PART_TAB',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ; set autotrace on set linesize 1000 select max(nbr) max_nbr from range_part_tab partition(p_201305); 执行计划 ------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 8 | 2 (0)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 8 | | | | | | 2 | PARTITION RANGE SINGLE | | 1 | 8 | 2 (0)| 00:00:01 | 5 | 5 | | 3 | INDEX FULL SCAN (MIN/MAX)| IDX_PART_NBR | 1 | 8 | 2 (0)| 00:00:01 | 5 | 5 | ------------------------------------------------------------------------------------------------------------ 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets select max(nbr) max_nbr from range_part_tab where deal_date >= TO_DATE('2013-05-01','YYYY-MM-DD') and deal_date < TO_DATE('2013-06-01','YYYY-MM-DD'); 执行计划 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 17 | 170 (0)| 00:00:03 | | | | 1 | SORT AGGREGATE | | 1 | 17 | | | | | | 2 | PARTITION RANGE SINGLE| | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 | | 3 | TABLE ACCESS FULL | RANGE_PART_TAB | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 | ---------------------------------------------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 568 consistent gets select count(*) max_nbr from range_part_tab partition(p_201305); 执行计划 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 8 (0)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | | | | | | 2 | PARTITION RANGE SINGLE| | 8716 | 8 (0)| 00:00:01 | 5 | 5 | | 3 | INDEX FAST FULL SCAN | IDX_PART_NBR | 8716 | 8 (0)| 00:00:01 | 5 | 5 | ------------------------------------------------------------------------------------------------ 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 29 consistent gets select count(*) max_nbr from range_part_tab where deal_date >= TO_DATE('2013-05-01','YYYY-MM-DD'); 执行计划 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 9 | 170 (0)| 00:00:03 | | | | 1 | SORT AGGREGATE | | 1 | 9 | | | | | | 2 | PARTITION RANGE SINGLE| | 22 | 198 | 170 (0)| 00:00:03 | 5 | 5 | | 3 | TABLE ACCESS FULL | RANGE_PART_TAB | 22 | 198 | 170 (0)| 00:00:03 | 5 | 5 | ---------------------------------------------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 568 consistent gets
select sum(nbr) max_nbr from range_part_tab partition(p_201305); 执行计划 -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 8 (0)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 8 | | | | | | 2 | PARTITION RANGE SINGLE| | 8716 | 69728 | 8 (0)| 00:00:01 | 5 | 5 | | 3 | INDEX FAST FULL SCAN | IDX_PART_NBR | 8716 | 69728 | 8 (0)| 00:00:01 | 5 | 5 | -------------------------------------------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 29 consistent gets
select sum(nbr) max_nbr from range_part_tab where deal_date >= TO_DATE('2013-05-01','YYYY-MM-DD'); 执行计划 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 17 | 170 (0)| 00:00:03 | | | | 1 | SORT AGGREGATE | | 1 | 17 | | | | | | 2 | PARTITION RANGE SINGLE| | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 | | 3 | TABLE ACCESS FULL | RANGE_PART_TAB | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 | ---------------------------------------------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 568 consistent gets
select distinct(nbr) from range_part_tab partition(p_201305); 执行计划 -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8660 | 69280 | 9 (12)| 00:00:01 | | | | 1 | HASH UNIQUE | | 8660 | 69280 | 9 (12)| 00:00:01 | | | | 2 | PARTITION RANGE SINGLE| | 8716 | 69728 | 8 (0)| 00:00:01 | 5 | 5 | | 3 | INDEX FAST FULL SCAN | IDX_PART_NBR | 8716 | 69728 | 8 (0)| 00:00:01 | 5 | 5 | -------------------------------------------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 29 consistent gets 0 physical reads 0 redo size 152890 bytes sent via SQL*Net to client 6741 bytes received via SQL*Net from client 577 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 8635 rows processed
select distinct(nbr) from range_part_tab where deal_date >= TO_DATE('2013-05-01','YYYY-MM-DD'); 执行计划 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 22 | 374 | 171 (1)| 00:00:03 | | | | 1 | HASH UNIQUE | | 22 | 374 | 171 (1)| 00:00:03 | | | | 2 | PARTITION RANGE SINGLE| | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 | | 3 | TABLE ACCESS FULL | RANGE_PART_TAB | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 | ---------------------------------------------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 568 consistent gets 0 physical reads 0 redo size 152886 bytes sent via SQL*Net to client 6741 bytes received via SQL*Net from client 577 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 8635 rows processed
select count(*) from range_part_tab where deal_date >= TO_DATE('2013-05-01 00:00:00','YYYY-MM-DD hh24:mi:ss') and deal_date <= TO_DATE('2013-06-01 00:00:00','YYYY-MM-DD hh24:mi:ss'); COUNT(*) ---------- 8635 执行计划 ------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 9 | 340 (1)| 00:00:05 | | | | 1 | SORT AGGREGATE | | 1 | 9 | | | | | | 2 | PARTITION RANGE ITERATOR| | 497 | 4473 | 340 (1)| 00:00:05 | 5 | 6 | |* 3 | TABLE ACCESS FULL | RANGE_PART_TAB | 497 | 4473 | 340 (1)| 00:00:05 | 5 | 6 | ------------------------------------------------------------------------------------------------------------ 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 1136 consistent gets
select count(*) from range_part_tab where deal_date >= TO_DATE('2013-05-01 00:00:00','YYYY-MM-DD hh24:mi:ss') and deal_date < TO_DATE('2013-06-01 00:00:00','YYYY-MM-DD hh24:mi:ss'); COUNT(*) ---------- 8635 执行计划 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 9 | 170 (0)| 00:00:03 | | | | 1 | SORT AGGREGATE | | 1 | 9 | | | | | | 2 | PARTITION RANGE SINGLE| | 22 | 198 | 170 (0)| 00:00:03 | 5 | 5 | | 3 | TABLE ACCESS FULL | RANGE_PART_TAB | 22 | 198 | 170 (0)| 00:00:03 | 5 | 5 | ---------------------------------------------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 568 consistent gets （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |