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

-- 范围分区示例

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', 'YYYY-MM-DD')),

           partition p_201303 values less than (TO_DATE('2013-04-01', 'YYYY-MM-DD')),

           partition p_201304 values less than (TO_DATE('2013-05-01', 'YYYY-MM-DD')),

           partition p_201305 values less than (TO_DATE('2013-06-01', 'YYYY-MM-DD')),

           partition p_201306 values less than (TO_DATE('2013-07-01', 'YYYY-MM-DD')),

           partition p_201307 values less than (TO_DATE('2013-08-01', 'YYYY-MM-DD')),

           partition p_201308 values less than (TO_DATE('2013-09-01', 'YYYY-MM-DD')),

           partition p_201309 values less than (TO_DATE('2013-10-01', 'YYYY-MM-DD')),

           partition p_201310 values less than (TO_DATE('2013-11-01', 'YYYY-MM-DD')),

           partition p_201311 values less than (TO_DATE('2013-12-01', 'YYYY-MM-DD')),

           partition p_201312 values less than (TO_DATE('2014-01-01', 'YYYY-MM-DD')),

           partition p_201401 values less than (TO_DATE('2014-02-01', 'YYYY-MM-DD')),

           partition p_201402 values less than (TO_DATE('2014-03-01', 'YYYY-MM-DD')),

           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,deal_date,area_code,nbr,contents)

      select rownum,

             to_date( to_char(sysdate,'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;

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')

   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 |     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')

   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 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')

   and deal_date < TO_DATE('2013-06-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 hh34:mi:ss')

   and deal_date <= TO_DATE('2013-06-01 00:00:00', 'YYYY-MM-DD hh34: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 hh34:mi:ss')

   and deal_date < TO_DATE('2013-06-01 00:00:00', 'YYYY-MM-DD hh34: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