分区表中的index 失效及处理

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

———————————————————————-

一、测试环境

[oracle@testdb ~]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 11.2.0.2.0 Production on Wed Jan 4 14:52:40 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Dayangase 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

二、测试步骤
1.全局索引测试步骤

createtable yang(idnumber,namechar(100))
partition by range(id)
(partitiona valuesless than(10),
partition b valuesless than(20),
partition c valuesless than(30));

insert intoyang values(1,'a');
insert intoyang values(11,'a');
insert intoyang values(21,'a');
commit;

create index idx_yang_id on yang (id);

----------------------- add测试----------------------------

alter table yang add partition d values less than (40);
alter table yang add partition e values less than (maxvalue);

----------------------- drop测试---------------------------

insert into yang values(31,'a');
commit;

alter table yang drop partition e;
alter table yang drop partition d;
alter index idx_yang_id rebuild;

----------------------- truncate测试----------------------------

alter table yang add partition d values less than (40);
alter table yang add partition e values less than (maxvalue);

insert into yang values(31,'a');
commit;

alter table yang truncate partition e;
alter table yang truncate partition d;
alter index idx_yang_id rebuild;

----------------------- split测试---------------------------
alter table yang add partition d values less than (maxvalue);
alter table yang split partition d at (40) into (partition d,partition e);

insert into yang values(45,'a');
commit;
alter table yang split partition e at (50) into (partition e,partition f);

insert into yang values(65,'a');
commit;
alter table yang split partition f at (60) into (partition f,partition g);

insert into yang values(75,'a');
commit;
alter table yang split partition g at (70) into (partition g,partition h);
alter index idx_yang_id rebuild;

2.本地索引测试步骤

createtable yang(idnumber,namechar(100))
partition by range(id)
(partitiona valuesless than(10),
partition b valuesless than(20),
partition c valuesless than(30));

insert intoyang values(1,'a');
insert intoyang values(11,'a');
insert intoyang values(21,'a');
commit;

create index l_idx on yang (id) local;

----------------------- add测试----------------------------

alter table yang add partition d values less than (40);
alter table yang add partition e values less than (maxvalue);

----------------------- drop测试---------------------------

insert into yang values(31,'a');
commit;

alter table yang drop partition e;
alter table yang drop partition d;

----------------------- truncate测试----------------------------

alter table yang add partition d values less than (40);
alter table yang add partition e values less than (maxvalue);

insert into yang values(31,'a');
commit;

alter table yang truncate partition e;
alter table yang truncate partition d;

----------------------- split测试---------------------------
alter table yang add partition d values less than (maxvalue);
alter table yang split partition d at (40) into (partition d,partition e);

insert into yang values(45,'a');
commit;
alter table yang split partition e at (50) into (partition e,partition f);

insert into yang values(65,'a');
commit;
alter table yang split partition f at (60) into (partition f,partition g);

insert into yang values(75,'a');
commit;
alter table yang split partition g at (70) into (partition g,partition h);

alter index l_idx rebuild partition h;
alter index l_idx rebuild partition g;

三、测试结论 (range, list)

global index:

add partition  valid 
drop partition 分区中无数据：valid  有数据：unusable
truncate partition 分区中无数据：valid 有数据：unusable


local index:
add partition,drop partition,truncate partition 都不会导致失效

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

split partition  a->a,b

global index  :
a,b无数据 valid  
a有数据 b无数据 valid
a无数据 b有数据 valid
a,b都有数据 unusable

local index:
a,b无数据 usable
a有数据 b无数据 usable
a无数据 b有数据 usable
a,b均有数据 unusable

———————————————————

exchang partition  (range list)

reference:http://www.itpub.net/thread-1323472-1-1.html

我下面就主要对这两种办法讨论update global indexes。

建表SQL（其中，HAOPART2和HAOPART是一样的结构）：

