oracle分区表的常规操作导致对索引的影响

原文地址：http://blog.itpub.net/30109892/viewspace-1874763/

原创 Oracle 作者：bestpaydata 时间：2015-12-18 18:17:44  4344  0

oracle分区表的常规操作导致对索引的影响

     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索引 ：

Global索引：

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

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

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

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

 

ALTER TABLE tmp_test_range RENAME PARTITION P20151207 TO P20151207_2;

查看索引情况：

Global

Local 索引：

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

删除表中的分区：

ALTER TABLE tmp_test_range DROP PARTITION  P20151208 ;

清空其中一个分区中的数据：

ALTER TABLE tmp_test_range TRUNCATE PARTITION P201511;

查看索引情况：

local索引会将被删除的分区上的local索引删除，不会影响到其他分区的索引。

Global 索引：

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

查看某个分区的数据（P201511），看下图是存在数据的：

现在对分区进行删除：

alter table tmp_test_range drop partition P201511;

或者：
ALTER TABLE tmp_test_range TRUNCATE PARTITION P201518;

查看Global索引：

唉，还是失效了，所以在删除分区表中的分区的时候，一定要确认有没有数据存在。

总结：

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

合并分区：

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

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

查看索引的情况：

总结：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 P201511 at (date'2015-11-28')    INTO (PARTITION TAB_PARTOTION_05,PARTITIONTAB_PARTOTION_OTHERE) update indexes;

查看索引的情况：

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

ALTER TABLE tmp_test_range SPLIT PARTITION P201511 at (date'2015-11-28')

    INTO (PARTITION TAB_PARTOTION_05,PARTITION TAB_PARTOTION_OTHERE) ;

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

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

总结：

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

交换分区：

交换分区同样也是有两种方式，一种是自动维护索引的，一种是直接交换分区不做维护索引操作，显然第二种方式会导致索引失效。具体见下面的实验（下面两个不要同时操作）：

ALTER TABLE tmp_test_range EXCHANGE PARTITION P201511 WITH TABLE tmp_test_range_2;

 

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

 

查看两个索引情况：

果不其然，交换分区还是会导致Global索引失效，local索引还是被交换的分区上的索引失效。所以在做交换分区的时候，我们还是可以使用update indexes 来维护索引Global索引，但是对于local索引即使使用update  indexes 也会导致local索引失效，还是要再重新维护 。

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索引更加方便对分区表中数据的管理。

author:冯栋华