Analyze Indexes

ANALYZE INDEX PA.PA_TASKS_N1 COMPUTE STATISTICS;
ANALYZE INDEX PA.PA_TASKS_N1 VALIDATE STRUCTURE;
SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)*100 as ratio FROM INDEX_STATS;

Knowing when to Rebuild Indexes

We must first 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. The SYS.INDEX_STATS table can be accessed with the public synonym INDEX_STATS. The INDEX_STATS table will only hold validation information for one index at a time. You will need to query this table before validating the structure of the next index.

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)     if the ratio of del_lf_row/lf_row*100 is greater than 20% then rebuild the index

Indexes on Tables

Find all Indexes on a Table

SELECT index_name, column_name, column_position
FROM dba_ind_columns
WHERE table_name like upper('&table_name')
ORDER BY index_name, column_position;