Re: Shrinking an index??

Hey,

   How about using a BITMAP index found in 7.3.2.3? It has saved me great amounts of disk space. A number of my indexes were over 400 Meg in size before changing to BITMAP and are now around 69 Meg each.

   BITMAPs are useful for 'low cardinality' indexes (ie. those with low number of distinct values compared to the total number of rows in the table). To find out about the cardinality do an ANALYZE on the index then check the DISTINCT_KEYS column in DBA_INDEXES. It is well worth the effort of upgrading to 7.3.2.3! -Doug Anderson
 DAtheDBA_at_mindspring.com

zlm101_at_psu.edu (Z. Martinez) wrote:
>I have a large table, a very large table, about one million rows.
>The table definition goes something like this.
>id number(10)
>type number(3)
>desc char(40)
>There are several select statements that are going query on this
>table, but only needs types 5 and 9, i.e type = 5 or type = 9.
>This is probably just 10% of the entire table or 100,000 rows.
>There is currently an index that keys on the "type" column.
>It certainly looks like a waste of storage and processing, cause the
>remaining 900,000 entries are not really needed.
>Is there a way to create an index such that only a subset of the table
>is indexed? I would like to shrink the index to save space and
>hopefully increase the performance of the queries.
>Please respond to zlm101_at_psu.edu

• Doug Anderson Oracle Database Administrator DAtheDBA_at_mindspring.com