Best Index type to use when creating index

[agilentfg]

We're trying to determine which type of index (B-tree, Bitmap, etc..) would be better for an attribute that will only to values of either "Y" or "N" on a table that has 3.5 million records and growing. We are estimating an average of 500K with value of "N", the rest "Y".

Thanks in advance for your help on this?

[jmodic]

Well, the B*tree index would be totaly useless with this kind of data distribution. Tho only usable option would be bitmap.

[TheSlob]

BITMAP index definitely.

[pando]

But if your database is OLTP you can forget about BITMAP indexes, would be too slow in DML operations

[agilentfg]

Thank you ALL for your response. BITMAT is it. I will look into the syntax now. Any examples out there?

[kmesser]

What do you hope to gain by indexing? Neither option is likely to produce much benefit if any. BITMAP is a disaster in OLTP.

[chrisrlong]

Unfortunately, it sounds as if you are taking an overly-simplistic view here.

A bitmap index in isolation is a waste.

First, you need to know if you have an OLTP or a DSS database. If OLTP, as others have emphasized - NO BITMAPS!!!!! Period.

Conversely, if you are DSS, then you should probably have LOTS of bitmaps. Bitmaps not only work better on low selectivity columns, they work better in groups.

Here is a thread where jurij and I discuss bitmap indexes in start schemas - this is applicable for most DSS situations in general, however.

http://www.dbasupport.com/forums/sho...threadid=11745

- Chris

[agilentfg]

We have a Data Warehouse that updates once a day with batch processing. There is a cursor that processes the table with 3.5 million records and it is using sequential scan and not the associated binary index because of NULLS in one of the concatenated indexed columns.

Given this, is BITMAP still the best and most efficient type of index we can use?

Thanks!!

[chrisrlong]

Please take the time to read the thread.

- Chris

[pando]

if the distinctveness is low you can use bitmap otherwise b*tree