createtable haopart(
id numbernot null,
c1 char(100),
c2 char(200),
c3 char(300)
)
PARTITION BY RANGE(id)
(
PARTITION PART01 VALUESLESS THAN(100),
PARTITION PART02 VALUESLESS THAN(200),
PARTITION PART03 VALUESLESS THAN(500),
PARTITION PART04 VALUESLESS THAN(1000),
PARTITION PARTMAX VALUESLESS THAN(MAXVALUE)
)
tablespace USERS
;

create indexhaolocal_1 on haopart(c1)local tablespaceUSERS;
create indexhaolocal_2 on haopart(c2)local tablespaceUSERS;
create indexhaolocal_3 on haopart(c3)local tablespaceUSERS;
create indexhaoglobal on haopart(id,c1,c2,c3)global tablespaceUSERS ;

insert intohaopart
select rownum,object_name,object_name,object_name
from dba_objects;

这样HAOPART就有3个local indexes和1个global index。

临时表建表SQL（其中，HAOTMP和HAOTMP2是一样的结构）：

createtable haotmp
(
id numbernot null,
c1 char(100),
c2 char(200),
c3 char(300)
) tablespaceusers;

create indextmphao_1 onhaotmp(c1)tablespace USERS;
create indextmphao_2 onhaotmp(c2)tablespace USERS;
create indextmphao_3 onhaotmp(c3)tablespace USERS;

一.以exchange partition为例，不加update global indexes时：
1. 如果partiton里有数据，global index则会失效
SQL> select count(*) from haopart2 partition(part04);
  COUNT(*)
———-
500
SQL> select count(*) from haotmp2;
  COUNT(*)
———-
0
SQL> alter table haopart2 exchange partition part04 with table haotmp2  including indexes without validation;
table altered.

SQL> select INDEX_NAME,STATUS,PARTITIONED from dba_indexes where  table_NAME=’HAOPART2′ and PARTITIONED=’NO’;
INDEX_NAME                     STATUS   PAR
—————————— ——– —
HAOGLOBAL2                     UNUSABLE NO

   2.  如果partition里没有任何数据，新的临时表有数据，global index也会失效。

SQL> select count(*) from haotmp2;
  COUNT(*)
———-
500

SQL>  select count(*) from haopart2 partition(part04);
  COUNT(*)
———-
0
SQL> alter index haoglobal2 rebuild;
Index altered.

SQL> select INDEX_NAME,STATUS,PARTITIONED from dba_indexes where  table_NAME=’HAOPART2′ and PARTITIONED=’NO’;
INDEX_NAME                     STATUS   PAR
—————————— ——– —
HAOGLOBAL2                     VALID    NO

SQL> alter table haopart2 exchange partition part04 with table haotmp2 including indexes without validation;
table altered.

SQL> select INDEX_NAME,STATUS,PARTITIONED from dba_indexes where
  2  table_NAME=’HAOPART2′ and PARTITIONED=’NO’;
INDEX_NAME                     STATUS   PAR
—————————— ——– —
HAOGLOBAL2                     UNUSABLE NO

3.即使partition和临时表都没有数据，也会使global index失效。
SQL>  alter table haopart2 truncate partition part04;
table truncated.

SQL> truncate table haotmp2;
table truncated.

SQL> alter index haoglobal2 rebuild;
Index altered.

SQL> select INDEX_NAME,STATUS,PARTITIONED from dba_indexes where table_NAME=’HAOPART2′ and PARTITIONED=’NO’;
INDEX_NAME                     STATUS   PAR
—————————— ——– —
HAOGLOBAL2                     VALID    NO

SQL> alter table haopart2 exchange partition part04 with table haotmp2  including indexes without validation;
table altered.

SQL> select INDEX_NAME,STATUS,PARTITIONED from dba_indexes where  table_NAME=’HAOPART2′ and PARTITIONED=’NO’;
INDEX_NAME                     STATUS   PAR
—————————— ——– —
HAOGLOBAL2                     UNUSABLE NO

二.以exchange partition为例，加上update global indexes时：
1. 无论任何时候，global index都不会失效。

