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?
Use Analyze index index1 VALIDATE STRUCTURE
COMPUTE STATISTICS doesn't populate index_stats. As pvl suggested, you must use VALIDATE STRUCTURE.
See http://otn.oracle.com/docs/products/...8a.htm#2058795 for details.
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';
FOR r_validate_index IN c_validate_index LOOP
vSQL := 'ANALYZE INDEX ' || r_validate_index.index_name ||' VALIDATE STRUCTURE';
EXECUTE IMMEDIATE VSQL;
vSQL := '';
-- Oracle recommends that is PCT WASTED > 20%, consider index rebuild
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
IF v_wasted > 20 THEN
DBMS_OUTPUT.PUT_LINE(r_validate_index.index_name ||' has '|| v_wasted ||'% wasted SPACE, consider rebuilding');
v_wasted := 0;
[Edited by grjohnson on 08-15-2001 at 11:21 PM]
oh? great! thanks for the valuable feedback...BTW can i
confirm that to qualify an index for rebuild...the percentage of must be
(DEL_LF_ROW_LEN/LF_ROW_LEN)*100 > 20%
(DEL_LF_ROWS/LF_ROWS_LEN) * 100 as suggested by grjohnson???
not that i am doubting his suggestions but I would just wish
to clarify to get the fact right.
This >20% is only a recommendation by Oracle, it obviously depends on your own individual system purpose, another Oracle recommendation is if the indexes blevel >= 4.
no that's not what i mean...the parameter that u used for measurement is it
..if u study it closely..these are diff parameters.
ngwh , yes, sorry about that, I will edit my above mail,
OCP 8i, 9i DBA
how did undo your previous posting on the index_Stats parameter measurement?
...can we undo wat we posted??
Click on the edit/delete link at the bottom of your post.
OCP 8i, 9i DBA
Click Here to Expand Forum to Full Width