Re: Fast bitwise search

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Wed, 02 Jun 2004 21:09:14 +0200
Message-ID: <c9l8co$l1j$1@news3.tilbu1.nb.home.nl>

Richard Kuhler wrote:

> 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
>

You do need to rewrite every bit of PL/SQL and Java; bitwise operations do not require that.
Apart from that - it seems quite fast.
And one bitand(column) is easier to code than where col1 = 1 and col2 = 'Y' and col3=... ....
and col75...

And I do not mimic Oracle code expert (wish I had the knowledge), but have not heard any technical reasons why I shouldn't use bitand. Datamodels are not the same as technical implementations.

-- 

Regards,
Frank van Bortel

Received on Wed Jun 02 2004 - 14:09:14 CDT