分区表中 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.全局索引测试步骤
create table yang (id number,name char(100))
partition by range(id)
(partition a values less than(10),
partition b values less than(20),
partition c values less than(30));
insert into yang values(1,’a’);
insert into yang values(11,’a’);
insert into yang 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.本地索引测试步骤
create table yang (id number,name char(100))
partition by range(id)
(partition a values less than(10),
partition b values less than(20),
partition c values less than(30));
insert into yang values(1,’a’);
insert into yang values(11,’a’);
insert into yang 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是一样的结构）：
create table haopart (
id number not null,
c1 char(100),
c2 char(200),
c3 char(300)
)
PARTITION BY RANGE(id)
(
PARTITION PART01 VALUES LESS THAN (100),
PARTITION PART02 VALUES LESS THAN (200),
PARTITION PART03 VALUES LESS THAN (500),
PARTITION PART04 VALUES LESS THAN (1000),
PARTITION PARTMAX VALUES LESS THAN (MAXVALUE)
)
tablespace USERS
;
create index haolocal_1 on haopart(c1) local tablespace USERS;
create index haolocal_2 on haopart(c2) local tablespace USERS;
create index haolocal_3 on haopart(c3) local tablespace USERS;
create index haoglobal on haopart(id,c1,c2,c3) global tablespace USERS ;
insert into haopart
select rownum,object_name,object_name,object_name
from dba_objects;
这样HAOPART就有3个local indexes和1个global index。
临时表建表SQL（其中，HAOTMP和HAOTMP2是一样的结构）：
create table haotmp
(
id number not null,
c1 char(100),
c2 char(200),
c3 char(300)
) tablespace users;
create index tmphao_1 on haotmp(c1) tablespace USERS;
create index tmphao_2 on haotmp(c2) tablespace USERS;
create index tmphao_3 on haotmp(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 table t_partition (id number,name varchar2(50))
2 partition by range(id)(
partition p1 values less than (10) ,
partition p2 values less than (20) ,
partition p3 values less than (30) ,
partition pmax values less than (maxvalue)
)
; 3 4 5 6 7 8
Table created.
SQL> create index partition_idx_name on t_partition (name) local;
Index created.
SQL> create table t_no_partition (id number,name varchar2(50));
Table created.
SQL> create index idx_name on t_no_partition(name);
Index created.
SQL> insert into t_no_partition values(11,’11’);
1 row created.
SQL> insert into t_no_partition values(12,’ddd’);
1 row created.
SQL> insert into t_no_partition values(13,’13dsa’);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> alter table t_partition exchange partition p2 with table t_no_partition INCLUDING INDEXES with validation;
Table altered.
SQL> select partition_name, status from user_ind_partitions where index_name=upper(‘partition_idx_name’);
PARTITION_NAME STATUS
—————————— ——–
P1 USABLE
P2 USABLE
P3 USABLE
PMAX USABLE
SQL> alter table t_partition exchange partition p1 with table t_no_partition;
Table altered.
SQL> alter table t_partition exchange partition p3 with table t_no_partition;
Table altered.
SQL> select partition_name, status from user_ind_partitions where 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 table liu(id number,name varchar2(20))
PARTITION BY HASH (id)
PARTITIONS 8
STORE IN (users) 2 3 4
5 ;
table created.
SQL>
SQL>
SQL> begin
2 for i in 1..10000 loop
3 insert into liu values (i,null);
4 commit;
5 end loop;
6 end;
7 /
alter table liu add constraint pk_liu_id primary key(id);
CREATE INDEX idx_liu_id ON liu (name) LOCAL ;
Partition Name Max Value Length tablespace Compression Rows Blocks Empty Blocks Last Analyzed Avg Space # Subparts
SYS_P153 0 USERS DISABLED 1,188 46 978 2012/1/4 14:25:41 7257 0
SYS_P152 0 USERS DISABLED 1,201 46 978 2012/1/4 14:25:41 7248 0
SYS_P150 0 USERS DISABLED 1,232 46 978 2012/1/4 14:25:41 7226 0
SYS_P155 0 USERS DISABLED 1,259 46 978 2012/1/4 14:25:41 7208 0
SYS_P151 0 USERS DISABLED 1,262 46 978 2012/1/4 14:25:41 7206 0
SYS_P156 0 USERS DISABLED 1,280 46 978 2012/1/4 14:25:41 7193 0
SYS_P149 0 USERS DISABLED 1,283 46 978 2012/1/4 14:25:41 7191 0
SYS_P154 0 USERS DISABLED 1,295 46 978 2012/1/4 14:25:41 7183 0
alter table liu add partition ;
idx_liu_id 分区变为unusable:
Partition Name tablespace Last Analyzed Max Value Length Rows BLevel Distinct Keys # Subparts
UNUSABLE SYS_P165 USERS 2012/1/4 14:30:15 0 0 0 0 0
VALID SYS_P166 USERS 2012/1/4 14:30:15 0 0 0 0 0
VALID SYS_P167 USERS 2012/1/4 14:30:15 0 0 0 0 0
VALID SYS_P168 USERS 2012/1/4 14:30:15 0 0 0 0 0
VALID SYS_P169 USERS 2012/1/4 14:30:15 0 0 0 0 0
VALID SYS_P170 USERS 2012/1/4 14:30:15 0 0 0 0 0
VALID SYS_P171 USERS 2012/1/4 14:30:15 0 0 0 0 0
VALID SYS_P172 USERS 2012/1/4 14: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