SQL> select count(*) from haopart2 partition(part04);
  COUNT(*)
———-
500
SQL> select count(*) from haotmp2;
  COUNT(*)
———-
56

SQL> select INDEX_NAME,STATUS,PARTITIONED from dba_indexes where  table_NAME=’HAOPART2′ and PARTITIONED=’NO’;
INDEX_NAME                     STATUS   PAR
—————————— ——– —
HAOGLOBAL2                     VALID    NO

SQL> alter table haopart2 exchange partition part04 with table haotmp2   including indexes without validation update global indexes;
table altered.

SQL> select INDEX_NAME,STATUS,PARTITIONED from dba_indexes where
  2  table_NAME=’HAOPART2′ and PARTITIONED=’NO’;
INDEX_NAME                     STATUS   PAR
—————————— ——– —
HAOGLOBAL2                     VALID    NO

  2. 会对原表加Mode=3 TM lock,会对原表做ddl的partition加Mode=6 TM lock。

select o.OBJECT_ID,o.OBJECT_NAME,o.SUBOBJECT_NAME,o.OBJECT_TYPE,l.LMODE
from dba_objects o,v$lock l
where o.OBJECT_ID=l.ID1
and l.TYPE=’TM’
and l.sid=1094
;
OBJECT_ID OBJECT_NAM SUBOBJECT_ OBJECT_TYPE              LMODE
———- ———- ———- ——————- ———-
10597 HAOPART    PART04     table PARTITION              6
     10593 HAOPART               table                        3
     10604 HAOTMP                table                        6

3. exchange partition update global indexes不会block使用global index的select语句，但是由于大量的update index操作，所以会使得查询大量走undo，所以查询会变慢。

在如下exchange partition update global indexes命令进行时：

alter table haopart exchange partition part04 with table haotmp
including indexes without validation
update global indexes;


在另一个session执行如下走global index的select：
select count(*) from haopart where id <=1000;

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |     4 |  2902   (1)| 00:00:35 |
|   1 |  SORT AGGREGATE   |           |     1 |     4 |            |          |
|*  2 |   INDEX RANGE SCAN| HAOGLOBAL | 31744 |   124K|  2902   (1)| 00:00:35 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"<=1000)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2914  consistent gets
          0  physical reads
          0  redo size
        516  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4095  consistent gets
          0  physical reads
      27052  redo size
        516  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5130  consistent gets
          0  physical reads
      49140  redo size
        516  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

可见，执行计划是不变的，但是逻辑读不断上升，也产生大量的redo。
明显查询了undo。

4. exchange partition update global index会阻碍该partition上的dml，但不会阻碍其他partition上的dml。

根据第二点，由于这条语句会对该partition加Mode=6 TM lock，所以很显然，该partition是无法做dml的。

我们会看到等待事件：enq: TM – contention:TM-3:2:

—————————–

exchange partition –local index  测试

SQL>create tablet_partition (idnumber,namevarchar2(50)) 
  2           partition byrange(id)( 
         partitionp1 valuesless than(10) , 
         partitionp2 valuesless than(20) , 
         partitionp3 valuesless than(30) , 
         partitionpmax valuesless than(maxvalue) 
         ) 
   ;    3    4    5   6    7   8 

Table created.

SQL> createindex partition_idx_name on t_partition(name)local;

Index created.

SQL>  createtable t_no_partition(idnumber,namevarchar2(50));

Table created.

SQL> createindex idx_nameon t_no_partition(name); 

Index created.


SQL> insertinto t_no_partitionvalues(11,'11');

1 rowcreated.

SQL> insertinto t_no_partitionvalues(12,'ddd');

1 rowcreated.

SQL> insertinto t_no_partitionvalues(13,'13dsa'); 

1 rowcreated.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> altertable t_partitionexchange partitionp2  withtable t_no_partition INCLUDING INDEXES withvalidation; 

Table altered.

