Re: Fast bitwise search

From: Richard Kuhler <noone_at_nowhere.com>
Date: Wed, 02 Jun 2004 14:38:44 GMT
Message-ID: <UDlvc.7376$Ha2.3697@twister.socal.rr.com>

Connor McDonald wrote:

> Richard Kuhler wrote:
> 

>>kuntz.1507173_at_bloglines.com wrote:
>>
>>>we have a large table that contains a bitmask field:
>>>MASK NUMBER(19)
>>>
>>>The application then performs a query that includes BITAND(MASK, ?) > 0
>>>condition.
>>>
>>>Is there any way to use an index for this condition. I was thinking
>>>about bitmap indexes for this but could not figure out how to use them
>>>here.
>>
>>Why in the world would you bundle data up into a column like that? I
>>can't imagine why the correct solution isn't to break those bits out
>>into individual columns with descriptive names so people can actually
>>see what information is stored there. Then you could use bitmap indexes
>>on them if performance dictated it (keeping in mind the problems they
>>create).
>>
>>--
>>Richard Kuhler

> 
> 
> Lets says its 20 attributes, each being boolean.  A b-tree index isn't
> going to help since the cardinality is so low.  

That's an over simplification. Especially with a bitmask where you are looking for rows with bits set. What if only 1 row in a 100 million row table has the attribute value you are looking for? Even if you forget about histograms, you can use a null-or-set design to create an index that will just have the rows that have the attribute set.

> So you might indeed be up for a full scan no matter what, at which > point, a bit mask in a single column may indeed be the best way to go

Sure, but should we design a data model purely so it gives the best performance possible? When does usability come into the equation?

--
Richard Kuhler

Received on Wed Jun 02 2004 - 09:38:44 CDT