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

最新推荐文章于 2023-06-10 13:05:31 发布
最新推荐文章于 2023-06-10 13:05:31 发布
阅读量1k 收藏 1
点赞数 1
分类专栏： Oracle 文章标签： oracle 数据库 分区索引 分区表 全局索引
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wk1134314305/article/details/61267733
版权

前言

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）全局索引和局部索引的应用场景？什么时候使用全局索引，什么时候使用局部索引？

KingWang_WHU
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
oracle 局部索引,Oracle局部索引和全局索引
weixin_29917533的博客
04-03 722
表可以按range，hash，list分区，表分区后，其上的索引和普通表上的索引有所不同，oracle对于分区表上的索引分为2类，即局部索引和全局索引，下面分别对这2种索引的特点和局限性做个总结。局部索引local index1. 局部索引一定是分区索引，分区键等同于表的分区键，分区数等同于表的分区说，一句话，局部索引的分区机制和表的分区机制一样。2. 如果局部索引的索引列以分区键开头，则称为...
局部索引和全局索引
zuixinnet的专栏
03-31 1685
局部索引local index 1. 局部索引一定是分区索引，分区键等同于表的分区键，分区数等同于表的分区说，一句话，局部索引的分区机制和表的分区机制一样。 2. 如果局部索引的索引列以分区键开头，则称为前缀局部索引。 3. 如果局部索引的列不是以分区键开头，或者不包含分区键列，则称为非前缀索引。 4. 前缀和非前缀索引都可以支持索引分区消除，前提是查询的条件中包含索引
索引分区分为:本地(局部)索引(local index) 全局索引(global index)
niukailun523的专栏
06-04 1724
一 本地索引 1.普通索引 SQL > CREATE INDEX INDEX_NAME ON TABLE (COLUMN) local ( partition part_idx_01 tablespace index_space01, partition part_idx_02 tablespace index_space02, partition part_idx_03 table
ORACLE局部索引和全局索引
javayunsky的专栏
04-22 1059
ALTER INDEX UR_INDEX_NAME UNUSABLE; ALTER INDEX UR_INDEX_NAME REBUILD; 当需要网一个表中插入大量数据的时候，最好能够先把索引取消，以提高插入的速度。如现在数据库管理员需要往表Product中添加记录的话，无论其采用什么方式，如采用Select into语句，还是采用图形化的导入工具都好。当需要插入的记录比较多的
Oracle局部索引
qq_41450814的博客
03-26 139
局部索引
分区索引,本地索引,全局索引的区别
04-13
oracle 分区索引,本地索引,全局索引的区别
深入oracle分区索引的详解
09-10
在Oracle中，有两种类型的分区索引：局部索引和全局索引。 局部索引是专门为分区表设计的，它们与表的分区机制相同，即索引的分区与表的分区一一对应。如果索引的列包含分区键作为前几列，这被称为前缀局部索引，...
Oracle数据库中建立索引的基本方法讲解
09-10
6. **创建局部索引**：对于分区表，可以创建局部索引，确保每个分区都有对应的索引： ```sql CREATE INDEX TG_CDR04_SERV_ID_IDX ON TG_CDR04 (SERV_ID) PCTFREE 5 TABLESPACE TBS_AK01_IDX STORAGE (MAXEXTENTS...
oracle索引与分区索引介绍
07-03
分区索引可以是局部索引或全局索引，全局索引可以跨越多个分区，而局部索引则只能作用于一个分区。 创建分区索引 创建分区索引可以使用以下语句： ```sql CREATE INDEX <index_name> ON () GLOBAL PARTITION BY ...
Oracle全局索引和局部索引
jc_benben的专栏
03-06 2234
Oracle数据库中，分区表有两种类型的分区索引，全局索引和本地索引，其中本地索引又可以分为本地前缀索引和本地非前缀索引 全局索引以整个表的数据为对象建立索引， 索引分区中的索引条目既可能是基于相同的键值但是来自不同的分区，也可能是多个不同键值的组合。 所以，全局索引既允许索引分区的键值和表分区键值相同，也可以不相同. 举例 先建一个分区表： create table t_orders ...
OBCP第四章 SQL调优-局部索引与全局索引
明确的爱，真诚的喜欢，直接的厌恶，站在太阳底下的坦荡，被坚定的选择。
03-29 515
OceanBase的每一张表都有主键，并在内部以主键为序组织数据。如果在创建用户表时不显式指定主键，系统会自动为表生成隐藏主键，隐藏主键不可被查询
oracle 全局 局部索引,Oracle全局索引和局部索引
weixin_39862794的博客
04-09 508
Oracle全局索引和局部索引 表可以按range，hash，list分区，表分区后，其上的索引和普通表上的索引有所不同，oracle对于分区表上的索引分为2类，即局部索引和全局索引，下面分别对这2种索引的特点和局限性做个总结。 局部索引local index 1. 局部索引一定是Oracle全局索引和局部索引　　表可以按range，hash，list分区，表分区后，其上的索引和普通表上的索引有所...
全局分区索引和局部分区索引
cri5768的博客
09-03 359
最近正在温习ORACLE分区表，下面是关于全局分区索引和局部分区索引，以及前缀局部分区索引和非前缀局部分区索引的概念和需要注意的地方全局分区索引：索引分区不按照表的分区进行，索引的分区可以指向任何表分区局部分区索引：索引分区完成...
全局分区索引与局部分区索引
@DBA_白老大
01-24 5527
全局分区索引与局部分区索引
分区表全局索引和局部索引对比
vip9472
06-10 151
局部索引只索引和维护单个分区上的数据,全局索引维护全表数据,开销比局部索引大（优先局部索引,数据维护简单,DDL操作快）局部索引 10毫秒 索引有效 对子分区的DDL操作,速度非常快,不会有索引重建的耗时,几乎感觉不到影响。全局索引 9秒 索引有效 对子分区的DDL操作,耗时很长,需重建全局索引,此过程耗时大,锁表。--查询SQL（有索引时查询,无论是全局索引还是局部索引,性能都很好,差别不大）--创建表结构(18毫秒)--创建全局索引（10秒）--创建局部索引（8秒）
部分索引
Health's Box
04-23 863
部分索引（partial index） 是建立在一个表的子集上的索引；该子集是由一个条件表达式定义的 (叫做部分索引的谓词)． 该索引只包含表中那些满足这个谓词的行． 部分索引的主要动机是为了避免对普通数值建立索引? 因为如果一个在一个普通数值(那种只占表中所有行几个百分点的数值) 上的查询不会使用索引， 那么就没有在索引中保存这些(普通)行的必要． 这样就可以减小索引的尺寸，这样就可以提
分区表、分区索引和全局索引部分总结
css2200的博客
07-30 117
分区表、分区索引和全局索引：　　在一个表的数据超过过2000万条或占用2G空间时，建议建立分区表。 create table ta(c1 int,c2 varchar2(16),c3 varchar2(64),c4 i...
oracle如何查看全局索引和分区索引
anddyhua's Cookbook
04-18 5782
create table ta(c1 int,c2 varchar2(16),c3 varchar2(64),c4 int ,constraint pk_ta primary key (c1)) partition by range(c1)( partition p1 values less than (10000), partition p2 values less than (20000), partition p3 values less than (30000), partition p4 val.
oracle中本地索引和全局索引的区别
最新发布
06-11
在Oracle数据库中，本地索引和全局索引是两种不同类型的索引。 本地索引是指只针对表中的某一列或某几列创建的索引，它们只在表所在的分区上创建，而不会跨越多个分区。本地索引的优点是查询速度快，但是如果需要...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值