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

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? Received on Mon Aug 14 2006 - 17:05:02 CDT