Skip to Content
avatar image
Former Member

Difference between ANALYZE INDEX VALIDATE STRUCTURE and DBMS_SPACE

Dear Experts,

Note 771929 suggests various kind of methods to measure index fragmentation.

Among these methods, while ANALYZE INDEX VALIDATE STRUCTURE locks the index table in question, DBMS_SPACE (OBJECT_SPACE_USAGE_TBF) function doesn't lock the index table.

What does the difference mean?

Which is more accurate, therefore reliable way to determine index fragmentation?

In the note, ANALYZE INDEX VALIDATE STRUCTURE collects information for both storage quality and leaf row quality. On the other hand, DBMS_SPACE seems to answer storage quality only. If so, I want to use the former way. But since the former way locks index tables, I would like to use DBMS_SPACE as the next best way.....

Our system is ECC 6.0 and Oracle 10.2.0.2.0.

Many Thanks,

Norifumi OTANI

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Aug 27, 2009 at 10:32 AM

    Dear Lars,

    Thank you for your quick reply.

    I would like to understand your reply, and answer your question, step by step.

    Firstly, could you tell me a little bet more detail about "data management blocks"?

    My assumption is that index-data blocks are used for root, blanch, leaf node etc,. But data management blocks?

    Many Thanks,

    Norifumi OTANI

    Add comment
    10|10000 characters needed characters exceeded

    • > Due to the parameter stats_change_threshold (default: 50%), BRCONNECT only creates new statistics for the table, which more than 50% of data have been changed. Is this threshold mechanism also applicable for the index-table?

      Hi again.

      There is no such thing as "index-tables".

      You're talking about indexes.

      And yes, the 50% threshold applies to indexes as well.

      But this really shouldn't be an issue.

      The logic here is: the statistics would only change when the data has changed.

      So the statistics should be "good enough".

      Anyway: it's really not necessary to constantly monitor all indexes and rebuild them.

      There are only special (pretty well known) indexes that need to be rebuilt due to extreme usage patterns (e.g. RFC tables with right-most inserts and rather random left-side deletes).

      > If it is true, do you force BW to create complete statistics of index-tables, before executing SQL command attached in note 771929?

      For BW you usually won't ever need to rebuild indexes, since the bigger ones are most often dropped and recreated during the load processes.

      regards,

      Lars

  • Aug 27, 2009 at 08:14 AM

    > What does the difference mean?

    It means that DBMS_SPACE can be used safely while the system is up and running.

    This method does not actually touch index-data blocks but just the space management blocks.

    ANALYZE INDEX on the other hand focuses on the B*TREE structure of the index storage and for that the actual index data blocks need to be read and locked for a while.

    > Which is more accurate, therefore reliable way to determine index fragmentation?

    The problem here is that you need to define what precisely you mean by "fragmentation".

    As you already found out yourself, ANALYZE INDEX considers more information and can therefore be more 'reliable'.

    Why don't you just go for the "CBO"-approach described in the note.

    For many cases it works pretty well.

    A general note: you usually don't want to check on each and every index all the time.

    Just focus on those where you have reason to believe that space savings will be worth the effort of checking it.

    Rebuilding indexes just to save a few percent of space can be safely considered a waste of time.

    B*Trees are specifically designed to 'waste' space...

    regards,

    Lars

    Add comment
    10|10000 characters needed characters exceeded