首页 > 资讯 > 数据库 >索引优化系列十三--分区表各类聚合优化玄机

712

分享到

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

2024-04-02 19:04:59 712人浏览独家记忆

摘要

-- 范围分区示例drop table range_part_tab purge;--注意，此分区为范围分区--例子1create table range_part_tab (id number,

-- 范围分区示例

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

执行计划

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

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

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

执行计划

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

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

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

执行计划

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

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

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

执行计划

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

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

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

执行计划

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

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

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

执行计划

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

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

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

执行计划

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

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

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

执行计划

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

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

| 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

执行计划

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

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

| 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

执行计划

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

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

| 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

您可能感兴趣的文档:

--结束END--

本文标题: 索引优化系列十三--分区表各类聚合优化玄机

本文链接: https://lsjlt.com/news/43306.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

回答

如何调试操作系统的错误？
操作系统

2023-11-15发布

回答

操作系统中的I/O系统是如何实现的？
操作系统

2023-11-15发布

回答

如何实现操作系统的内存管理？
操作系统

2023-11-15发布

回答

什么是虚拟内存，它对操作系统有什么影响？
操作系统

2023-11-15发布

回答

ASP中的MVC架构和WebForms架构有什么区别和使用场景？
ASP.NET

2023-11-15发布

回答

ASP中的数据验证和数据校验有什么不同？
ASP.NET

2023-11-15发布

回答

ASP中的ADO对象和DAO对象有什么区别和使用方法？
ASP.NET

2023-11-15发布

回答

Node.js中的包管理器NPM是什么？如何使用它进行依赖管理？
node.js

2023-11-15发布

回答

Vue.js中的动态组件是什么？如何使用它来动态渲染组件？
VUE

2023-11-15发布

回答

如何使用Vue.js实现懒加载和预加载？
VUE

2023-11-15发布

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

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

索引优化系列三聚合因子

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

索引优化系列十五--组合查询和in有关的优化

索引优化系列二回表

索引系列十--索引特性之有序优化order by

索引系列十一--索引特性之有序与存列值优化max

关于SQL建表语句使用详解

HBase在大数据审计与合规性追踪中的应用

MySQL与HBase在大数据金融分析中的性能与可扩展性对比

HBase的Region Server之间的网络通信优化

HBase在大数据监控与告警系统中的实时数据处理能力

MySQL与HBase在大数据安全策略中的实现与对比

HBase的分布式事务处理在复杂业务场景中的应用

MySQL与HBase在混合存储架构中的整合策略

HBase如何支持高效的二级索引查询

MySQL与HBase在物联网数据收集与处理中的协作模式