How to store Yes No questions on table and BITAND function

Hi everyone,

you might see BITAND function is frequently used by Oracle on source codes. Did you wonder why? What does BITAND function do? as you can understand from the name, it does a bitwise AND operation on parameters. probably you know what a bitwise and but as a small explanation it is an AND operator for every bit. since computer systems use binary model, everything is represented as ones and zeros. if you want to write number of 5 in 4 bit it is represented as this:

0 1 0 1

every digit is a power of 2 and starting by right most digit, 2^0  2^1 2^2 and so on. so

0 1 0 1 = (2^3)*0 + (2^2)*1 + (2^1)*0 + (2^0) *1 = 5

a “bitwise and” compares 2 set of bits and produce a result:

0 1 0 1
AND
1 1 0 0
————±
0 1 0 0 = 4

so if both digits are 1 result is 1 and if not result is 0. that’s what AND operator does. if it was and OR operator then if any of the binary bits is a 1 then result would be 1.

so this is not a mathematical sum operation as we know and result of it seems not much useful but actually it can help you a lot!

Let’s say you want to store some yes no questions (more than one) on a table. as the simplest solution you can create many columns X_YN, N_YN, Z_YN etc but instead you can store this information on bits and you can store those bits in one column.

Let’s say you have an “Order” table and you are storing information about the orders. some of them are (let’s say), DANGEROUS_GOODS_YN (if you are carrying something dangerous), INSURANCE_YN (if the order is insured), ARRIVED_YN (if order is complete), VOYAGE_YN (if the goods are moved by a ship) etc. you can increase the number of those YN questions of course. now I have 4 columns because I need to store 4 information but instead of that I can add just 1 column and use the power of “bits”.

Since those are all YN questions which means only have 2 different possible value, I can mark them as 1s and 0s. 1 is YES and 0 is NO

DANGEROUS_GOODS_YN  INSURANCE_YN ARRIVED_YN  VOYAGE_YN
1                                              0                          1                       1

so I combine those “bits” result is 1011 which is 11 in decimal. I will create a column (one column only) as “order_info” and make it a number column and then store 11 for the order above. so 11 means, I am carrying dangerous goods, not insured, arrived and moved by a ship. of course I won’t memorize what 11, 7 or 5 is. I will just generate bits and check them in the specific order.

Now let’s say I want to generate a report which is called “The Most Risky Orders”. This report will fetch the orders for dangerous goods, not insured, not arrived and carried by a ship. if we generate our bits by looking for our request we are searching for: 1 0 0 1 which is 9. you might look for “order_info=9” but remember bitwise operations are much more faster so we can use BITAND function:

Bitand(Order_Info, 9) = 9 is our condition. Bitand will do a bitwise and operation on it’s two parameter and return the result as decimal. if an order has specifications of what we are looking for (we are looking for 9 (1 0 0 1) ) and if the result is also 9 which means they are identical then this is a row what we are looking for.

By using this you can also compare just one properties of the column. for example if I want to get all orders with dangerous goods I need to find orders with left most bit is 1. since I have 4 YN information (property) of the order and I assumed that left most digit is dangerous goods, I am simply looking for this:

Bitand(Order_Info, 8) = 8

now, order might have other properties like arrived or insured but the condition above always return true if dangerous goods bit is 1. let’s say one of the order has 10 as order info, it is represented as :

1 0 1 0 so this is a dangerous goods and arrived order. Lets check our condition above.

1 0 1 0  (10 is order’s order_info)
1 0 0 0  (8 what we looking for, dangerous goods)
————±
1 0 0 0 = 8 so this order will be return from the query.

so bitand of 10 and 8 is 8 which means this is an order with dangerous goods. basically we are just putting ones to bits that we want to find as YES value and do a “bitwise and” operation. result must be what we are looking for because other bits are zeros on our request. Our search criteria can be more complex like:

Not Dangerous good, insured, not arrived, voyage shipment
0                                     1              0                   1

so we are looking for orders with order_info data “0101”, we will bitand order info with that and result must be what we looking for 0101

of course there might be more than 4 questions/properties. you don’t have to calculate bit’s decimal values. Oracle has functions for that:

Bitand(Order_info, bin_to_num(1,0,1,0)) = bin_to_num(1,0,1,0) (dangerous, not insured, arrived, not voyage)

this is also more readable for us. that way you will be able to store more than one data in a single column and query it.