600字范文 > oracle update indexs update global indexes的online的程度研究

oracle update indexs update global indexes的online的程度研究

时间：2018-12-17 17:57:58

我们知道，如果要在一个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(*)

----------

SQL> alter table haopart2 exchange partition part04 with table haotmp2

2including indexes without validation;

Table altered.

SQL> select INDEX_NAME,STATUS,PARTITIONED from dba_indexes where

2TABLE_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(*)

----------

SQL> alter index haoglobal2 rebuild;

Index altered.

SQL> select INDEX_NAME,STATUS,PARTITIONED from dba_indexes where

2TABLE_NAME='HAOPART2' and PARTITIONED='NO';

INDEX_NAME STATUS PAR

------------------------------ -------- ---

HAOGLOBAL2 VALID NO

SQL> alter table haopart2 exchange partition part04 with table haotmp2

2including indexes without validation;

Table altered.

SQL> select INDEX_NAME,STATUS,PARTITIONED from dba_indexes where

2TABLE_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

2TABLE_NAME='HAOPART2' and PARTITIONED='NO';

INDEX_NAME STATUS PAR

------------------------------ -------- ---

HAOGLOBAL2 VALID NO

SQL> alter table haopart2 exchange partition part04 with table haotmp2

2including indexes without validation;

Table altered.

SQL> select INDEX_NAME,STATUS,PARTITIONED from dba_indexes where

2TABLE_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(*)

----------

SQL> select INDEX_NAME,STATUS,PARTITIONED from dba_indexes where

2TABLE_NAME='HAOPART2' and PARTITIONED='NO';

INDEX_NAME STATUS PAR

------------------------------ -------- ---

HAOGLOBAL2 VALID NO

SQL> alter table haopart2 exchange partition part04 with table haotmp2

2including indexes without validation

3update global indexes;

Table altered.

SQL> select INDEX_NAME,STATUS,PARTITIONED from dba_indexes where

2TABLE_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 HAOPARTTABLE 3

10604 HAOTMPTABLE 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;

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 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

----------------------------------------------------------

0recursive calls

0db block gets

2914consistent gets

0physical reads

0redo size516bytes sent via SQL*Net to client

469bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed

Statistics

----------------------------------------------------------

0recursive calls

0db block gets

4095consistent gets

0physical reads27052redo size

516bytes sent via SQL*Net to client

469bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed

Statistics

----------------------------------------------------------

0recursive calls

0db block gets

5130consistent gets

0physical reads

49140redo size

516bytes sent via SQL*Net to client

469bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows 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

2TABLE_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

2TABLE_NAME='HAOPART' and PARTITIONED='NO';

INDEX_NAME STATUS PAR

------------------------------ -------- ---

HAOGLOBAL VALID NO

另外，无论走exchange还是truncate，由于Oracle都需要FTS整个partition来判断一下，这里面到底有没有数据。所以，整个过程会持续比较长。

这样就必然对其他查询SQL造成长时间的library cache lock。这点需要注意。

本内容不代表本网观点和政治立场，如有侵犯你的权益请联系我们处理。

网友评论

网友评论仅供其表达个人看法，并不表明网站立场。