When to re-create indexes

Hi Guru's

        I am using the following approach for deciding whether to re-create indexes are not. I appreciate, if some one can tell me some additional tips that help me in this process.

step 1) use the following command on the index

        ANALYZE INDEX <schema>.<index_name> VALIDATE STRUCTURE;

step 2) Immediately after analyzing the index with the above statement, I query the INDEX_STATS table using the following select statement.

	SELECT 	
		LF_ROWS,
		LF_ROWS_LEN, 
		DEL_LF_ROWS, 
		DEL_LF_ROWS_LEN
	FROM 
		INDEX_STATS
	WHERE 
		NAME = '<index_name>'

Step 3) If the DEL_LF_ROWS_LEN/LF_ROWS_LEN > 20%, then I recreate indexes.

        Are there any other things that I should check before re-creating Indexes.

Thanks in advance
Vijay Vardhineni
Oracle DBA, EDS
Plano, TX Received on Sun Sep 08 1996 - 00:00:00 CEST