Re: How to store bit info

music4 wrote:
> Greetings,
>
> In my application, I have an unsigned int (32 bit) number to store 32
> switches. If a bit is 0, then the switch is off, 1 for on. Now I want to
> store this number into Oracle 8i database, I am considering which data type
> is best for that requriement.
>
> Any idea will be greatly appriciated.
>
> Evan
>
>

Depends how you plan to extract data. If it's only ever going to be used in queries as just a number, then you can store it as just a number. If you want to use the flags in WHERE clauses and fetch back data according to the flags, then you could use bitwise functions as Mark suggested, but functions kill indices, so it could be better to use (up to) 32 "boolean" columns instead, thus giving you the ability to efficiently query, for example, WHERE SOMEFLAG=1 AND ANOTHERFLAG=0.

It also depends how much space you want to use. 4,294,967,295 (maximum value for a 32 bit unsigned integer) fits into NUMBER(10), or CHAR(8) as hex, or RAW(4) as binary. The above WHERE clause can be done with a NUMBER(1), but that requires up to 32 NUMBER(1) columns plus extra code to split and recombine the flags on INSERT/UPDATE and SELECT.

Ultimately it depends on your definition of "best." There is no single best way of doing anything. Each of the above has pros and cons - CHAR(8) allows you to "see" flag values in a simple SELECT but wastes 4 bytes, although is less wasteful than NUMBER(10). RAW(4) is probably the most compressed form of the data but is harder to use. 32 NUMBER(1)s gives you the ability to query according to flags but wastes 7 bits per byte and requires additional code to split and recombine the values. Received on Thu Apr 07 2005 - 09:20:43 CDT