Storage of bit flags

[knechod]

From my SQL Server days, I was able to create bit flags. It would pack 8 to a byte. Now, I am looking to do similar things in Oracle, and there is no BIT/Boolean type.

OK, no sweat. I build a number(1) field. But what are the storage and/or performance ramifications of doing that versus using "0"/"1" CHAR(1)?

Kevin

 

[karluk]

I would be astonished if performance doesn't suffer. You have to write your own functions to pack and unpack the bits in order to figure out the field values.

For the purist, it's also a violation of the normalization principle that column values should be atomic. You end up storing multiple items per table column.

Admittedly you do gain a savings in space. I would guess the trade-off only makes sense in limited cases where you have dozens or hundreds of Boolean columns in your table.

 

[knechod]

I guess I muddied the waters, here. I'm not looking to violate atomicity. In SQL Server, it has its own datatype. Physically, the DB did the packing and unpacking.

I'm trying to figure out about space usage when I don't have that option available. If I use NUMBER(1), does it store one byte? 8 bytes? What is the overhead?

If I store CHAR(1), it stores one byte, but what's the overhead?

Does Oracle sense that I am only storing 2 values in the record and store it minimally? (Hah! I wish!)

 

[karluk]

If I am reading the documentation correctly, number(1) requires 2 bytes of storage. The first byte is an exponent and the second a value.

Char(1) should be just a single byte. I don't think there's any additional overhead.

There's no chance that Oracle would pack your data any denser than standard.

 

[Guest_imported]

I'm all for normalization too, but...

Actually PL/SQL does support bit operations if
you take a look at the sys.standard package.
The bitand operation, for example.
I've found there are enough primitives to implement
all the bit operations I've needed (bitor, masking, etc).
Addition, subtraction and power operations may be a little expensive, but they avoid bit-shifting and concerns about
the byte boundary.

I see the question is about comparing the storage of
CHAR(1) and NUMBER(1), but this just isn't the type
of overhead I typically worry about.
I typically use a NUMBER for the bitmaps and masks
assuming I'm using less than log(2,10^20) variables.
Queries actually turn out OK, particularly if I'm looking
for a precalculated value.

***

Bitflags aside,
If you're doing work with large binary data, rather
than bitmapping values of interest, the LONG RAW
and the utl_raw functions work well (for example, sending and receiving raw messages with utl_tcp).

 

[sem]

Try to use RAW datatype. It may be manipulated by UTL_RAW package (some bitwise operators and others).