DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, August 12, 2014

How to determine if an index needs to be rebuilt.

We can get an idea of the current state of the index by using the ANALYZE INDEX VALIDATE STRUCTURE command.

The VALIDATE STRUCTURE command can be safely executed without affecting the optimizer.

The VALIDATE STRUCTURE command populates the SYS.INDEX_STATS table only.


SQL> ANALYZE INDEX INDEX1 VALIDATE STRUCTURE;

Statement processed.

SQL> SELECT name, height,lf_rows,lf_blks,del_lf_rows FROM INDEX_STATS;

NAME                    HEIGHT        LF_ROWS         LF_BLKS      DEL_LF_ROW
----------------------   -----------        --------------        -------------      -------------------
INDEX1                      2                     1                        3                    6

1 row selected.

There are two rules of thumb to help determine if the index needs to be rebuilt.

1)     If the index has height greater than four, rebuild the index.

2)     The deleted leaf rows should be less than 20%.

If it is determined that the index needs to be rebuilt, this can easily be done by the following command

ALTER INDEX <INDEX_NAME> REBUILD | REBULID ONLINE command.

It is not recommended to execute this command during normal operating hours.

Creating an index uses the base table as its data source that needs to put a lock on the table. The index is also unavailable during creation.

In this example, the HEIGHT column is clearly showing the value 2. This is not a good candidate for rebuilding.

 

1 comment:

Sridevi K said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.