INDEX_STATS table will need to be viewed after each VALIDATE STRUCTURE command. Cause it is overwriten by the next command. I wrote the script below to check for wasted space on the indexes in my system, and report the indexes which are > 20%, use it if you like. It's a bit untidy, but I didn't spent a lot of time on it, it also has minor dbms debugging still in it.

PROCEDURE validate_indexes

-- Author: Greg Johnson
-- Date: 29/07/2001
-- Purpose: Determine is Indexes have high wasted space
-- and, advise if rebuild may be required.

IS

CURSOR c_validate_index IS
SELECT INDEX_NAME FROM USER_INDEXES
WHERE INDEX_NAME NOT LIKE ('%_LOAD')
AND INDEX_TYPE = 'NORMAL';

vSQL VARCHAR2(100);
v_wasted NUMBER(3);
v_del_lf_rows NUMBER(8);
v_index VARCHAR2(30);

BEGIN

DBMS_OUTPUT.ENABLE(1000000);

FOR r_validate_index IN c_validate_index LOOP

vSQL := 'ANALYZE INDEX ' || r_validate_index.index_name ||' VALIDATE STRUCTURE';

EXECUTE IMMEDIATE VSQL;

vSQL := '';

SELECT NAME
INTO v_index
FROM INDEX_STATS;

-- Oracle recommends that is PCT WASTED > 20%, consider index rebuild

SELECT DEL_LF_ROWS
INTO v_del_lf_rows
FROM INDEX_STATS;

DBMS_OUTPUT.PUT_LINE('Analyzed '||v_index || ' '||v_del_lf_rows);

IF v_del_lf_rows > 0 THEN

SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100
INTO v_wasted
FROM INDEX_STATS;

IF v_wasted > 20 THEN
DBMS_OUTPUT.PUT_LINE(r_validate_index.index_name ||' has '|| v_wasted ||'% wasted SPACE, consider rebuilding');
END IF;

END IF;

v_wasted := 0;

END LOOP;

END validate_indexes;


Cheers,

[Edited by grjohnson on 08-15-2001 at 11:21 PM]