The difference between ALTER INDEX rebuild and ALTER index rebuild online

What are the general conditions for refactoring the index
1. When the index is based on the table of DML operations frequently, over time, indexing efficiency is lower and higher, so you need to rebuild the index
2. When the table is moved to another tablespace, the index to which the table is located becomes invalid and the index needs to be rebuilt

Note:

DML (manipulation Language) data manipulation language, one of the SQL classifications,

DDL (data definition Language) database definition language

DCL (Data Control Language).

DML includes: INSERT, UPDATE, DELETE. Attention

The DQL (Data Query Language) SELECT statement belongs to the DQL

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.