我們知道，如果要在一個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 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:

三. 以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。這點需要注意。

update global indexes的online的程度研究

相關文章