May 31 2008

Bitwise OR Aggregate Function

Category: Oracle,SQLRadoslav Golian @ 5:46 pm

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     : flag3 flag2 flag1 flag0
state    :   off   on   off    on
bit value:     0    1     0     1

01012 (binary) = 510 (decadic), so the number 5 will be stored in the database.

In this case, the columns tied with the flags flag0 and flag2 will appear on the front-end and the columns tied with the flags flag1 and flag3 won’t. We can use the BITAND function to find out whether the flag is set or not. The flag flagi in the number X is set iff BITAND(X, 2i) = 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 table1_flagi OR table2_flagi … OR tablen_flagi was equal to 1, then the column columni 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(N1, N2) = N1 + N2 – BITAND(N1, N2). 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:

510 OR 110 OR 910 OR 1210 = 01012 OR 00012 OR 10012 OR 11002 = 11012 = 1310

Tags:

11 Responses to “Bitwise OR Aggregate Function”

  1. Holger Bartnick says:

    thanks a lot. this is exactly what i needed.

  2. Andrei Latyshau says:

    Great Article! thank you very much for info!

  3. Jefwork says:

    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

  4. Radino’s blog » Bitcount aggregate function says:

    […] 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. […]

  5. Mark Mitchell says:

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

  6. Scott says:

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

  7. Georg Egger says:

    This made my day! Thanks a lot!

  8. Jorge says:

    Right what i needed!

    Thanks!!

  9. Toxeh says:

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

  10. Anton Pryamostanov says:

    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.

  11. bedorlan says:

    Exactly what i needed. Thanks!

Leave a Reply