您现在的位置是:首页>博客详情

Oracle千万级表分区实战(5)oracle分区表的常规操作导致对索引的影响

FreshMan2020年03月18日 16:23oracle,分表 分区 local index57

简介Oracle千万级表分区实战,该系列文章将详细讲述一个实际的问题的解决过程,并将讲述在解决该问题中需要积累的知识和注意事项。有目的的学习总是最快的。将分为以下几步:1、问题及需要处理流程;2、基本知识积累;3、解决问题

       上篇中我们只是介绍了如何创建分区表,但是对于生产环境中的表必定会有索引的存在,同时在处理原有大数据量表转为分区表时也会出现对原有索引的处理。现在我们来说一说分区表的操作对索引的影响。

       oracle分区表目前已经很普遍的应用于我们的生产系统,但是在日常需要维护分区表的时候,一些对于分区表的基本操作的时候,我们难免会对分区表上的索引是否失效有些担心,那么今天我就带大家看下具体哪些操作会导致分区表上的索引失效。

       为了控制篇幅,本次实验只针对RANGE分区,其他两种分区请有兴趣的同学自行测试哦~~~

       1、分区表索引的分类

       oracle分区表的索引类型分为两种,一种是Local索引又称本地索引,一种是Global索引也叫全局索引。本次实验不具体介绍这两种索引对分区表访问所产生的性能问题方面的差异,只介绍一些常用的DDL操作对分区表上的索引的一些影响。

        2、针对分区表做各种DDL操作,查看对索引的影响

        2.1创建分区表,并创建全局索引和分区索引

create table tmp_test_range
(
  id number,
  id_local number,
  name varchar2(30),
  int_date date,
  bz varchar2(20)
)
PARTITION BY RANGE(int_date)
--interval(numtodsinterval (1,'DAY'))
(
partition P201511 values less than (to_date('20151201','yyyymmdd')),
partition P20151201 values less than (to_date('20151202','yyyymmdd')),
partition P20151203 values less than (to_date('20151203','yyyymmdd')),
partition P20151204 values less than (to_date('20151204','yyyymmdd')),
partition P20151205 values less than (to_date('20151205','yyyymmdd')),
partition P20151206 values less than (to_date('20151206','yyyymmdd')),
partition P20151207 values less than (to_date('20151207','yyyymmdd'))
);

        向分区表中插入数据:

declare
  v_date date := to_date('20151127', 'yyyy-mm-dd');
begin
  for c in 1 .. 9 loop
    for d in 1 .. 100 loop
      insert into tmp_test_range
      values
        (c || d, c || d, '测试数据', v_date, 'BZ');
    end loop;
    v_date := v_date + 1;
  end loop;
  commit;
end;

创建索引:分别在ID, ID_LOCAL 和创建一个全局索引和分区索引

create index I_TMP_TEST_RANGE_G on tmp_test_range(id) nologging;
create index I_TMP_TEST_RANGE_L on tmp_test_range(id_local)
 nologging LOCAL;

查看索引的状态:

local索引:

local_index.png

Global索引:

global_index.png

2.2  DDL操作对全局索引的影响:

好了,前面的基础工作已经准备完毕,下面我们开始做一些DDL操作,看下对全局索引的影响。

添加分区和对其中一个分区重命名:

ALTER TABLE tmp_test_range ADD PARTITION  P20151208
 values less than (to_date('20151208','yyyymmdd'));

        查看索引的状态:

local索引:

添加分区本地索引.png

Global索引:

global_index.png

总结:添加分区和对分区重新命名并不会导致Global和local索引失效。

删除表中的分区:

ALTER TABLE tmp_test_range DROP PARTITION  P20151208 ;

        查看索引情况:

local索引:

删除分区后本地索引.png

Global索引:

删除分区全局索引.png

OK,经过上面的实验可能你很容易就能得出结论说,删除分区不会导致Global索引失效,其实不然,让我们看一种其他情况:

