[Oracle] Testing of Local Index and Global Index

Preface

Oracle database

(1) For Oracle partitioned tables, there are two partitions: local index and global index.
(2) Table partitioning and index partitioning are two different concepts
 (3) Local index, also known as local index, has the same partition form as table partition, the same dependent columns and the same storage attributes.

Following is a practical application of a problem encountered, close-up of this article for discussion.

The current table structure is as follows:

-- 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 'Congestion incident ID';
comment on column GDYDSJ_HISTORY_JWD.dirvingdir
  is 'Congestion direction,For 1 and 2 hours xys Same direction as drawing,3:00 is opposite to the drawing direction';
comment on column GDYDSJ_HISTORY_JWD.speed
  is 'Link speed';
comment on column GDYDSJ_HISTORY_JWD.state
  is 'Section congestion';
comment on column GDYDSJ_HISTORY_JWD.length
  is 'road length';
comment on column GDYDSJ_HISTORY_JWD.linkid
  is 'Road link';
comment on column GDYDSJ_HISTORY_JWD.roadname
  is 'Name of reason';
comment on column GDYDSJ_HISTORY_JWD.roadtype
  is '//  Free Way
//  1. Main Street, City Speed Way
//  2. National Road
//  3. Main Road
//  4. Provincial 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 'Endpoint coordinates of road sections';
comment on column GDYDSJ_HISTORY_JWD.submittime
  is 'Deposit time, based on this';
comment on column GDYDSJ_HISTORY_JWD.yszs
  is 'Congestion Delay Index';
comment on column GDYDSJ_HISTORY_JWD.xys
  is 'Segment coordinate set';
-- 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;

The following is a comparison of execution plans for several query modes:

(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

Force global indexing

Test results:

(1) Changing the order of where conditions has no effect on query efficiency, just like executing the plan.
(2) In the table above, submittime is a local index. After adding the query condition (i.e. two query conditions, submittime local index and evented global index), the query speed becomes slower obviously.
(3) Compulsory index of Evetid (global index in this table). According to the execution plan, Oracle has a global index at this time, which improves the efficiency significantly.

Doubt:

(1) What is Oracle's query strategy when using both global and local indexes? Do you want to use local index or global index first?
(2) Why is the efficiency of queries slowed down when the Submittime index (i.e. local index) is used?
(3) Application scenarios of global and local indexing? When to use global index and when to use local index?

Posted by goldages05 on Mon, 15 Apr 2019 11:48:32 -0700

Programmer Group