update global indexes的online的程度研究

viadeazhu

我们知道，如果要在一个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。这点需要注意。

sundog315

学习了。

exchange的时候，如果partiton和临时表都没有数据，问什么global index 还会失效？
为什么判断分区是否有数据需要走FTS，理论上，如果有索引的话，可以通过索引来判断。不明白。

it_caribe

very good thread...

szoraclefans

学习了

warehouse

sylvanzzy

加上INCLUDING INDEXES  不就搞定了？