我的编程空间，编程开发者的网络收藏夹
学习永远不晚
位置：首页-资讯-数据库

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

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号
获取短信验证
立即预约

请填写图片验证码后获取短信验证码

看不清楚，换张图片

免费获取短信验证码

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

-- 范围分区示例

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   

   




   








免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

阅读原文内容投诉

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

下载Word文档到电脑，方便收藏和打印～

下载Word文档

相关文章

猜你喜欢

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

2024-04-02

索引优化系列三 聚合因子

2024-04-02

索引优化系列十四--啥时分区索引性能反而低

2024-04-02

热门标签

Linux(148)PHP(127)Java(102)正则表达式(101)JavaScript(69)最佳实践(67)jQuery(44)MySQL(39)Docker(37)C语言(36)性能优化(34)Python(34)XML(28)string(27)第三方库(23)回调函数(23)ZIP(22)数组(22)可扩展性(22)字符串比较(21)find(20)RPM(20)Go(20)grep(19)ASP.NETCore(19)XML解析器(19)事件(19)事件处理程序(19)StringBuilder(18)Nginx(18)

编程热搜

查看更多

编程资源站

  • 资料下载
  • 历年试题
2021年下半年软考高级信息系统项目管理师高频考点精选资料
2021下半年软考高级信息系统技术知识点记忆口诀精选资料
2021下半年软考《信息系统项目管理师》考试真题及答案精选资料
2021下半年软考高级考试备考攻略精选资料
2021年软考高级《信息系统项目管理师》巩固练习题汇总精选资料
2021下半年软考高级信息系统项目管理师30个易考知识点汇总精选资料
2021下半年软考高级知识点这样记，还担心记不住吗精选资料
2021年下半年软考高级考试重点汇总精选资料
2021下半年软考高级信息系统项目管理师计算公式汇总精选资料
2021年下半年软考高级《信息系统项目管理师》模拟试题精选资料
查看更多
信息系统项目管理师选择题每日一练（2024）历年试题
2023年下半年信息系统项目管理师综合知识真题演练历年试题
查看更多

目录

本网页已闲置超过3分钟，按键盘任意键或点击空白处，即可回到网页
最新资讯更多
    友情链接编程网

    反馈

    我要
    反馈