Re: How to Find Space Allocated by an Index

For the big picture:

"select \

        dba_segments.tablespace_name,   \
        dba_segments.owner,         \
        segment_type,               \
        segment_name,               \
        sum(extents),               \
        sum(blocks)                 \
    from dba_indexes, dba_segments  \
    where segment_type = 'INDEX'    \
      and table_owner = '%s'        \
      and table_name = '%s'         \
      and dba_indexes.owner = dba_segments.owner    \
      and segment_name = index_name \
    group by dba_segments.tablespace_name,  \
            dba_segments.owner,             \
            segment_type,                   \
            segment_name"

Or for more accuracy (and more overhead):

    VALIDATE INDEX owner.indexname;

"select lf_rows - del_lf_rows, \

        lf_blks + br_blks,          \
        pct_used,                   \
        distinct_keys,              \
        most_repeated_key           \
    from sys.index_stats            \

It's the validate that takes a while, but generates some nice stats as a side effect.

-- 
       ...
      (. .)
___ooO-(_)-Ooo________________________________________________________________
                  |                                     |
Keith Peterson    | The problem with the "School of     | email: kp_at_majure.com
Majure Data, Inc  | Hard Knocks" is that you get the    | Voice (770) 587-1031
993 Mansell Road  | final exam first and afterward      |   Fax (770) 594-9224
Roswell, GA 30076 | learn what the course is all about. |  Info (770) 555-1212
                  |                                     |
__________Oooo._______________________________________________________________
 .oooO    (   )
 (   )     ) /
  \ (     (_/
   \_)