ITPub博客

首页 > Linux操作系统 > Linux操作系统 > update global indexes的online的程度研究

update global indexes的online的程度研究

原创 Linux操作系统 作者:monica_yaotf 时间:2011-08-04 10:29:44 0 删除 编辑
原文地址:http://space.itpub.net/15415488/viewspace-667603


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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7353848/viewspace-704040/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-03-27

  • 博文量
    73
  • 访问量
    298150