ALTER INDEX indexname REBUILD.

What does an ALTER INDEX indexname REBUILD do ? When would it be used ?


See this test case :

These are my two tables and their indexes :

QL> select table_name, blocks, num_rows,sample_size from user_tables
2  where table_name like 'STORES_LIST%';

TABLE_NAME                         BLOCKS   NUM_ROWS SAMPLE_SIZE
------------------------------ ---------- ---------- -----------
STORES_LIST                          4930     405884      405884
STORES_LIST_2                        4930     405884      405884

SQL> select table_name, index_name, num_rows, leaf_blocks from user_indexes
2  where table_name like 'STORES_LIST%';

TABLE_NAME                     INDEX_NAME                       NUM_ROWS LEAF_BLOCKS
------------------------------ ------------------------------ ---------- -----------
STORES_LIST                    STORES_LIST_CNTRY_STT_NDX          405884        1709
STORES_LIST_2                  STORES_LIST_2_CNTRY_STT_NDX        405884        1709

SQL> 

STORES_LIST is a list of Stores. STORES_LIST_2 is a replica of the table.
The Index that is present is on COUNTRY+STATE.

You can see that both the tables are of exactly the same size.
The Indexes on the two tables are also of the same size.


What happens when I REBUILD the two indexes ?

SQL> alter session set sql_trace=TRUE;

Session altered.

SQL> alter index stores_list_cntry_stt_ndx rebuild;

Index altered.

SQL> alter index stores_list_2_cntry_stt_ndx rebuild;

Index altered.

SQL> 

A tkprof on the trace file shows :

alter index stores_list_cntry_stt_ndx rebuild


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.05          0         10          0           0
Execute      1      2.42       2.72          1       1789       2818           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      2.44       2.77          1       1799       2818           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64



alter index stores_list_2_cntry_stt_ndx rebuild


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.03          0          1          0           0
Execute      1      2.22       2.63          1       4924       2817           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      2.26       2.67          1       4925       2817           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64 

Why did the rebuild of stores_list_2_cntry_stt_ndx have to read so many more blocks (4,924 versus 1,789 in "consistent get" mode)?





..........

The answer is in the fact that there is a difference between the two REBUILDs.





..........


What I hadn't presented was this done just before the REBUILD :

SQL> alter index stores_list_2_cntry_stt_ndx unusable;

Index altered.

SQL> select table_name,index_name, status from user_indexes
where table_name like 'STORES_LIST%';

TABLE_NAME                     INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
STORES_LIST                    STORES_LIST_CNTRY_STT_NDX      VALID
STORES_LIST_2                  STORES_LIST_2_CNTRY_STT_NDX    UNUSABLE

SQL> 

Index STORES_LIST_2_CNTRY_STT_NDX had actually been marked UNUSABLE. A REBUILD of this index was executed by reading the *table* in it's entirety.
On the other hand, a REBUILD of STORES_LIST_CNTRY_STT_NDX was executed by re-reading only the Index. It was still valid and usable.

Thus, I have presented a case where an ALTER INDEX indexname REBUILD may either only read the index (STORES_LIST_CNTRY_STT_NDX) which can be very fast or reads the whole table again (STORES_LIST_2_CNTRL_STT_NDX).

The difference is in the fact that the latter case was working with an Index that had been marked UNUSABLE. An UNUSABLE index is not updated when DML occurs against the table. Therefore, Oracle cannot and does not guarantee that the Index has all the table rows captured with their corresponding Key Values. A Rebuild of such an Index can only be executed by re-reading the Table in it's entirety.
On the other hand a REBUILD of a VALID Index (STORES_LIST_CNTRY_STT_NDX, the index on the first table) can read the index alone (locking the table to prevent rows being changed while the index is being read, unless the "ONLINE" keyword is added to the ALTER INDEX ... command). It is assured that the Index, being VALID, is consistent with the table.


When and why would an Index (or a Partition of a Partitioned Index) be marked UNUSABLE ?
1. If it is LOCAL (Partitioned) Index on a Partitioned Table and DDL (e.g an ALTER TABLE ... PARTITON ... command) is executed against the Table.

2. If it is a GLOBAL (non-Partitioned) Index on a Partitioned Table and DDL (e.g. an ALTER TABLE ... PARTITION ... command) is executed against the Table.

3. If it (whether an Index or an Index Partition) is explicitly set to UNUSABLE -- which may be done a in DataWarehouse prior to loading a large batch of rows, with the proviso the the Index / Index Partition is rebuilt manually or by a script at the end of the data load.

4. If a "DIRECT=true" (i.e. direct path load) SQLLoader sesssion with the option "SKIP_INDEX_MAINTENANCE=true" is executed to insert rows into the table. This may be done when loading a very large batch of rows -- like the previous method, something that may be used in aDataWarehouse.

In all of such cases, the Index or Index Partition is rebuilt with the "ALTER INDEX indexname PARTITION [partitionname] rebuild" which sets the Index / Index Partition status back from UNUSABLE to VALID.
.
.
.