Juz to seek some expertise here...how come when i perform a ANALYZE INDEX compute STATISTIC...then i chk the INDEX_STATS there's no nothing inside...does this mean
the index is ok? or am i missing somethg?
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.
-- Author: Greg Johnson
-- Date: 29/07/2001
-- Purpose: Determine is Indexes have high wasted space
-- and, advise if rebuild may be required.
CURSOR c_validate_index IS
SELECT INDEX_NAME FROM USER_INDEXES
WHERE INDEX_NAME NOT LIKE ('%_LOAD')
AND INDEX_TYPE = 'NORMAL';