查看: 4411|回复: 5

[原创] update global indexes的online的程度研究

[复制链接]
论坛徽章:
113
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:512011新春纪念徽章
日期:2011-02-18 11:42:50现任管理团队成员
日期:2011-05-07 01:45:08ITPUB官方微博粉丝徽章
日期:2011-06-28 19:45:36蛋疼蛋
日期:2011-07-24 22:25:332012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-02-13 15:12:25
发表于 2010-7-8 17:43 | 显示全部楼层 |阅读模式
我们知道,如果要在一个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。这点需要注意。



论坛徽章:
86
2015中国数据库技术大会纪念徽章
日期:2015-04-24 16:04:24马上有车
日期:2014-02-19 11:55:14马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11优秀写手
日期:2013-12-18 09:29:11日产
日期:2013-10-17 08:44:39马自达
日期:2013-08-26 16:28:022013年新春福章
日期:2013-02-25 14:51:24ITPUB 11周年纪念徽章
日期:2012-10-23 16:55:51马上有房
日期:2014-02-19 11:55:14
发表于 2010-7-9 08:37 | 显示全部楼层
学习了。

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

使用道具 举报

回复
论坛徽章:
4
数据库板块每日发贴之星
日期:2006-02-25 01:01:35数据库板块每日发贴之星
日期:2006-02-28 01:02:17数据库板块每日发贴之星
日期:2011-04-17 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15
发表于 2010-7-9 10:54 | 显示全部楼层
very good thread...

使用道具 举报

回复
论坛徽章:
2
2014年新春福章
日期:2014-02-18 16:44:08马上有对象
日期:2014-02-18 16:44:08
发表于 2010-12-9 16:50 | 显示全部楼层
学习了

使用道具 举报

回复
认证徽章
论坛徽章:
150
蓝锆石
日期:2011-11-16 22:31:22萤石
日期:2011-11-17 13:05:31祖母绿
日期:2008-06-14 15:23:26海蓝宝石
日期:2011-11-16 22:25:15紫水晶
日期:2011-11-16 22:31:22红宝石
日期:2011-10-09 08:54:30蓝锆石
日期:2009-01-31 15:20:54萤石
日期:2008-12-22 15:22:00祖母绿
日期:2011-11-17 13:13:26海蓝宝石
日期:2008-07-05 14:52:18
发表于 2010-12-9 19:14 | 显示全部楼层

使用道具 举报

回复
认证徽章
论坛徽章:
17
ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:442015年新春福章
日期:2015-03-06 11:57:31暖羊羊
日期:2015-03-04 14:50:37马上有钱
日期:2014-05-19 11:18:35马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:112013年新春福章
日期:2013-02-25 14:51:24奥运会纪念徽章:现代五项
日期:2012-10-16 23:29:11ITPUB 11周年纪念徽章
日期:2012-10-09 18:06:202012新春纪念徽章
日期:2012-01-04 11:50:44
发表于 2011-12-21 22:35 | 显示全部楼层
加上INCLUDING INDEXES  不就搞定了?

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表