RE: Real space

Aldi,

I've always used the following as a guide:

ANALYZE INDEX <schema>.<index_name> VALIDATE STRUCTURE;
SELECT DEL_LF_ROWS/LF_ROWS*100 FROM INDEX_STATS; If the percentage of deleted leaf rows is > 15-20% I rebuild the index.

Keep in mind that INDEX_STATS is a single-row view so it only shows the results of the last index analyzed with validate structure.

HTH,

• Anita
  • "Gogala, Mladen" <MGogala_at_oxhp.com> wrote:
    > I believe that if you execute 'ANALYZE INDEX myindx
    > compute statistics' and
    > then look
    > into the USER_INDEXES table you'll be able to see
    > the requested information.
    >
    > -----Original Message-----
    > Sent: Monday, May 15, 2000 12:06 PM
    > To: Multiple recipients of list ORACLE-L
    >
    >
    > Hi list,
    >
    > I want to know how much space are really allocated
    > for an index (or how much
    >
    > has been wasted space in index segments due to
    > delete statements), to decide
    >
    > which index should be rebuild.
    >
    > Aldi
    > Oracle DBA
    >
    >
    

    ---

    
    > Get Your Private, Free E-mail from MSN Hotmail at
    > http://www.hotmail.com
    >
    > --
    > Author: Aldi Barco
    > INET: ipal_at_hotmail.com
    >
    > Fat City Network Services -- (858) 538-5051 FAX:
    > (858) 538-5051
    > San Diego, California -- Public Internet
    > access / Mailing Lists
    >
    

    ---

    
    > To REMOVE yourself from this mailing list, send an
    > E-Mail message
    > to: ListGuru_at_fatcity.com (note EXACT spelling of
    > 'ListGuru') and in
    > the message BODY, include a line containing: UNSUB
    > ORACLE-L
    > (or the name of mailing list you want to be removed
    > from). You may
    > also send the HELP command for other information
    > (like subscribing).
    > --
    > Author: Gogala, Mladen
    > INET: MGogala_at_oxhp.com
    >
    > Fat City Network Services -- (858) 538-5051 FAX:
    > (858) 538-5051
    > San Diego, California -- Public Internet
    > access / Mailing Lists
    >
    

    ---

    
    > To REMOVE yourself from this mailing list, send an
    > E-Mail message
    > to: ListGuru_at_fatcity.com (note EXACT spelling of
    > 'ListGuru') and in
    > the message BODY, include a line containing: UNSUB
    > ORACLE-L
    > (or the name of mailing list you want to be removed
    > from). You may
    > also send the HELP command for other information
    > (like subscribing).
    

    ---