ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 分区表中全局及本地分区索引什么时候会失效及处理[final]

分区表中全局及本地分区索引什么时候会失效及处理[final]

原创 Linux操作系统 作者:tolywang 时间:2011-01-22 14:44:50 0 删除 编辑


分区表中 local 索引的维护会在oracle 操作表分区的时候自动进行,需要注意的是global 索引,当global索引所在表执行alter table 涉及下列操作时,会导至该索引失效,需要重新建立:


 ADD PARTITION | SUBPARTITION
 COALESCE PARTITION | SUBPARTITION
 DROP PARTITION | SUBPARTITION
 EXCHANGE PARTITION | SUBPARTITION
 MERGE PARTITION | SUBPARTITION
 MOVE PARTITION | SUBPARTITION
 SPLIT PARTITION | SUBPARTITION
 TRUNCATE PARTITION | SUBPARTITION


因此,建议用户在执行上述操作sql 语句后附加update indexes 子句,oracle
即会自动维护全局索引,当然,需要注意这中间有一个平衡,你要平衡操作ddl 的
时间和重建索引哪个时间更少,以决定是否需要附加updateindexes 子句。

 

---------------------------------------------------------------------- 

 


执行alter table add partition 时未指定update indexes 子句:
a. 如果是range/list分区,其local 索引和global 索引不会受影响;
b. 如果是hash 分区,新加分区及有数据移动的分区的local 索引和global索引会被
置为unuseable,需要重新编译。

在执行drop partition时如果没有指定update indexes 子句,会导致glocal
索引的失效,对于local索引,删除分区时对应的索引分区会被同时删除,且
它分区的local 索引不会受到影响。


在执行split partition/subpartition 时,如果没有指定update indexes 子
句,都会造成local 和global 索引的失效。不过如果你split partition/
subpartition 的是个空分区,或者没有触发任何数据移动或变化,那么即使不加
update indexes,也不会影响到索引。当然,保险起见,建议你还是执行完之后,
查询一下数据字典,确认一下当前索引的状态。

 


下面简单测试一下:

 

1. 创建一个Range分区表:

CREATE TABLE DFMS.TEST04
PARTITION BY RANGE(OBJECT_ID)

  PARTITION P1 VALUES LESS THAN (2000)
    TABLESPACE LOG_DATA, 
  PARTITION P2 VALUES LESS THAN (8000)
    TABLESPACE LOG_DATA, 
  PARTITION P3 VALUES LESS THAN (20000)
    TABLESPACE LOG_DATA, 
  PARTITION P4 VALUES LESS THAN (40000)
    TABLESPACE LOG_DATA, 
  PARTITION PMAX VALUES LESS THAN (MAXVALUE)
    TABLESPACE LOG_DATA
)
AS
SELECT * FROM DBA_OBJECTS ;

 

2. 建立一个PK, 同时生成global index: 
alter table DFMS.TEST04 add constraint pk_id primary key(object_id); 

建立一个local index :
CREATE INDEX DFMS.IDX1_TEST04 ON DFMS.TEST04
(OBJECT_NAME)  LOCAL ;

 

3. 我们通过dba_indexes视图查看global index的状态发现是valid : 
select index_name, status, last_analyzed,partitioned
from dba_indexes where index_name='PK_ID' ;

本地索引local index通过dba_indexes查看的状态是N/A, 需要通过
dba_ind_partitions来查看,可以看到每个索引分区都是USABLE状态。
而通过DBA_PART_INDEXES可以看到这个本地分区索引的整体状态。

select * from dba_ind_partitions where index_name='IDX1_TEST04' ;
select * from dba_indexes where index_name='IDX1_TEST04' ;
select * from DBA_PART_INDEXES where index_name='IDX1_TEST04' ;

 

4. 因为存在maxvalue,我们先测试split对全局及本地索引的影响 .

4.1 新分区中都有数据的情况

alter table test04 split partition pmax at (80000) into 
(partition p5 tablespace log_data ,
 partition pmax  tablespace log_data); 

我们从table的脚本可以看出pmax被分成p5和pmax两部分 :

....
  PARTITION P5 VALUES LESS THAN (80000), 
  PARTITION PMAX VALUES LESS THAN (MAXVALUE) ;
.....


显然由于select max(object_id) from TEST04 的行数是101769,split
后旧分区中符合less than 80000的留在了第一个分区p5,其他的都存在
了第二个分区(新的pmax分区)。

我们查询global index及local index的状态:

select index_name, status, last_analyzed,partitioned
from dba_indexes where index_name='PK_ID' ; 

这里显然触发了数据的移动,global index索引状态变成UNUSABLE.

select * from dba_ind_partitions where index_name='IDX1_TEST04' ;

因为新split出来的分区(这里指p5)中有数据,原pmax中的数据被拆分到
新分区p5及新的pmax中,发现p1,p2,p3,p4 对应的本地索引仍然是USABLE,
而新的p5及新pmax对应的本地索引都是UNUSABLE. 

