转载

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教程 Maven教程 hibernate教程 Docker 教程 memcached教程 Quartz指南 Hive教程 Ant教程 ANTLR教程 SpringCloud java实例教程 springcloud-demo XStream教程 Hazelcast教程 Elastic-Job-Lite 深入浅出MyBatis SVN教程 ibaties教程 rabittmq教程 solr教程 Hadoop教程 WebService CXF学习 JPA教程 ActiveMQ中文指南 java-demo Java内存模型 dubbo教程 python3-demo Linux入门视频教程
近期评论
  1. 1 Spring Cloud Consul实现选举机制
  2. 2 Spring Boot集成ShedLock实现分布式定时任务
  3. 3 利用oss进行数据库和网站图片备份
  4. 4 Spring Cloud Stream实现数据流处理
  5. 5 Python3访问MySQL数据库快速入门Demo
  6. 6 Github开源项目作者可以免费申请 JetBrains 全家桶
  7. 7 Spring Cloud Vault快速入门Demo
  8. 8 Spring Cloud Gateway快速入门Demo
  9. 9 Spring Cloud Contract快速入门Demo
  10. 10 Spring Cloud Consul快速入门Demo
网站信息
  • 文章总数：82,714 篇
  • 文件总数：284,287 个
  • 标签总数：2,393 个
  • 分类总数：85 个
  • 留言数量：2,560 条
  • 在线人数：687 人
  • 运行天数：4,409天
  • 最后更新：2024年11月22日03点
Loading...

其他链接

关于本站

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

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

问题交流

[HBLOG]公众号 HBLOG HBLOG