Re: Fast bitwise search

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. Bitmap indexes pretty much mean that the table has to be read-only. Moving the attributes to rows might not work so well since may end up with a massive lot of joins to the attribute table to handle the various AND/OR permutations.

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

Cheers
Connor

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"

------------------------------------------------------------