alter index rebuild与alter index rebuild online的区别

一般在什么情况下重构索引
1.当索引所基于表上的DML操作频繁,随着时间推移,索引效率就越来越低,所以就需要重建索引
2.当表被移动另一个表空间时,此表所在的索引会变得无效,也需要重建索引 

 

备注：

DML（Data Manipulation Language）数据操纵语言，SQL的分类之一，

DDL（Data Definition Language）数据定义语言

DCL（Data Control Language）数据控制语言。

DML包括：INSERT、UPDATE、DELETE。注意，

DQL(Data Query Language）select语句属于DQL

 

 

本文用10046事件来解析alter index rebuild与alter index rebuild online的区别

alter index rebuild online实质上是扫描表而不是扫描现有的索引块来实现索引的重建

alter index rebuild 只扫描现有的索引块来实现索引的重建。

我们可以用10046事件来发现这个现象。

一 先看alter index rebuild:

SQL> conn
请输入用户名: wwf/wwf
已连接。
SQL> drop table wwftest;

表已丢弃。

SQL> create table wwftest as select * from all_objects where rownum < 20000;

表已创建。

SQL> create index ind_wwftest on wwftest(object_id) tablespace idx_ts;

索引已创建。

SQL> alter session set events '10046 trace name context forever, level 12';

会话已更改。

SQL> alter index ind_wwftest rebuild;

索引已更改。

SQL> alter session set events '10046 trace name context off';

会话已更改。

检查导出文件，我们可以在其中发现：

WAIT #1: nam='db file scattered read' ela= 41105 p1=12 p2=11 p3=6
WAIT #1: nam='db file scattered read' ela= 1110 p1=12 p2=17 p3=8
WAIT #1: nam='db file scattered read' ela= 1117 p1=12 p2=25 p3=8
WAIT #1: nam='db file scattered read' ela= 959 p1=12 p2=33 p3=8
WAIT #1: nam='db file scattered read' ela= 955 p1=12 p2=41 p3=8
WAIT #1: nam='db file scattered read' ela= 749 p1=12 p2=49 p3=6

文件12恰好是索引表空间对应的文件编号。

二 我们再看alter index rebuild online

SQL> conn
请输入用户名: wwf/wwf

已连接。

SQL> drop index ind_wwftest;

索引已丢弃。

SQL> create index ind_wwftest on wwftest(object_id) tablespace idx_ts;

索引已创建。

SQL> alter session set events '10046 trace name context forever, level 12';

会话已更改。

SQL> alter index ind_wwftest rebuild online;

索引已更改。

SQL> alter session set events '10046 trace name context off';

会话已更改。

我们看导出文件：

WAIT #1: nam='db file scattered read' ela= 23773 p1=11 p2=34 p3=7
WAIT #1: nam='db file scattered read' ela= 2279 p1=11 p2=41 p3=8
WAIT #1: nam='db file scattered read' ela= 3468 p1=11 p2=49 p3=8
WAIT #1: nam='db file scattered read' ela= 2227 p1=11 p2=57 p3=8
WAIT #1: nam='db file scattered read' ela= 2171 p1=11 p2=65 p3=8
WAIT #1: nam='db file scattered read' ela= 1954 p1=11 p2=73 p3=8
WAIT #1: nam='db file scattered read' ela= 3492 p1=11 p2=81 p3=8
WAIT #1: nam='db file scattered read' ela= 1687 p1=11 p2=89 p3=8
WAIT #1: nam='db file scattered read' ela= 1953 p1=11 p2=97 p3=8
WAIT #1: nam='db file scattered read' ela= 1937 p1=11 p2=105 p3=8
WAIT #1: nam='db file scattered read' ela= 991 p1=11 p2=113 p3=8
WAIT #1: nam='db file scattered read' ela= 2303 p1=11 p2=121 p3=8
WAIT #1: nam='db file scattered read' ela= 1926 p1=11 p2=129 p3=8
WAIT #1: nam='db file scattered read' ela= 1724 p1=11 p2=137 p3=8
WAIT #1: nam='db file scattered read' ela= 1878 p1=11 p2=145 p3=8
WAIT #1: nam='db file scattered read' ela= 3437 p1=11 p2=153 p3=8
WAIT #1: nam='db file scattered read' ela= 81546 p1=11 p2=265 p3=16
WAIT #1: nam='db file scattered read' ela= 4857 p1=11 p2=280 p3=16
WAIT #1: nam='db file scattered read' ela= 3432 p1=11 p2=296 p3=16
WAIT #1: nam='db file scattered read' ela= 3511 p1=11 p2=312 p3=16
WAIT #1: nam='db file scattered read' ela= 2685 p1=11 p2=328 p3=16
WAIT #1: nam='db file scattered read' ela= 4356 p1=11 p2=344 p3=16
WAIT #1: nam='db file scattered read' ela= 2356 p1=11 p2=360 p3=16
WAIT #1: nam='db file scattered read' ela= 3396 p1=11 p2=376 p3=16
WAIT #1: nam='db file sequential read' ela= 347 p1=11 p2=392 p3=1
WAIT #1: nam='db file scattered read' ela= 1924 p1=11 p2=393 p3=16
WAIT #1: nam='db file scattered read' ela= 1051 p1=11 p2=408 p3=8

在本例中，文件11是表wwftest所在的数据文件。而上面两个例子就证明了文章开始时的论断。