Bitmap index or Composite index better on a huge table
816802Jul 15 2011 — edited Jul 15 2011Hi All,
I got a question regarding the Bitmap index and Composite Index.
I got a table which has got only two colums CUSTOMER(group_no NUMBER, order_no NUMBER)
This is a 100Million+ record table and here I got 100K Group_nos and and unique 100Million order numbers. I.E Each group should have 1000 order numbers.
I tested by creating a GLOBAL Bitmap index on this huge table(more than 1.5gb in size) and the GLOBAL Bitmap index that got created is under 50MB and when I query for a group number say SELECT * FROM CUSTOMER WHERE group_no=67677; --> 0.5 seconds to retrive all the 1000 rows. I checked for different groups and it is the same.
Now I dropped the BitMap Index and re-created a Composite index on( group_no and order_no). The index size more than the table size and is around 2GB in size and when I query using the same select statment SELECT * FROM CUSTOMER WHERE group_no=67677; -->0.5 seconds to retrive all the 1000 rows.
My question is which one is BETTER. BTree or BITMAP Index and WHY?
Appreciate your valuable inputs on this one.
Regars,
Madhu K.