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}

July 24th, 2008 2:44 pm

thanks a lot. this is exactly what i needed.

December 16th, 2008 8:12 pm

Great Article! thank you very much for info!

November 16th, 2010 11:28 pm

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

November 17th, 2010 2:27 am

[…] a blog post after ages . Recently I received a new feedback on my two years old article on bitwise or aggregate function, where a visitor is looking for a bitcount aggregate function. This was a nice exercise for me. […]

April 15th, 2011 6:48 pm

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

August 26th, 2011 4:02 am

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

April 9th, 2012 2:20 pm

This made my day! Thanks a lot!

July 15th, 2012 5:09 pm

Right what i needed!

Thanks!!

November 19th, 2012 2:25 pm

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));

March 11th, 2013 11:44 am

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.

May 9th, 2013 5:50 pm

Exactly what i needed. Thanks!