oracle不适合建立索引的场景,Oracle 分区表中本地索引和全局索引的适用场景

背景

分区表创建好了之后，如果需要最大化分区表的性能就需要结合索引的使用，分区表有两种索引：本地索引和全局索引。既然存在着两种的索引类型，相信存在即合理。既然存在就会有存在的原因，也就是在特定的场景中就更能发挥出索引的性能的；

本文档通过测试，总结出两种索引的适合的场景；

测试环境

数据库版本：11.2.0.3

分区表的创建脚本：

CREATE TABLE SCOTT.PTB

(

GG1DM VARCHAR2(9 BYTE),

SL NUMBER(18,4) ,

DJBH VARCHAR2(20 BYTE)

)

NOCOMPRESS

PARTITION BY LIST (GG1DM)

(

PARTITION PTABLE_P1 VALUES ('07')，

PARTITION PTABLE_P2 VALUES ('08')，

PARTITION PTABLE_P3 VALUES ('09')

)

然后插入大量的数据，再进行统计信息的更新；

select t3.table_name,

t3.partition_name,

t3.high_value,

t3.num_rows,

t3.blocks,

t3.empty_blocks,

t3.last_analyzed

from dba_tab_partitions t3

where t3.table_name='PTABLE'

order by t3.num_rows desc;

开始测试

测试一、跨分区的数据查询

1.1 创建本地索引(注意：该列不是分区的列)

SQL> CREATE INDEX SCOTT.IN_PTB ON SCOTT.PTB

(DJBH)

LOGGING

LOCAL (

PARTITION PTABLE_P1

LOGGING

NOCOMPRESS ,

PARTITION PTABLE_P2

LOGGING

NOCOMPRESS ,

PARTITION PTABLE_P3

LOGGING

NOCOMPRESS

)

SQL> select Segment_NAME,PARTITION_NAME,SEGMENT_TYPE from dba_segments a where a.segment_name='IN_PTB';

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE

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

IN_PTB PTABLE_P1 INDEX PARTITION

IN_PTB PTABLE_P2 INDEX PARTITION

IN_PTB PTABLE_P3 INDEX PARTITION

LOCAL索引会在每个分区上面单独创建INDEX PARTITION，类似于三个子索引；

进行执行计划的查看

SQL> select count(1) from scott.ptb where djbh='R23NAA002138250';

COUNT(1)

----------

512

1.2 创建全局索引，原先的索引先drop(注意：该列不是分区的列)

SQL> CREATE INDEX SCOTT.IN_PTB_L ON SCOTT.PTB

(DJBH)

NOLOGGING

STORAGE (

BUFFER_POOL DEFAULT

FLASH_CACHE DEFAULT

CELL_FLASH_CACHE DEFAULT

)

NOPARALLEL;

SQL> select Segment_NAME,PARTITION_NAME,SEGMENT_TYPE from dba_segments a where a.segment_name='IN_PTB_L';

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE

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

IN_PTB_L INDEX

进行执行计划的查看

需要先刷新buffer：

alter system flush buffer_cache;

select count(1) from scott.ptb where djbh='R23NAA002138250';

测试一总结：

以上那种情况因为djbh这一列是需要跨分区的，当查询的条件是需要跨分区查询内容的时候，LOCAL INDEX的效率比GLOBAL INDEX的效率要低，通过consistent gets和db block gets的对比可以看出来；

测试二、分区内部的查询

2.1 分区内使用本地索引

alter system flush buffer_cache;

select count(1) from scott.ptb where djbh='R23NAA002138250' and GG1DM='07'; #

1

2

该条件可以确定在单个分区里面

2.2 分区内使用全局索引

alter system flush buffer_cache;

select /*+ index(PTB IN_PTB_L) */ count(1) from scott.ptb where djbh='R23NAA002138250' and GG1DM='07';

1

2

测试二总结：

通过这组实验可以看出来如果查询的条件是在单个分区里面查询的时候，那么LOCAL INDEX的效率比GLOBAL INDEX的效率要高。

总结

经过以上的测试可以发现全局索引和本地索引的使用效率跟查询条件有直接的影响，创建索引的时候需要根据业务的使用场景进行创建；

而分区表的创建也是受使用场景所影响的，所以在创建分区表和分区索引的时候都需要事先了解业务的需求，尽量把业务需要统计的信息放在一个同一个分区。这样使分区表的性能实现最大化；

Atitit.分区对索引的影响 分区索引和全局索引 attilax总结

