analyze index validate structure

[dorothy]

If I issue the below analyze statement and query and there is no rows returned. Is that what the output supposed to look like if the index is fine and it's not fragmented?


SQL> analyze index scott.pk_emp validate structure


Index analyzed.

SQL> select DEL_LF_ROWS*100/decode(LF_ROWS, 0, 1, LF_ROWS) PCT_DELETED,
2 (LF_ROWS-DISTINCT_KEYS)*100/ decode(LF_ROWS,0,1,LF_ROWS)
3 DISTINCTIVENESS
4 from index_stats
5 where NAME='&index_name';
Enter value for index_name: pk_emp
old 5: where NAME='&index_name'
new 5: where NAME='pk_emp'

no rows selected


Am I supposed to get a different output other than NO ROWS SELECTED even if the index is not fragmented?

[sreddy]

Not sure, I guess it gives data only if there should have been some DML on it. You better try on some development/test database where there was some DML recently. Did you try CASCADE option which analyses the child tables also...

[dorothy]

thanks sreddy. I thought I am supposed to see an output even if the index is not fragmented. But not really sure. To me that output looks very funny.

[sreddy]

You are using against scott schema which is part of system tablespace for both data and index. I dunno, I wll try sometime on one of my devt database.

[dorothy]

thanks sreddy