SQL> selectpartition_name, status fromuser_ind_partitionswhere  index_name=upper('partition_idx_name');

PARTITION_NAME              STATUS
------------------------------ --------
P1                  USABLE
P2                  USABLE
P3                  USABLE
PMAX                  USABLE



SQL> altertable t_partitionexchange partitionp1  withtable t_no_partition;

Table altered.



SQL>  altertable t_partitionexchange partitionp3 withtable t_no_partition;

Table altered.

SQL> selectpartition_name, status fromuser_ind_partitionswhere  index_name=upper('partition_idx_name');

PARTITION_NAME              STATUS
------------------------------ --------
P1                  USABLE
P2                  USABLE
P3                  UNUSABLE
PMAX                  USABLE

可以看到不带INCLUDING INDEXES with validation 被exchange的local index partition 会失效，对于存在主键的分区表，可以在主键上以DISABLE VALIDATE方式创建unique constraint约束，以代替全局的主键索引。若交换表上存在主键索引的话，那么建议在交换前暂时将该索引drop掉，待交换完成后再重建。

———————————————
hash partition的测试：

SQL>CREATE tableliu(idnumber,namevarchar2(20))
PARTITION BY HASH(id)
PARTITIONS 8
STORE IN(users) 2    3   4 
  5  ;

table created.

SQL>
SQL>
SQL> begin    
  2  fori in1..10000 loop
  3  insertinto liuvalues (i,null);
  4  commit;
  5  endloop;
  6  end;
  7  /
alter tableliu  addconstraint pk_liu_id  primarykey(id);
CREATE INDEXidx_liu_id  ON liu (name) LOCAL ;




Partition Name    MaxValue    Length    tablespace   Compression    Rows    Blocks   Empty Blocks   Last Analyzed   Avg Space   # Subparts

SYS_P153        0    USERS   DISABLED    1,188   46    978   2012/1/414:25:41   7257    0
SYS_P152        0    USERS   DISABLED    1,201   46    978   2012/1/414:25:41   7248    0
SYS_P150        0    USERS   DISABLED    1,232   46    978   2012/1/414:25:41   7226    0
SYS_P155        0    USERS   DISABLED    1,259   46    978   2012/1/414:25:41   7208    0
SYS_P151        0    USERS   DISABLED    1,262   46    978   2012/1/414:25:41   7206    0
SYS_P156        0    USERS   DISABLED    1,280   46    978   2012/1/414:25:41   7193    0
SYS_P149        0    USERS   DISABLED    1,283   46    978   2012/1/414:25:41   7191    0
SYS_P154        0    USERS   DISABLED    1,295   46    978   2012/1/414:25:41   7183    0

alter table liu  add partition ;

idx_liu_id 分区变为unusable:

PartitionName    tablespace   Last Analyzed   Max Value   Length    Rows    BLevel   Distinct Keys    # Subparts

UNUSABLE    SYS_P165    USERS   2012/1/414:30:15       0    0   0    0   0
VALID    SYS_P166   USERS    2012/1/414:30:15       0    0   0    0   0
VALID    SYS_P167   USERS    2012/1/414:30:15       0    0   0    0   0
VALID    SYS_P168   USERS    2012/1/414:30:15       0    0   0    0   0
VALID    SYS_P169   USERS    2012/1/414:30:15       0    0   0    0   0
VALID    SYS_P170   USERS    2012/1/414:30:15       0    0   0    0   0
VALID    SYS_P171   USERS    2012/1/414:30:15       0    0   0    0   0
VALID    SYS_P172   USERS    2012/1/414:30:15       0    0   0    0   0
UNUSABLE    SYS_P173    PURCHASE           0               0

select status  from dba_indexes where index_name=’PK_LIU_ID’;

SQL> select status  from dba_indexes where index_name=’PK_LIU_ID’;

STATUS
——–
UNUSABLE

SQL>

同样变为了unusable,hash分区在add partition时候 由于数据会重组,所以local,global index 都会变成unusable