Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Fast bitwise search

Re: Fast bitwise search

From: Frank van Bortel <>
Date: Wed, 02 Jun 2004 21:09:14 +0200
Message-ID: <c9l8co$l1j$>

Richard Kuhler wrote:

> Frank van Bortel wrote:

>> Richard Kuhler wrote:
>>> 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.


Frank van Bortel
Received on Wed Jun 02 2004 - 14:09:14 CDT

Original text of this message