Re: Fast bitwise search

From: Richard Kuhler <noone_at_nowhere.com>
Date: Tue, 01 Jun 2004 23:53:14 GMT
Message-ID: <KF8vc.26919$wO4.18099@twister.socal.rr.com>

Frank van Bortel 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

> 
> Why would Oracle use this internally all over? (Check sql.bsq).
> For one, you can add a new mask, without adding columns to
> the design.

No offense, but if you mimic everything I've seen done by Oracle's own developers then you're going to make many mistakes. They aren't gods. In fact, why not just have one table with one BLOB column, store everything in that and we'd never have to change the data model again? STOP! I'm not suggesting you do that! (although I've seen it done with disastrous results). The reason is that it defeats one of the major purposes of having a data model. Namely, so that data is stored in a well-described manner so a wide variety of people can access it intuitively and easily. This bitmask scenario is extremely counter to that.

Besides, why is adding columns to a table to support new functionality a bad thing? Heaven forbid it might actually work to inform people that it exists.

--
Richard Kuhler

Received on Tue Jun 01 2004 - 18:53:14 CDT