Title: RE: Analyzing indexes
Chuck,
Do you think these indexes are corrupt? Validate structure doesn't give you statistics like Compute Statistics or Estimate Statistics does.
Jerry Whittle
ASIFICS DBA
NCI Information Systems Inc.
[EMAIL PROTECTED]
618-622-4145
-Original Message
RE: Analyzing indexesNo but validate structure populates the index_stats
view which is the only way I know of to get the index height, leaf rows,
deleted leaf rows, and pct. of used space which is what I normally use in
determining if an index needs to be rebuilt or not. Is there another way?
Chuck,
I've been convinced that rebuilding indexes is a waste of time.
In fact, it can cost you time, as rebuilding indexes can kill your
peformance while the indexes again seek their 'level'.
Check into at asktom.oracle.com. There's some good examples.
jared
Chuck Hamilton [EMAIL
Chuck
I think Jared has some excellent advice (as usual).
Interestingly, while you may need to lock the table while analyzing the
table, you may be able to rebuild it without locking it. According to the
Oracle Education notes, Oracle8i introduced a method of re-creating and
existing index
If you want the index height (or blevel + 1),
then you could dump the index root block
alter system dump datafile N block MM
Check dba_segments for the address of the
segment header block, unless you have
multiple freelist groups, the index root block
will be the one after the segment header.