Re: Fast bitwise search

kuntz.1507173_at_bloglines.com wrote:
> This is a legacy application and the DB was designed some time ago. I
> guess this looked like a simplier alternative to having 40+ columns on
> 50 or so tables.

We've all been there before. I'm curious though, does it seem simpler to you?

> If I understood you correctly, you said that it is not possible to use
> indexes in this design?

Well, just a basic b-tree index would probably help considerably if the table is fairly wide (many columns/large columns), that's the only criteria used in the query and very few rows will satisfy it. I'm thinking that Oracle should go with a fast full (multi-block) scan on that index to save some I/O. Have you tried that?

I don't see how bitmap indexes can be of any use here though. If you knew every possible mask that might be used, I guess you could build a function-based index on each of those, but it's probably a very, very bad idea to actually do that.

--
Richard Kuhler