Re: why does a bitmap index grow so fast but shrinks after recreating it

On 14 Aug 2006 17:05:02 -0500, mistonl_at_mail.com (Mistton) wrote:

>have a bitmap that is growing too fast!
>
>using 9.2.0.3.0 on Sun Unix 15k server
>
>have a table with 13 million rows with following sturcture
>
> ID NUMBER NOT NULL,
> DATESTAMP DATE NOT NULL,
> HOUR NUMBER NOT NULL,
> AAMP NUMBER,
> BAMP NUMBER,
> CAMP NUMBER,
> CAT NUMBER
>
>ID goes from 1 to 8000, datestamp from 5/2003 to present
>
>created a bimap on ID colum
>CREATE BITMAP INDEX DLMP.AAAA ON DLMP.LM_COMP_LOAD_ARCHIVE
>(ID)
>TABLESPACE DATA02
>PCTFREE 2
>INITRANS 2
>MAXTRANS 255
>STORAGE (
> INITIAL 56K
> NEXT 256K
> MINEXTENTS 1
> MAXEXTENTS 2147483645
> PCTINCREASE 0
> FREELISTS 1
> FREELIST GROUPS 1
> BUFFER_POOL DEFAULT
> )
>
>we load 8k rows every hour , 18 hours a day, 365/year
>
>ISSUE:
>
>when I created bitmap its size is 3 megs for all 13 million rows. however
>after adding 150k rows it has grown to 7 megs, after another 150k 11 meg etc.
>4 megs per 150k rows.
>
>If i drop and create again the 11 meg bitmap is now down to 5 meg with the
>orginal rows plus the 450k added ones. if i insert 1 row it does not grow,
>yet if i insert say 8k row it grows to 11 meg the size it had before i dropped
>and recreated it.
>
>is there a solution to this?
>
>is there a reason for such growth? how can it handle the 13 million in 3 megs
>with problems but then grow to such a large size?
>

Bitmap indexes are meant primarily (exclusively?) for datawarehouse kind of databases, in which you load the table on which the index is defined by means of batch load processes. It seems like you are doing just that, so the sensible thing to do is to load the table while the index is dropped or set to unusable, and afterwards putting it on again. If your load is not *much* smaller than your table ifself this could even be faster. If your load *is* much smaller, you should try to partition the table and its indexes in such a way that you can put your load into one (small) partition, whereby you drop the index of the partition and put it on again afterwards.

Jaap. Received on Tue Aug 15 2006 - 13:26:49 CDT