Atitit.分区对索引的影响 分区索引和全局索引 attilax总结 1. 分区的好处1 2. 分区键:2 3. 分区的建议:2 4. 分区索引和全局索引:2 5. 全局索引就是在全表上创建索引, ...

Oracle 分区表中索引失效

当对分区表进行 一些操作时,会造成索引失效. 当有truncate/drop/exchange 操作分区  时全局索引 会失效. exchange 的临时表没有索引,或者有索引,没有用includin ...

Oracle分区表删除分区引发错误ORA-01502: 索引或这类索引的分区处于不可用状态

(一)问题: 最近在做Oracle数据清理,在对分区表进行数据清理时,采用的方法是drop partition,删除的过程中,没有遇到任何问题,大概过了10分钟,开发人员反馈部分分区表上的业务失败.具 ...

Oracle数据库中如何选择合适的索引类型 .

索引就好象一本字典的目录.凭借字典的目录,我们可以非常迅速的找到我们所需要的条目.数据库也是如此.凭借Oracle数据库的索引,相关语句可以迅速的定位记录的位置,而不必去定位整个表. 虽然说,在表中是 ...

Oracle分区表删除分区数据时导致索引失效解决

https://blog.csdn.net/e_wsq/article/details/80896258

ORACLE 全局索引和本地索引

Oracle数据库中,有两种类型的分区索引,全局索引和本地索引,其中本地索引又可以分为本地前缀索引和本地非前缀索引.下面就分别看看每种类型的索引各自的特点. 全局索引以整个表的数据为对象建立索引,索引 ...

Oracle非分区索引，全局分区索引和本地分区索引。

1.如果按照索引是否分区作为划分依据,Oracle 的索引类型可以分为非分区索引,全局分区索引和本地分区索引. 2.创建演示实例 --创建非分区表create table test_partition ...

深入学习Oracle分区表及分区索引

关于分区表和分区索引(About Partitioned Tables and Indexes)对于10gR2而言,基本上可以分成几类: •       Range(范围)分区 •       Has ...

转：深入学习Oracle分区表及分区索引

转自:http://database.ctocio.com.cn/tips/286/8104286.shtml 关于分区表和分区索引(About Partitioned Tables and Inde ...

随机推荐

在Mac OS上安装Vagrant和Docker的教程

转载于:http://www.itxuexiwang.com/a/shujukujishu/redis/2016/0216/128.html?1455808640 当听到很多人在说Docker是多么多 ...

html5网页动画总结--jQuery旋转插件jqueryrotate

CSS3 提供了多种变形效果,比如矩阵变形.位移.缩放.旋转和倾斜等等,让页面更加生动活泼有趣,不再一动不动.然后 IE10 以下版本的浏览器不支持 CSS3 变形,虽然 IE 有私有属性滤镜(fil ...

【转】SQL删除重复记录,只保留其中一条

SQL:删除重复数据,只保留一条用SQL语句,删除掉重复项只保留一条在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢 1.查找表中多余的重复记录,重复记录是根据单个字段(peop ...

DropMaster

DropMaster 是4个原生 VCL 控件的集合,在 Delphi 和 C++Builder 中使用.虽然包含在 Delphi 和 C++Builder 中的 VCL 组件允许同一程序内窗口之间的 ...

Redis 实现用户积分排行榜

排行榜功能是一个很普遍的需求.使用 Redis 中有序集合的特性来实现排行榜是又好又快的选择. 一般排行榜都是有实效性的,比如“用户积分榜”.如果没有实效性一直按照总榜来排,可能榜首总是几个老用户,对 ...

MongoDB 和 mySql 的关系

1. mysql 和 MongoDb MySQL与MongoDB都是开源的常用数据库,但是MySQL是传统的关系型数据库,MongoDB则是非关系型数据库,也叫文档型数据库,是一种NoSQL的数据库. ...

CentOS6.5自带Python2.6.6升级至Python2.7

CentOS6.5中Python2.6升级到Python2.7 由于Python开发团队已不再支持2.6版本,且该版本对一些软件不支持,因此将2.6升级到2.7. 1.安装Python2.7: 下载源 ...

解决ionic2各种坑文章收集

小白最近打算用ionic2做一个APP,无奈没有大神指点,一路坎坷遇到数不清的坑(主要是墙的问题).这里整理一些大神的帖子链接,用以指路. 新建/打包: Ionic2+Angular2创建项目打包An ...

Centos上把新安装的程序添加到系统环境变量的两种方法

1.软链接 通过命令查看当前系统的环境变量信息,然后软连接形式把程序的地址连接到已经在环境变量中的目录中 echo "$PATH" > /root/tmp 结果如下: /us ...