update global indexes的online的程度研究
我们知道，如果要在一个table partition上做DDL时，是可能会invalidateglobalindex的。
但是，如果我们加上updateglobalindexes，或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 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)
truncated 带where1 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都需要FT
S整个partition来判断一下，这里面到底有没有数据。所以，整个过程会持续比较长。
这样就必然对其他查询SQL造成长时间的library cache lock。这点需要注意。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论