Re:Bitmap Indexes sizes

From: <dgoulet_at_vicr.com>
Date: Thu, 19 Apr 2001 10:05:59 -0700
Message-ID: <F001.002ECA4A.20010419070204@fatcity.com>

Darren,

I would not say it's a problem with bit map indexes, although they have other problems, but indexes in general especially when those indexes are involved in a lot of insert, delete, and update activity. What appears to happen, and although I've a lot of evidence to support the matter I can't get anyone at Oracle to agree, is that as the index grows leaf points migrate from one data block to another via the aforementioned DML, especially updates. Sooner of later a data block is left with no leaf blocks, but pointers to where the leafs went to, and then those pointers eventually move as well leaving the block empty, still in the index, but bypassed. What one watching the index's space usage will see is a gradual increase in extents that rises in almost a logarithmic manner until you run out of space. Then you rebuild the index & wonder where all the extra space came from. Normal result.

Dick Goulet

____________________Reply Separator____________________
Author: "Browett; Darren" <dbrowett_at_city.coquitlam.bc.ca>
Date:       4/18/2001 2:15 PM

I have (had) an index that according to index_stat (after analyze index) and dba_extents that
reported to be approximately 53 Mb and took up 11 extents. The percentage increase is 50.

I dropped the index and recreated it, after I adjusted the initial extents, it turns out the final size
is less then 500k. This seems like a large amount for it to drop.

The index is on a snapshot within our data warehouse.

Has anybody else seen this ?? Is this a problem with bitmap indexes. ??

Thanks

Darren

Darren Browett P.Eng                                    This message was
transmitted
Systems Admin/DBA                                       using 100% recycled

electrons
Information and Communications Technology. City of Coquitlam P:(604) 927 - 3614
E:dbrowett_at_city.coquitlam.bc.ca

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Browett, Darren
INET: dbrowett_at_city.coquitlam.bc.ca

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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: dgoulet_at_vicr.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------