本文详细探讨了在Oracle中，对带有全局索引的分区表执行Exchange Partition和Truncate Partition操作时，如何影响全局索引的可用性。如果不使用`UPDATE GLOBAL INDEXES`选项，这些操作可能导致全局索引变得无效。而使用此选项，Oracle将自动更新全局索引，保持其有效状态，但可能增加查询的逻辑读和redo日志。此外，Exchange Partition操作会锁定相应分区，阻止在此期间的DML操作。
我们知道，如果要在一个table partition上做DDL时，是可能会invalidate global index的。
但是，如果我们加上update global indexes，或update indexes语句，那么Oracle会自动帮我们update global index，使其继续保持valid状态，而且在其间global index可以继续使用。
当我们需要定期清除有global index的partition table的一个partition时，一般有两种办法。
其一是exchange partition，其二是truncate partition。
这两种办法，加不加update global indexes也是有区别的。
我下面就主要对这两种办法讨论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
2 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
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
2 TABLE_NAME='HAOPART2' and PARTITIONED='NO';
INDEX_NAME STATUS PAR
------------------------------ -------- ---
HAOGLOBAL2 VALID NO
SQL> alter table haopart2 exchange partition part04 with table haotmp2
2 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
2 TABLE_NAME='HAOPART2' and PARTITIONED='NO';
INDEX_NAME STATUS PAR
------------------------------ -------- ---
HAOGLOBAL2 VALID NO
SQL> alter table haopart2 exchange partition part04 with table haotmp2
2 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
二.以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
2 TABLE_NAME='HAOPART2' and PARTITIONED='NO';
INDEX_NAME STATUS PAR
------------------------------ -------- ---
HAOGLOBAL2 VALID NO
SQL> alter table haopart2 exchange partition part04 with table haotmp2
2 including indexes without validation
3 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 size516 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 reads27052 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:
三. 以truncate partition为例，不加update global index时：
1.如果partition里有数据，global index会失效。
SQL> select count(*) from haopart partition(part04);
COUNT(*)
----------
500
SQL> alter table haopart truncate partition part04;
Table truncated.
SQL> select INDEX_NAME,STATUS,PARTITIONED from dba_indexes where
2 TABLE_NAME='HAOPART' and PARTITIONED='NO';
INDEX_NAME STATUS PAR
------------------------------ -------- ---
HAOGLOBAL UNUSABLE NO
2. 如果partition里没有数据，global index不会失效。
SQL> delete from haopart partition(part04);
500 rows deleted.
SQL> commit;
Commit complete.
SQL> alter table haopart truncate partition part04;
Table truncated.
SQL> select INDEX_NAME,STATUS,PARTITIONED from dba_indexes where
2 TABLE_NAME='HAOPART' and PARTITIONED='NO';
INDEX_NAME STATUS PAR
------------------------------ -------- ---
HAOGLOBAL VALID NO
另外，无论走exchange还是truncate，由于Oracle都需要FTS整个partition来判断一下，这里面到底有没有数据。所以，整个过程会持续比较长。
这样就必然对其他查询SQL造成长时间的library cache lock。这点需要注意。
扫一扫
136
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
