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

"Mistton" <mistonl_at_mail.com> wrote in message news:44e0f330$0$433$bb4e3ad8_at_newscene.com...
> 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?
>

There are three articles on bitmap indexes on this page that may help - I think I explained the sizing thing in one of them.

    http://www.jlcomp.demon.co.uk/ind_misc.html

If you really need this index to exist whilst you are updating data, then you may be able to stabilise its size by recreating it with pctfree set to 67. (So that the copies of big bitmap entries don't keep causing leaf and branch blocks).

Be warned - updates on bitmapped columns can easily result in massive locking contention and deadlocks.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html