oracle中alter index,oracle alter index rebuild online和alter index rebuild的區別

本文用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所在的數據文件。而上面兩個例子就證明了文章開始時的論斷。

===============================================================

=================相關資料metalink note272762.1==================

===============================================================

Problem:

========

- Online Index rebuild takes a long time.

- ONLINE INDEX REBUILD SCANS THE BASE TABLE AND NOT THE INDEX

Symptoms:

=========

Performance issues while rebuilding very large indexes.

- The offline rebuilds of their index is relatively quick -finishes in 15 minutes.

- Issuing index rebuild ONLINE statement => finishes in about an hour.

- This behavior of ONLINE index rebuilds makes it a non-option for large tables

as it just takes too long to scan the table to rebuild the index. The

offline may not be feasible due to due to the 24/7 nature of the database.

- This may be a loss of functionality for such situations.

- If we attempt to simultaneously ONLINE rebuild the same indexes we may encounter

hanging behavior indefinitely (or more than 6 hours).

DIAGNOSTIC ANALYSIS:

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

We can trace the sessions rebuilding the indexes with 10046 level 12.

Comparing the IO reads for the index-rebuild and the index-rebuild-online

reveals the following:

-ONLINE index rebuilds

It scans the base table and it doesn't scan the blocks of the index.

-OFFLINE index rebuilds

It scans the index for the build operation.

- This behaviour is across all versions.

Cause

Cause/Explanation

=============

When you rebuild index online,

- it will do a full tablescan on the base table.

- At the same time it will maintain a journal table for DML data, which has

changed during this index rebuilding operation.

So it should take longer time, specially if you do lots of DML on the same table,

while rebuilding index online.

On the other hand, while rebuilding the index without online option, Oracle will grab

the index in X-mode and rebuild a new index segment by selecting the data from

the old index. So here we are

- not allowing any DML on the table hence there is no journal table involved

- and it is doing an index scan

Hence it will be pretty fast.

Fix

Solution/Conclusion:

===========

- The ONLINE index rebuild reads the base table, and this is by design.

- Rebuilding index ONLINE is pretty slow.

- Rebuilding index offline is very fast, but it prevents any DML on the base table.