OK, 我们对global index及p5,pmax对应的本地分区索引进行rebuild :

alter index PK_ID rebuild online; 

然后查询发现global index变成valid :
select index_name, status, last_analyzed,partitioned
from dba_indexes where index_name='PK_ID' ; 

alter index IDX1_TEST04 rebuild partition p5 online; 
alter index IDX1_TEST04 rebuild partition pmax online;  
执行之后查询:
select * from dba_ind_partitions where index_name='IDX1_TEST04' ;
可以看到两个索引分区p5及pmax状态都变成USABLE .

 

4.2 新分区中有一个没有数据

TEST04 的行数是101769,那么我们将p6新分区设置为110000,那么pmax分区
显然就没有数据了。

alter table test04 split partition pmax at (110000) into 
(partition p6 tablespace log_data , partition pmax  tablespace log_data); 

查看global index及local index可以看到全局索引及每个本地索引分区都是
USABLE, 这是因为没有触发数据移动 。
select index_name, status, last_analyzed,partitioned from dba_indexes where index_name='PK_ID' ;
select * from dba_ind_partitions where index_name='IDX1_TEST04' ;

备注:在split pmax分区时新的分区名称可以随便起(不一定含有pmax),比如上面
的可以使用p6,p7, 只是p6会遵循less than 110000, 而第二个分区p7仍然是less
than maxvalue. 

 还有因为这里是表空间没有变化,如果非空的分区存储属性和原来的存储属性不一样,也会发生数据移动,也会导致索引失效。

 

5. 测试drop partition对全局及本地索引的影响。

对test04表的最后一个没有数据的pmax分区进行删除动作。
alter table test04 drop partition pmax ; 

因为删除的分区没有数据,所以不涉及数据变化,所以对全局及本地所以
都没有影响 。

假设我们要删除有数据的部分,既不保留分区也不保留数据,那么本地索引
不会受到影响,global index会失效。
alter table test04 drop partition p6 ; 
查询
select * from dba_indexes where index_name='PK_ID' ;
全局索引失效,状态变成UNUSABLE .
select * from dba_ind_partitions where index_name='IDX1_TEST04' ;
本地索引(其他分区)状态不变,为USABLE .

 


6. 测试add partition 对全局索引和本地索引的影响。
alter table test04 add partition p6  values less than (120000) ;
查询状态:
select * from dba_indexes where index_name='PK_ID' ;
select * from dba_ind_partitions where index_name='IDX1_TEST04' ;
发现Range分区,加入分区对于全局及本地索引都没有影响。
同样测试list分区,也可以知道加入分区对于全局及本地索引都没有影响。
这主要是因为没有触发数据的移动。

对于Hash分区,由于add parittion会发生数据分布平衡的I/O操作,数据
会发生移动,所以本地分区索引及全局索引都会置为UNUSABLE, 需rebuild.
下面做简单测试:


CREATE TABLE DFMS.TEST05
PARTITION BY HASH (OBJECT_ID)
PARTITIONS 8
STORE IN (LOG_DATA)
AS SELECT * FROM DBA_OBJECTS ; 

加入global及local index .
alter table DFMS.TEST05 add constraint pk_test05_id primary key(object_id); 
CREATE INDEX DFMS.IDX1_TEST05 ON DFMS.TEST05 (OBJECT_NAME)  LOCAL ;

加入新分区:
alter table test05 add partition ; 

查询
select * from dba_indexes where index_name='PK_TEST05_ID' ;
select * from dba_ind_partitions where index_name='IDX1_TEST05' ;
发现global index是UNUSABLE状态,本地分区索引中的第一个和最后一个
分区的本地分区索引是UNUSABLE状态,其它是USABLE. 显然因为数据从第一
个分区被拆分到了新的hasn分区,所以这两个分区中的数据发生了移动,
导致了本地分区索引的失效,因为有数据行的移动,当然global index也
变成了失效状态(UNUSABLE) .  

 

 


7. 测试truncate partition 对全局索引和本地索引的影响。


Truncate partition 就像truncate table 一样,直接从头部截断数据。在不指
定update indexes 子句的情况下,truncate partition 也会造成分区所在表的
global 索引失效。语法非常简单:
alter table tbname truncate partition/subpartition ptname;

alter table test04 truncate partition p6 ;
查询
select * from dba_indexes where index_name='PK_ID' ;
select * from dba_ind_partitions where index_name='IDX1_TEST04' ;
发现global index索引失效,本地分区索引状态都是USABLE .

 

8. 其他操作如 Merge Partitions,Exchange Partitions及coalesce partitions
等较少使用,这里不做测试。 

 

其实总之,如果发生数据移动,那么索引肯定是需要注意的。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-684473/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13470862