# Bitwise OR Aggregate Function

A few months ago I was working on an application which used bits to store some flags in one number. In this approach the bit value in the number indicates whether a flag is on (1) or off (0). In my case a flag was tied with a column of a front-end table. If the flag was off then the column didn’t appear on the front-end.

Example:

flag : flag_{3}flag_{2}flag_{1}flag_{0}state : off on off on bit value: 0 1 0 1

0101_{2} (binary) = 5_{10} (decadic), so the number 5 will be stored in the database.

In this case, the columns tied with the flags flag_{0} and flag_{2} will appear on the front-end and the columns tied with the flags flag_{1} and flag_{3} won’t. We can use the BITAND function to find out whether the flag is set or not. The flag flag_{i} in the number X is set iff BITAND(X, 2^{i}) = 1.

I had to merge some tables vertically, in that application. The appearance of a column in the merged table depended on the result of the bitwise OR operations. If the result of the operation table_{1}_flag_{i} OR table_{2}_flag_{i} … OR table_{n}_flag_{i} was equal to 1, then the column column_{i} appeared in the merged table. I had to find all columns visible in the merged table.

There is no BITOR function in the Oracle database for the integer types. But it can be easily implemented: BITOR(N_{1}, N_{2}) = N_{1} + N_{2} – BITAND(N_{1}, N_{2}). This is common approach to compute the bitwise OR.

The idea is simple: In exactly one (arbitrary) number, we have to unset those bits, which are set in both numbers and then simply use the addition operation.

Example:

1001 1000 1001 OR 0101 + 0101 + 0100 ---- ---- ---- 1101 1101 1101

We unset the last bit in exactly one number, because it is the only one common bit for both numbers:

BITAND(1001, 0101) = 1

The next step to be done is implementation of bitwise OR aggregate function using user-defined aggregates interface:

CREATE OR REPLACE TYPE bitor_impl AS OBJECT ( bitor NUMBER, STATIC FUNCTION ODCIAggregateInitialize(ctx IN OUT bitor_impl) RETURN NUMBER, MEMBER FUNCTION ODCIAggregateIterate(SELF IN OUT bitor_impl, VALUE IN NUMBER) RETURN NUMBER, MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT bitor_impl, ctx2 IN bitor_impl) RETURN NUMBER, MEMBER FUNCTION ODCIAggregateTerminate(SELF IN OUT bitor_impl, returnvalue OUT NUMBER, flags IN NUMBER) RETURN NUMBER ) / CREATE OR REPLACE TYPE BODY bitor_impl IS STATIC FUNCTION ODCIAggregateInitialize(ctx IN OUT bitor_impl) RETURN NUMBER IS BEGIN ctx := bitor_impl(0); RETURN ODCIConst.Success; END ODCIAggregateInitialize; MEMBER FUNCTION ODCIAggregateIterate(SELF IN OUT bitor_impl, VALUE IN NUMBER) RETURN NUMBER IS BEGIN SELF.bitor := SELF.bitor + VALUE - bitand(SELF.bitor, VALUE); RETURN ODCIConst.Success; END ODCIAggregateIterate; MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT bitor_impl, ctx2 IN bitor_impl) RETURN NUMBER IS BEGIN SELF.bitor := SELF.bitor + ctx2.bitor - bitand(SELF.bitor, ctx2.bitor); RETURN ODCIConst.Success; END ODCIAggregateMerge; MEMBER FUNCTION ODCIAggregateTerminate(SELF IN OUT bitor_impl, returnvalue OUT NUMBER, flags IN NUMBER) RETURN NUMBER IS BEGIN returnvalue := SELF.bitor; RETURN ODCIConst.Success; END ODCIAggregateTerminate; END; /

The last step is definition of the bitwise OR aggregate function. This definition is tied with the object bitor_impl, that implements the aggregate function. I implemented the ODCIAggregateMerge method in this object, therefore I can allow parallel execution by using clause PARALLEL_ENABLE.

CREATE OR REPLACE FUNCTION bitoragg(x IN NUMBER) RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING bitor_impl; /

The aggregate function in action:

SQL> DROP TABLE bitor_test; Table dropped SQL> CREATE TABLE bitor_test(table_name varchar2(31), flags number); Table created SQL> INSERT INTO bitor_test VALUES ('table1', 5); -- 0101 1 row inserted SQL> INSERT INTO bitor_test VALUES ('table2', 1); -- 0001 1 row inserted SQL> INSERT INTO bitor_test VALUES ('table3', 9); -- 1001 1 row inserted SQL> INSERT INTO bitor_test VALUES ('table4', 12); -- 1100 1 row inserted SQL> COMMIT; Commit complete SQL> SELECT bitoragg(flags) FROM bitor_test; BITORAGG(FLAGS) --------------- 13 SQL>

Simple calculation shows us the correctness of this result:

5_{10} OR 1_{10} OR 9_{10} OR 12_{10} = 0101_{2} OR 0001_{2} OR 1001_{2} OR 1100_{2} = 1101_{2} = 13_{10}

thanks a lot. this is exactly what i needed.

Great Article! thank you very much for info!

Yes, 2 years later, this helps me a lot.

Now looking for a bitcount function to count the number of On-bits in a given number.

Thanks

Jef

a few months later and another satisfied blog reader. thank you.

We found this and used it to great success. Thanks much!

This made my day! Thanks a lot!

Right what i needed!

Thanks!!

Not work correctly if any value is null

You need replace

SELF.bitor := SELF.bitor + VALUE – BITAND (SELF.bitor, VALUE);

for

SELF.bitor := SELF.bitor + NVL(VALUE,0) – BITAND (SELF.bitor, NVL(VALUE,0));

This was extremely usefull – allowed to replace 1 big bitor with 30 arguments (each being max(bit) over partition – thus 30 windows) with 1 aggregate.

Exactly what i needed. Thanks!

Toxeh, your implementation is also not correct because NULL OR NULL should be NULL, not 0.

semantically, it should be:

1 OR NULL = 1

0 OR NULL = NULL

NULL OR NULL = NULL

so if you have a NULL value among your values, then all zeros in binary representation of the resulting number should be NULL and therefore the result should be also NULL.

anyway, you can always do bitor(nvl(x, 0))