Radino’s blog

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

0101₂ (binary) = 5₁₀ (decadic), so the number 5 will be stored in the database.

In this case, the columns tied with the flags flag₀ and flag₂ will appear on the front-end and the columns tied with the flags flag₁ and flag₃ 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ⁱ) = 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₁_flag_i OR table₂_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₁, N₂) = N₁ + N₂ – BITAND(N₁, N₂). 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₁₀ OR 1₁₀ OR 9₁₀ OR 12₁₀ = 0101₂ OR 0001₂ OR 1001₂ OR 1100₂ = 1101₂ = 13₁₀

Tags: SQL

I’ve already installed the SimpleFickr plug-in for WordPress.

Here is a sample gallery:

UPDATE (31st May 2008):
I’ve uninstalled SimpleFickr plug-in, because I decided to not pay Flickr pro account.

I’ll use NextGen Gallery for my pictures.

Tags: Skiing

So, this is my first post and I hope not the last one . I’m going to share some knowledge and experience here. I’m not going to post regularly, but I hope you will enjoy my posts.

The main topic will be Oracle database – database administration, PL/SQL and SQL snippets of interesting problems. I’m going to write something about hiking, rafting and canyoning, too. I love adrenalin activities and I’ve got some interesting pictures taken, so I’ll probably publish some of them.