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

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?

8000 different values: Why do you think this is an appropriate use of a bitmap index?

Why are you on 9.2.0.3 when there are at least 4 patches for that version?

And finally INITIAL of 56K. Should it be 256K?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org