The difference between ALTER INDEX rebuild and ALTER index rebuild online

This paper uses 10046 events to analyze the difference between ALTER INDEX rebuild and ALTER index rebuild online

Alter index rebuild online is essentially a scan table rather than scanning an existing index block to achieve an indexed rebuild

Alter INDEX rebuild only scans the existing index blocks to implement the rebuild of the index.

We can use 10046 events to discover this phenomenon.

First look at ALTER INDEX rebuild:

Sql> Conn
Please enter user name: WWF/WWF
is connected.
sql> drop table wwftest;

The table has been discarded.

Sql> CREATE TABLE Wwftest as SELECT * from All_objects where rownum < 20000;

Table has been created.

Sql> CREATE index ind_wwftest on wwftest (object_id) tablespace idx_ts;

The index has been created.

Sql> alter session SET events ' 10046 Trace name Context forever, Level 12 ';

The session has changed.

sql> ALTER index ind_wwftest rebuild;

The index has changed.

Sql> alter session SET events ' 10046 Trace name context off ';

The session has changed.

Check the export file, where we can find:

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

File 12 is exactly the file number that corresponds to the index table space.

Two we'll see ALTER index rebuild online

Sql> Conn
Please enter user name: WWF/WWF

is connected.

sql> DROP Index ind_wwftest;

The index has been discarded.

Sql> CREATE index ind_wwftest on wwftest (object_id) tablespace idx_ts;

The index has been created.

Sql> alter session SET events ' 10046 Trace name Context forever, Level 12 ';

The session has changed.

sql> ALTER index ind_wwftest rebuild online;

The index has changed.

Sql> alter session SET events ' 10046 Trace name context off ';

The session has changed.

Let's look at the export file:

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

In this case, file 11 is the data file that contains the table wwftest. The above two examples prove the thesis at the beginning of the article. Category: (Oracle Management):: Comments (4):: Static link URL:: Reference (0)

Metalink note:272762.1 explanation on the difference between the two [reply]

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 minutes.
-Issuing index rebuild ONLINE statement => finishes in about a 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 is feasible due to due to the "24/7 nature of the" database.
-This May is 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 are across all versions.

Cause
Cause/explanation
=============
When you rebuild index online,
-It'll do a full tablescan on the base table.
-At the same time it'll maintain a journal table for DML data, which has
Changed during this index rebuilding operation.
So it should take longer time, specially if you did lots of DML on the same table,
While rebuilding index online.

On the other hand, while rebuilding the index without online option, Oracle would grab
The index in X-mode and rebuild a new index segment by selecting the data from
The old index. So we are
-not allowing any DML on the table hence there is no journal table involved
-and it is doing a index scan
Hence It'll be pretty fast.
Fix
Solution/conclusion:
===========
-the ONLINE index rebuild reads the base table, and this are by design.
-Rebuilding index ONLINE is pretty slow.
-Rebuilding index offline is very fast, but it prevents any DML on the base table. www.wwf.co | 25/08/2005, 22:23

Reply

Praise the Old Monk | 26/08/2005, 10:03

Thank the old monk for his encouragement. Reply

Thank you. www.wwf.co | 26/08/2005, 10:29

When index is in unusable state, rebuild offline scans the entire table. Reply

note:278600.1 when does Offline Index Rebuild refer to Base Table?

Goal
An index rebuilt either Online or Offline.

Online Index Rebuild Features:
+ ALTER INDEX REBUILD ONLINE;
+ dmls are allowed on the base table
+ It is comparatively slow
+ Base table is referred for the new index
+ Base table is locked in shared mode and DDLs are not possible
+ Intermediate table stores the data changes in the base table, during the index rebuild to update the new index later

Offline Index Rebuild Features:
+ ALTER INDEX REBUILD; (Default)
+ does not refer the base table and the base table is exclusively locked
+ New index is created from the old index
+ No DML and DDL possible on the base table
+ Comparatively faster

So, the base table isn't referred for data when the index is rebuilt offline.
This article describes the this behavior with the test cases and depicts a few scenarios when it is violated.

Fix
The test cases considers Btree index being rebuilt online/offline. The results are also same for Bitmap index. For analysis 10046 Trace is generated to the if the statement refers the base table for data access. Trace Analyzer is also used to get a clear picture (note:224270.1).

Base TABLE:T5
Index Name:ind5

Test-1:
=========
Index is rebuilt OFFLINE

Alter session SET Events ' 10046 Trace name Context forever, Level 12 ';
Alter index IND5 rebuild;
Alter session SET Events ' 10046 Trace name context off ';

The Trace Analyzer output has the following wait details:
+ There is no blocks access from T5
+ IND5 blocks are accessed

Test-2:
=========
Index is rebuilt ONLINE:

Alter session SET Events ' 10046 Trace name Context forever, Level 12 ';
Alter index IND5 rebuild online;
Alter session SET Events ' 10046 Trace name context off ';