查看某个分区的数据(P201511),P201511存在数据的。

        删除其中一个分区:

alter table tmp_test_range drop partition P201511;

        查看Global索引:

删除有数据的分区后全局索引失效.png

所以在删除分区表中的分区的时候,一定要确认有没有数据存在。

总结:删除分区表中的分区的时候 或者truncate 分区中的数据时,一定要确认分区中是否有数据存在,如果没有数据不会导致Global失效,反之则会导致Global索引失效。而对其他分区上的local索引都不会造成影响。

        重建索引:

--重建全局索引
alter index I_TMP_TEST_RANGE_G rebuild;
--重建本地索引
alter index I_TMP_TEST_RANGE_L rebuild partition P20151208 online;

合并分区:

合并分区有两种方式,一种是维护索引的,一种是不维护索引。我们先来看不维护索引的。

ALTER TABLE tmp_test_range MERGE PARTITIONS P201511,P20151201 INTO PARTITION P20151208;

查看索引的情况:

local索引:

合并分区后索引失效.png

Global索引

合并分区后全局索引失效.png

总结:Global索引,做合并分区操作的时候会导致Global索引失效,所以操作的时候一定要当心哦!!!

local 索引不会维护合并后的分区,但是不会影响其他的分区,合并分区操作还会将原来被合并的分区删除。

当然oracle 也提供了合并分区的时候维护索引的操作,当大家在线上操作的时候,请使用下面的语句对分区做合并。

ALTER TABLE tmp_test_range MERGE PARTITIONS P201511,
P20151201 INTO PARTITION P20151208 update  indexes ;

这样就不会在做合并分区操作的时候,导致索引失效了。


拆分分区:

拆分分区同样也是有两种方式,一种是直接拆分并不维护索引,另一种是带维护索引的拆分方式。具体操作见下面的实验:

我们先看维护索引的方式拆分:

ALTER TABLE tmp_test_range SPLIT PARTITION P20151208 at (date'2015-12-01')
    INTO (PARTITION P201511,PARTITION P20151201) update indexes;

查看索引的状态:

拆分索引并更新索引本地.png拆分索引并更新索引全局.png

果然使用update indexes的方式进行拆分Global索引和local索引都是正常的。那下面我们再来看下不维护索引的方式:

ALTER TABLE tmp_test_range EXCHANGE PARTITION P201511
  WITH TABLE tmp_test_range_2 update INDEXES;

拆分.png

还是没有令我们失望,使用这种直接拆分的方式两种索引都有失效的情况发生,

local索引在新增的两个分区上的索引失效,Global索引还是不负众望的失效了。

总结:

对分区表进行拆分分区的时候如果不加参数 update indexes 会导致 新增分区上的local索引失效,Global索引失效。采用update  indexes 的方式这两种索引都不会失效。

3、总结:

好了,上面我们说了那么多,让我们总结一下,具体哪些操作会对分区表上的索引有一定的影响。

两种索引都不会产生影响的操作:

1.添加分区

2.删除分区(分区中没有数据)

3.对分区重命名


会产生影响的:

1.合并分区

新增分区上的local索引失效

Global索引失效

PS:使用update indexes 的方式可以避免索引失效的发生(包括local索引和Global索引)。


2、拆分分区

拆分出来的分区上的local索引失效

Global索引失效

PS:使用update indexes 的方式可以避免索引失效的发生(包括local索引和Global索引)。


3、分区交换

被交换分区上的local索引失效

Global索引失效

PS:使用update indexes 的方式可以避免Global索引失效的发生,不能避免local索引失效。


4、删除分区 或者truncate分区中的数据 时,如果被删除的分区上有数据存在,会导致Global索引失效。

通过上面的实验我们可以看出对于分区表来说,且不论性能如何,就单单对方便数据管理来说,还是创建local索引更加方便对分区表中数据的管理。

上一篇: Oracle千万级表分区实战(4)创建分区表

下一篇: 没有了