溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

索引優(yōu)化系列十三--分區(qū)表各類聚合優(yōu)化玄機

發(fā)布時間：2020-07-23 18:59:04 來源：網(wǎng)絡(luò) 閱讀：463 作者：1415699306 欄目：關(guān)系型數(shù)據(jù)庫

-- 范圍分區(qū)示例

drop table range_part_tab purge;

--注意，此分區(qū)為范圍分區(qū)


--例子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年一整年日期隨機數(shù)和表示福建地區(qū)號含義（591到599）的隨機數(shù)記錄，共有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年一整年日期隨機數(shù)和表示福建地區(qū)號含義（591到599）的隨機數(shù)記錄，共有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;


--統(tǒng)計信息系統(tǒng)一般會自動收集，這只是首次建成表后需要操作一下，以方便測試

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

執(zhí)行計劃

------------------------------------------------------------------------------------------------------------

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

------------------------------------------------------------------------------------------------------------

統(tǒng)計信息

----------------------------------------------------------

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

執(zhí)行計劃

----------------------------------------------------------------------------------------------------------

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

----------------------------------------------------------------------------------------------------------

統(tǒng)計信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

        568  consistent gets









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

執(zhí)行計劃

------------------------------------------------------------------------------------------------

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

------------------------------------------------------------------------------------------------

統(tǒng)計信息

----------------------------------------------------------

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

執(zhí)行計劃

----------------------------------------------------------------------------------------------------------

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

----------------------------------------------------------------------------------------------------------

統(tǒng)計信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

        568  consistent gets

        

        

           


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

執(zhí)行計劃

--------------------------------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------------------------------

統(tǒng)計信息

----------------------------------------------------------

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

執(zhí)行計劃

----------------------------------------------------------------------------------------------------------

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

----------------------------------------------------------------------------------------------------------

統(tǒng)計信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

        568  consistent gets   

   



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

執(zhí)行計劃

--------------------------------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------------------------------

統(tǒng)計信息

----------------------------------------------------------

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

執(zhí)行計劃

----------------------------------------------------------------------------------------------------------

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

----------------------------------------------------------------------------------------------------------

統(tǒng)計信息

----------------------------------------------------------

          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

執(zhí)行計劃

------------------------------------------------------------------------------------------------------------

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

------------------------------------------------------------------------------------------------------------

統(tǒng)計信息

----------------------------------------------------------

          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   

執(zhí)行計劃

----------------------------------------------------------------------------------------------------------

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

----------------------------------------------------------------------------------------------------------

統(tǒng)計信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

        568  consistent gets   

   




   








向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手