The Trace Analyzer output has the following wait details:
+ T5 blocks are accessed

Conclusion
==========
When a index is rebuilt offline there is no FTS on the base table. When the ' index is ' rebuilt online all of the blocks from the base table are accessed.

These conclusions are if we donot make a scenario when the index is unusable and then there
Is data load to the base table, and finally the index is rebuilt. Lets the results from different scenarios when the index is unusable.

Test-3
=========
The Index is made unusable. Nodata is load to the base table. The Index is rebuilt.

sql> ALTER INDEX Ind5 unusable;
Index altered.

Sql> Select Index_name,status from user_indexes where index_name = ' IND5 ';
Index_name STATUS
------------------------------ --------
IND5 unusable

Alter session SET Events ' 10046 Trace name Context forever, Level 12 ';
ALTER INDEX Ind5 REBUILD;
Alter session SET Events ' 10046 Trace name context off ';

Sql> Select Index_name,status from user_indexes where index_name = ' IND5 ';

Index_name STATUS
------------------------------ --------
IND5 VALID

The Trace Analyzer output has the following wait details:
+ There is no blocks access from T5
+ IND5 blocks are accessed

Test-4:
=========
The Index is made unusable. The Data is load to the base table. Then Index is rebuilt OFFLINE.

sql> ALTER INDEX Ind5 unusable;
Index altered.

Sql> INSERT into T5 values (55555, ' eeeee ');
INSERT into T5 values (55555, ' eeeee ')
*
ERROR at line 1:
Ora-01502:index ' BH. IND5 ' or partition of such index is in unusable state

Test-5:
=========
Now lets do some dataload using SQLLDR.

Index made UNUSABLE/SQLLDR dataload to Table/rebuild Index

Load data
InFile *
Append
into Table T5 (
A position (1:5),
b position (6:10))

Begindata
55555EEEEE
44444DDDDD
66666FFFFF

Sqlldr userid=bh/sh control=test.ctl log=test.log Bad=test.bad Discard=test.discard

(Skip_unusable_indexes = false--DEFAULT)

All of the three rows are listed in Test.bad. NO DATA LOADED

Test-6:
=========
Index made UNUSABLE/SQLLDR dataload to table with Skip_unusable_indexes=true/rebuild index

Sql> Select COUNT (*) from T5;
COUNT (*)
----------
0

Sql> CREATE INDEX Ind5 on T5 (b) Storage (initial 1K next 1K Maxextents unlimited pctincrease 0);
Index created.

Sql> Select Block_id,blocks from dba_extents where segment_name = ' T5 ';
block_id BLOCKS
---------- ----------
5897 130

Sql> Select Block_id,blocks from dba_extents where segment_name = ' IND5 ';
block_id BLOCKS
---------- ----------
4682 2

sql> alter index IND5 unusable;
Index altered.

Sqlldr userid=bh/sh control=test.ctl log=test.log Bad=test.bad Discard=test.discard

Sql> Select COUNT (*) from T5;
COUNT (*)
----------
154400

DATA GETS LOADED to table. Data doesnot go to index.

Sql> Select Block_id,blocks from dba_extents where segment_name = ' T5 ';
block_id BLOCKS
---------- ----------
5897 130
4684 130
4814 195

Sql> Select Block_id,blocks from dba_extents where segment_name = ' IND5 ';
block_id BLOCKS
--------- ----------
4682 2

No new blocks gets added to index.

Sql> Select Status,index_type from user_indexes where index_name = ' IND5 ';
STATUS
--------
Unusable

Alter session SET Events ' 10046 Trace name Context forever, Level 12 ';
ALTER INDEX Ind5 REBUILD;
Alter session SET Events ' 10046 Trace name context off ';

Sql> Select COUNT (*) from dba_extents where segment_name = ' IND5 ';
COUNT (*)
----------
9

Sql> Select Status,index_type from user_indexes where index_name = ' IND5 ';
STATUS
--------
VALID

From Trace Analyzer o/p, there are trace that T5 has been referred.
+

Conclusion
==========
As documentation says, while rebuilding a index OFFLINE, the base table is not referred. But There are situations where the base table is accessed similar to a index create, they are:

+ index is made "unusable"
+ data is loaded with SQL loader with skip_unusable_indexes = TRUE
+ Index is REBUILD OFFLINE

Documentation is isn't in agreement the particular case.

There could is one more scenarion I guess:
+ Move a table to a different tablespace
+ Index becomes unusable
+ We rebuild the index

Basically, when a index is made "unusable", it are normally meant that it'll not being used at all (which is drop later) Or it has to be drop/create. Here's the index being unusable takes more priority than the Offline of it. An unusable index has to refer the base table while rebuilding. www.wwf.co | 26/08/2005, 16:28