【Oracle】关于局部索引和全局索引的测试

前言

Oracle数据库中

（1）对于Oracle分区表，存在两种分区：局部索引和全局索引 （2）表分区和索引分区是两个不同的概念 （3）局部索引，也称本地索引（Local索引），其分区形式与表的分区完全相同，依赖列相同，存储属性也相同。

以下是实际应用中碰到的一个问题，特写此篇文章以供探讨

目前表结构如下：

-- Create table create table GDYDSJ_HISTORY_JWD ( eventid VARCHAR2(30) not null, dirvingdir VARCHAR2(10), speed NUMBER, eventjamspeed NUMBER, eventjamdist NUMBER, state VARCHAR2(10), length NUMBER, linkid VARCHAR2(50), roadname VARCHAR2(50), roadtype VARCHAR2(10), xy VARCHAR2(50), traveltime VARCHAR2(50), submittime DATE, yszs NUMBER, xys CLOB ) partition by range (SUBMITTIME) ( partition GDYDSJJWDP1 values less than (TO_DATE(' 2017-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace DLYDINFO pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ), partition GDYDSJJWDP2 values less than (TO_DATE(' 2017-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace DLYDINFO pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ), partition GDYDSJJWDP3 values less than (TO_DATE(' 2017-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace DLYDINFO pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ), partition GDYDSJJWDP4 values less than (TO_DATE(' 2017-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace DLYDINFO pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ), partition GDYDSJJWDP5 values less than (TO_DATE(' 2017-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace DLYDINFO pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ), partition GDYDSJJWDP6 values less than (TO_DATE(' 2017-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace DLYDINFO pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ), partition GDYDSJJWDP7 values less than (TO_DATE(' 2017-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace DLYDINFO pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ), partition GDYDSJJWDP8 values less than (TO_DATE(' 2017-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace DLYDINFO pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ), partition GDYDSJJWDP9 values less than (TO_DATE(' 2017-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace DLYDINFO pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ), partition GDYDSJJWDP10 values less than (TO_DATE(' 2017-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace DLYDINFO pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ), partition GDYDSJJWDP11 values less than (TO_DATE(' 2017-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace DLYDINFO pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ), partition GDYDSJJWDP12 values less than (TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace DLYDINFO pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ), partition GDYDSJJWDP13 values less than (TO_DATE(' 2018-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace DLYDINFO pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ), partition GDYDSJJWDP14 values less than (TO_DATE(' 2018-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace DLYDINFO pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ), partition GDYDSJJWDP15 values less than (TO_DATE(' 2018-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace DLYDINFO pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ), partition GDYDSJJWDP16 values less than (TO_DATE(' 2018-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace DLYDINFO pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ), partition GDYDSJJWDP17 values less than (TO_DATE(' 2018-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace DLYDINFO pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ), partition GDYDSJJWDP18 values less than (TO_DATE(' 2018-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace DLYDINFO pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ), partition GDYDSJJWDP19 values less than (TO_DATE(' 2018-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace DLYDINFO pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ), partition GDYDSJJWDP20 values less than (TO_DATE(' 2018-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace DLYDINFO pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ), partition GDYDSJJWDP21 values less than (TO_DATE(' 2018-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace DLYDINFO pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ), partition GDYDSJJWDP22 values less than (TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace DLYDINFO pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ), partition GDYDSJJWDP23 values less than (TO_DATE(' 2018-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace DLYDINFO pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ), partition GDYDSJP24 values less than (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace DLYDINFO pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ) ); -- Add comments to the columns comment on column GDYDSJ_HISTORY_JWD.eventid is '拥堵事件ID'; comment on column GDYDSJ_HISTORY_JWD.dirvingdir is '拥堵方向,为1、2时xys与绘制方向相同,为3时与绘制方向相反'; comment on column GDYDSJ_HISTORY_JWD.speed is '路段速度'; comment on column GDYDSJ_HISTORY_JWD.state is '路段拥堵状态'; comment on column GDYDSJ_HISTORY_JWD.length is '路段长度'; comment on column GDYDSJ_HISTORY_JWD.linkid is '道路link'; comment on column GDYDSJ_HISTORY_JWD.roadname is '道理名称'; comment on column GDYDSJ_HISTORY_JWD.roadtype is '// : 0,高速路(Free Way) // : 1,城市快速路(Main Street、City Speed way) // : 2,国道(National Road) // : 3,主要道路(Main road) // : 4,省道(Province Road) // : 5,次要道路(Secondary road) // : 6,普通道路(Common road) // : 7,县道(County Road) // : 8,乡公路(Rural Road) // : 9,县乡村内部道路(In County Road)'; comment on column GDYDSJ_HISTORY_JWD.xy is '路段终点坐标'; comment on column GDYDSJ_HISTORY_JWD.submittime is '入库时间，以此为准'; comment on column GDYDSJ_HISTORY_JWD.yszs is '拥堵延时指数'; comment on column GDYDSJ_HISTORY_JWD.xys is '路段坐标集合'; -- Create/Recreate indexes create index GDYDSJ_HISTORY_JWD_EVENTID on GDYDSJ_HISTORY_JWD (EVENTID) tablespace DLYDINFO pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); create index GDYDSJ_HISTORY_JWD_LINKID on GDYDSJ_HISTORY_JWD (LINKID) tablespace DLYDINFO pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); create index GDYDSJ_HISTORY_JWD_SUBMITTIME on GDYDSJ_HISTORY_JWD (SUBMITTIME) nologging local;

以下是几种查询方式的执行计划的对比：

（1）

select to_char(submittime,'yyyy/mm/dd') coltime,round(avg(yszs),2) avg_yszs,round(avg(speed),2) avg_speed from (select submittime,yszs,speed from GDYDSJ_HISTORY_JWD where linkid='5904799666507688178' and yszs is not null) t where t.submittime between to_date('2017/03/03 00:00:00','yyyy/mm/dd hh24:mi:ss')and to_date('2017/03/10 00:00:00','yyyy/mm/dd hh24:mi:ss')group by to_char(submittime,'yyyy/mm/dd')order by coltime

（2）

select to_char(submittime,'yyyy/mm/dd') coltime,round(avg(yszs),2) avg_yszs,round(avg(speed),2) avg_speed from ( select linkid,submittime,yszs,speed from GDYDSJ_HISTORY_JWD where submittime between to_date('2017/03/03 00:00:00','yyyy/mm/dd hh24:mi:ss') and to_date('2017/03/10 00:00:00','yyyy/mm/dd hh24:mi:ss') ) t where t.linkid='5904799666507688178' and t.yszs is not null group by to_char(submittime,'yyyy/mm/dd') order by coltime

（3）

select to_char(submittime,'yyyy/mm/dd') coltime,round(avg(yszs),2) avg_yszs,round(avg(speed),2) avg_speed from ( select submittime,yszs,speed from GDYDSJ_HISTORY_JWD where linkid='5904799666507688178' and yszs is not null and submittime between to_date('2017/03/03 00:00:00','yyyy/mm/dd hh24:mi:ss') and to_date('2017/03/10 00:00:00','yyyy/mm/dd hh24:mi:ss') ) t group by to_char(submittime,'yyyy/mm/dd') order by coltime

（4）

select submittime,yszs,speed from GDYDSJ_HISTORY_JWD where linkid='5904799666507688178' and yszs is not null and submittime between to_date('2017/03/03 00:00:00','yyyy/mm/dd hh24:mi:ss') and to_date('2017/03/10 00:00:00','yyyy/mm/dd hh24:mi:ss')

（5）

select submittime,yszs,speed from GDYDSJ_HISTORY_JWD where linkid='5904799666507688178' and yszs is not null

（6）

select to_char(submittime,'yyyy/mm/dd') coltime,round(avg(yszs),2) avg_yszs,round(avg(speed),2) avg_speed from (select /*+index(GDYDSJ_HISTORY_JWD GDYDSJ_HISTORY_JWD_LINKID)*/ submittime,yszs,speed from GDYDSJ_HISTORY_JWD where linkid='5904801865530363496' and yszs is not null) t where t.submittime between to_date('2017/03/03 00:00:00','yyyy/mm/dd hh24:mi:ss') and to_date('2017/03/10 00:00:00','yyyy/mm/dd hh24:mi:ss') group by to_char(submittime,'yyyy/mm/dd') order by coltime

强制走全局索引

测试结果：

（1）改变where条件的顺序，对查询效率没有影响，执行计划一样 （2）上表中，submittime是局部索引，加入了该查询条件之后（也就是有两个查询条件，submittime局部索引，eventid全局索引），查询速度明显变慢 （3）对eventid（此表中的全局索引）强制索引，根据执行计划可知，此时Oracle进行全局索引，效率明显提升

疑问：

（1）同时使用全局索引和局部索引的时候，Oracle的查询策略是怎样的？是先走局部索引还是全局索引？ （2）为什么使用了Submittime索引（也就是局部索引）之后，查询效率反而变慢了？ （3）全局索引和局部索引的应用场景？什么时候使用全局索引，什么时候使用局部索引？