分区表中 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.全局索引测试步骤
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);
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.本地索引测试步骤
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;
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)
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是一样的结构):
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是一样的结构):
(
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命令进行时:
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 测试
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的测试:
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:
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