want to discuss: how to do a performant bitwise compare in a where clause

From: Bram Stieperaere <bse_at_N_O_S_P_A_Msodexho-pass.be>
Date: Fri, 10 Jul 1998 12:15:31 +0200
Message-ID: <35A5E9C3.6BADD129@N_O_S_P_A_Msodexho-pass.be>

Hi all,

I would like to have opinions on the following problem:

We have a large table (25 M records) with a bitwise coded field. ie we have

( suppose

BIT_1 constant integer := 1;
BIT_2 constant integer := 2;
BIT_3 constant integer := 4;
BIT_4 constant integer := 8;

....
)

to set some bits in the bitfield we use:

update T set bitfield = BIT_x + BIT_y+...

The problem is how to query such a table in a performant way: We consider different approaches:

1. a full table scan:

select from T where bitand(bitfield, mask) > 0;

(where mask = sum of some BIT_... values)

2) make a function that generates all possible bitfield values that match a mask,

    (e.g. mask= 11=8+2+1 =>
     matching list = (1, 2, 8, 1+2, 1+8, 2+8, 1+2+8 ) = (1, 2, 3, 8, 9, 10, 11))

    then use it in a dynamical created SQl stmt:

    select * from T where bitfield in (1, 2, 3, 8, 9, 10, 11);

    which should allow to use an index.

3) (rejected, because too space consuming)

    split the bitfield in separate columns and use a bitmapped composite index on these columns. But this means we have to wast 7 bits per coded bit!

any comments? Received on Fri Jul 10 1998 - 05:15:31 CDT