转载

oracle本地分区索引跨分区对成本的影响

分区索引分为本地索引和全局索引,但对于在分区表上建索引,一般用的比较多的还是普通索引和本地分区索引,而全局分区索引相对用的比较少.
以下测试为验证:分区表上的本地分区索引因为查询条件引起跨分区,是否改为普通索引更合适.

以下测试:
oracle version:11.2.0.4

建测试表:
drop table SCOTT.TB_TEST01;
create table SCOTT.TB_TEST01
partition by range (CREATED)
(
  partition P_2015 values less than (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
  partition P_2016 values less than (TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
  partition P_MAX values less than (MAXVALUE)
)
as
select * from dba_objects;
##插入测试数据
insert into SCOTT.TB_TEST01
select * from SCOTT.TB_TEST01;
commit;
insert into SCOTT.TB_TEST01
select * from SCOTT.TB_TEST01;
commit;
insert into SCOTT.TB_TEST01
select * from SCOTT.TB_TEST01;
commit;
insert into SCOTT.TB_TEST01
select * from SCOTT.TB_TEST01;
commit;

#修改测试数据使其均匀分布
alter table SCOTT.TB_TEST01 enable row movement;

update SCOTT.TB_TEST01
set created=to_date('20150101','yyyymmdd')+dbms_random.value(1,1000);
commit;

#收集一下统计信息
begin
  dbms_stats.gather_table_stats(ownname=>'scott',tabname => 'TB_TEST01', cascade=> TRUE );
end;

情况1:过滤条件在全分区
查询语句:
select * from  SCOTT.TB_TEST01 where owner='SCOTT' and object_type='TABLE';

#建普通索引
create index scott.idx_TB_TEST01 on SCOTT.TB_TEST01(owner,object_type);
#执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 2208186213

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

| Id  | Operation                          | Name          | Rows  | Bytes | Cos
t (%CPU)| Time     | Pstart| Pstop |

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

|   0 | SELECT STATEMENT                   |               |   176 | 36432 |
19   (0)| 00:00:01 |       |       |

|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| TB_TEST01     |   176 | 36432 |
19   (0)| 00:00:01 | ROWID | ROWID |

|*  2 |   INDEX RANGE SCAN                 | IDX_TB_TEST01 |   176 |       |
 3   (0)| 00:00:01 |       |       |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='SCOTT' AND "OBJECT_TYPE"='TABLE')


#统计信息
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        141  consistent gets
          0  physical reads
          0  redo size
      18793  bytes sent via SQL*Net to client
        644  bytes received via SQL*Net from client
         13  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        176  rows processed
#上面的测试cost=19,consistent gets=141

#建本地分区索引
drop index scott.idx_TB_TEST01;
create index scott.idx_TB_TEST01 on SCOTT.TB_TEST01(owner,object_type) local;


SQL> /
#执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 2644430318

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

| Id  | Operation                          | Name          | Rows  | Bytes | Cos
t (%CPU)| Time     | Pstart| Pstop |

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

|   0 | SELECT STATEMENT                   |               |   176 | 36432 |
23   (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGE ALL               |               |   176 | 36432 |
23   (0)| 00:00:01 |     1 |     3 |

|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TB_TEST01     |   176 | 36432 |
23   (0)| 00:00:01 |     1 |     3 |

|*  3 |    INDEX RANGE SCAN                | IDX_TB_TEST01 |   176 |       |
 7   (0)| 00:00:01 |     1 |     3 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("OWNER"='SCOTT' AND "OBJECT_TYPE"='TABLE')


#统计信息
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        147  consistent gets
          0  physical reads
          0  redo size
      11678  bytes sent via SQL*Net to client
        644  bytes received via SQL*Net from client
         13  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        176  rows processed

#上面的测试cost=23,consistent gets=147
小结:
1.从上面测试可以看出,本地分区索引成本大于普通索引.因为此时用本地分区索引是需要PARTITION RANGE ALL的.
2.查询语句中的两个过滤条件都是等值的,在建这种复合索引时,索引列的顺序跟成本没有关系.这里没有测试，有兴趣可以测试。


情况2:过滤条件在单分区
查询语句:
select * from  SCOTT.TB_TEST01
where owner='SCOTT' and object_type='TABLE' and created between to_date('20161230','yyyymmdd') and to_date('20160105','yyyymmdd');

#建一个差的索引
drop index scott.idx_TB_TEST01;
create index scott.idx_TB_TEST01 on SCOTT.TB_TEST01(created,owner,object_type);

SQL> select * from  SCOTT.TB_TEST01
where owner='SCOTT' and object_type='TABLE' and created between to_date('20161230','yyyymmdd') and to_date('20160105','yyyymmdd');

#执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 783220382

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

| Id  | Operation                          | Name          | Rows  | Bytes | Cos
t (%CPU)| Time     | Pstart| Pstop |

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

|   0 | SELECT STATEMENT                   |               |     1 |   207 |   2
34   (0)| 00:00:03 |       |       |

|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| TB_TEST01     |     1 |   207 |   2
34   (0)| 00:00:03 |     2 |     2 |

|*  2 |   INDEX RANGE SCAN                 | IDX_TB_TEST01 |     1 |       |   2
33   (0)| 00:00:03 |       |       |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CREATED">=TO_DATE(' 2016-06-11 00:00:00', 'syyyy-mm-dd hh24:mi:ss
') AND "OWNER"='SCOTT' AND

              "OBJECT_TYPE"='TABLE' AND "CREATED"<=TO_DATE(' 2016-07-11 00:00:00
', 'syyyy-mm-dd hh24:mi:ss'))

       filter("OWNER"='SCOTT' AND "OBJECT_TYPE"='TABLE')



#统计信息
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        226  consistent gets
          0  physical reads
          0  redo size
       1932  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

#上面的测试cost=234,consistent gets=226

#建普通索引
drop index scott.idx_TB_TEST01;
create index scott.idx_TB_TEST01 on SCOTT.TB_TEST01(owner,object_type,created);

SQL> /

#执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 783220382

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

| Id  | Operation                          | Name          | Rows  | Bytes | Cos
t (%CPU)| Time     | Pstart| Pstop |

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

|   0 | SELECT STATEMENT                   |               |     4 |   828 |
14   (0)| 00:00:01 |       |       |

|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| TB_TEST01     |     4 |   828 |
14   (0)| 00:00:01 |     2 |     2 |

|*  2 |   INDEX RANGE SCAN                 | IDX_TB_TEST01 |     4 |       |
 3   (0)| 00:00:01 |       |       |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='SCOTT' AND "OBJECT_TYPE"='TABLE' AND "CREATED">=TO_DATE('
 2016-06-11 00:00:00',

              'syyyy-mm-dd hh24:mi:ss') AND "CREATED"<=TO_DATE(' 2016-07-11 00:0
0:00', 'syyyy-mm-dd hh24:mi:ss'))



#统计信息
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
       1932  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed
#上面的测试cost=14,consistent gets=9

#建本地分区索引
drop index scott.idx_TB_TEST01;
create index scott.idx_TB_TEST01 on SCOTT.TB_TEST01(owner,object_type,created) local ;


SQL> /

#执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 2387350646

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

| Id  | Operation                          | Name          | Rows  | Bytes | Cos
t (%CPU)| Time     | Pstart| Pstop |

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

|   0 | SELECT STATEMENT                   |               |     4 |   828 |
11   (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGE SINGLE            |               |     4 |   828 |
11   (0)| 00:00:01 |     2 |     2 |

|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TB_TEST01     |     4 |   828 |
11   (0)| 00:00:01 |     2 |     2 |

|*  3 |    INDEX RANGE SCAN                | IDX_TB_TEST01 |     4 |       |
 7   (0)| 00:00:01 |     2 |     2 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("OWNER"='SCOTT' AND "OBJECT_TYPE"='TABLE' AND "CREATED">=TO_DATE('
 2016-06-11 00:00:00',

              'syyyy-mm-dd hh24:mi:ss') AND "CREATED"<=TO_DATE(' 2016-07-11 00:0
0:00', 'syyyy-mm-dd hh24:mi:ss'))



#统计信息
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1872  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

#上面的测试cost=11,consistent gets=8

小结:
1.建的第一个差索引,之所以差,是因为created字段是一个区间值,此时access只能对应created字段,而owner字段和object_type都是通过filter过滤的.所以过滤条件是区间和要放后面.
2.从上面测试可以看出,本地分区索引成本小于普通索引.此时本地分区索引使用PARTITION RANGE SINGLE.


情况3:过滤条件在跨分区
查询语句:
select * from  SCOTT.TB_TEST01
where owner='SCOTT' and object_type='TABLE' and created between to_date('20161220','yyyymmdd') and to_date('20170205','yyyymmdd');

#建普通索引
drop index scott.idx_TB_TEST01;
create index scott.idx_TB_TEST01 on SCOTT.TB_TEST01(owner,object_type,created);

SQL> select * from  SCOTT.TB_TEST01
where owner='SCOTT' and object_type='TABLE' and created between to_date('20161220','yyyymmdd') and to_date('20170205','yyyymmdd');

#执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 2208186213

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

| Id  | Operation                          | Name          | Rows  | Bytes | Cos
t (%CPU)| Time     | Pstart| Pstop |

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

|   0 | SELECT STATEMENT                   |               |     1 |    98 |
 5   (0)| 00:00:01 |       |       |

|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| TB_TEST01     |     1 |    98 |
 5   (0)| 00:00:01 | ROWID | ROWID |

|*  2 |   INDEX RANGE SCAN                 | IDX_TB_TEST01 |     1 |       |
 3   (0)| 00:00:01 |       |       |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='SCOTT' AND "OBJECT_TYPE"='TABLE' AND "CREATED">=TO_DATE('
 2016-12-20 00:00:00',

              'syyyy-mm-dd hh24:mi:ss') AND "CREATED"<=TO_DATE(' 2017-02-05 00:0
0:00', 'syyyy-mm-dd hh24:mi:ss'))


#统计信息
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
       1836  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

#上面的测试cost=5,consistent gets=7          
          
#建本地分区索引
drop index scott.idx_TB_TEST01;
create index scott.idx_TB_TEST01 on SCOTT.TB_TEST01(owner,object_type,created) local ;

SQL> /

#执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 353906606

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

| Id  | Operation                          | Name          | Rows  | Bytes | Cos
t (%CPU)| Time     | Pstart| Pstop |

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

|   0 | SELECT STATEMENT                   |               |     1 |    98 |
 7   (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGE ITERATOR          |               |     1 |    98 |
 7   (0)| 00:00:01 |     2 |     3 |

|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TB_TEST01     |     1 |    98 |
 7   (0)| 00:00:01 |     2 |     3 |

|*  3 |    INDEX RANGE SCAN                | IDX_TB_TEST01 |     1 |       |
 5   (0)| 00:00:01 |     2 |     3 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("OWNER"='SCOTT' AND "OBJECT_TYPE"='TABLE' AND "CREATED">=TO_DATE('
 2016-12-20 00:00:00',

              'syyyy-mm-dd hh24:mi:ss') AND "CREATED"<=TO_DATE(' 2017-02-05 00:0
0:00', 'syyyy-mm-dd hh24:mi:ss'))


#统计信息
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
       1803  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

#上面的测试cost=7,consistent gets=10

小结:
1.从上面测试可以看出,本地分区索引成本大于普通索引.因为此时用本地分区索引是需要PARTITION RANGE ITERATOR的.

总结:
1.在选择普通索引和本地分区索引时,分区的跨度影响索引的选择,但相对于其它因素,这个影响还是很小的.如果考虑到将来分区归档的维护,本地分区索引还是有优势的.最重要的是索引列的选择,索引列顺序的选择才是最重要的.
2.查询语句中的两个过滤条件都是等值的,在建这种复合索引时,索引列的顺序跟成本没有关系.这里没有测试，有兴趣可以测试。
3.查询语句中的过滤条件是区间的,要放在等值的后面.

正文到此结束
所属分类： 编程技术

热门推荐

相关文章

阿里云首购8折
说给你听

本文目录
随机标签
书籍教程
springboot-demo Java入门教程 bootstrap3 CSS Apache基础教程 php ionic 教程 Python mysql教程 eclipse Ubuntu VPS系统配置 AngularJS 教程 MongoDB教程 Struts2教程 Redis教程 Spring教程 Git教程 Jenkins进阶系列 openfire参考指南 Java设计模式 HBase教程 java-demo Maven教程 springcloud-demo hibernate教程 Docker 教程 memcached教程 Quartz指南 Hive教程 ANTLR教程 SpringCloud Ant教程 java实例教程 Hazelcast教程 XStream教程 Elastic-Job-Lite 深入浅出MyBatis ibaties教程 SVN教程 rabittmq教程 Hadoop教程 solr教程 WebService CXF学习 JPA教程 ActiveMQ中文指南 Java内存模型 dubbo教程 python3-demo Linux入门视频教程
近期评论
  1. 1 使用 Docker 打包和运行 Vue 应用
  2. 2 Dependency Check命令行方式扫描jar包的安全漏洞
  3. 3 maven使用Dependency-Check来扫描安全漏洞
  4. 4 使用sonarqube分析代码质量
  5. 5 使用screw来对比数据库表和字段差异
  6. 6 Spring Boot 实现防盗链功能
  7. 7 在Spring Boot中的实现国际化（i18n）
  8. 8 一个用于 SMB/CIFS 网络的 Java 库-JCIFS
  9. 9 Spring Boot 3.x嵌入MongoDB 进行测试
  10. 10 java如何解析和生成yaml文件？
网站信息
  • 文章总数：82,737 篇
  • 文件总数：284,312 个
  • 标签总数：2,416 个
  • 分类总数：85 个
  • 留言数量：2,559 条
  • 在线人数：608 人
  • 运行天数：4,439天
  • 最后更新：2024年12月22日13点
Loading...

其他链接

关于本站

本站定位：个人技术类博客

本站作用：写博客、记日志、闲聊扯淡鼓捣技术。

问题交流

[HBLOG]公众号 HBLOG HBLOG