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" <[EMAIL PROTECTED]>
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:[EMAIL PROTECTED]
----------------------------------------------------------------------------
-------------------------------------------------
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Browett, Darren
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
