Sample Chapter: Efficient PL/SQL, Page 12

Binary Operations

To use the available PL/SQL features, first learn which features are available. (If you are thinking this is a polite way of saying: "Go reread those manuals," you're correct.) For example, if we need to perform the logical numeric operations, AND, OR, and XOR, with a little bit of arithmetic, the following PL/SQL function can be built for binary AND. We will not delve too deeply into the specifics of how it works because as we will see imminently, creating such a function is a wasted effort.

SQL> create or replace
  2  function binary_and(x number,y number)return number is
  3    max_bin number(22):=power(2,64);
  4    l_x number :=x;
  5    l_y number :=y;
  6    result number :=0;
  7 begin
  8   for i in reverse 0 ..64 loop
  9     if l_x >=max_bin and l_y >=max_bin then
 10       result :=result +max_bin;
 11     end if;
 12     if l_x >=max_bin then
 13       l_x :=l_x -max_bin;
 14     end if;
 15     if l_y >=max_bin then
 16       l_y :=l_y -max_bin;
 17     end if;
 18     max_bin :=max_bin/2;
 19   end loop;
 20   return result;
 21 end;
 22 /

Function created.

This is a wasted effort because such a function already exists, namely the BITAND function. This is perhaps a special case, and a developer could possibly be excused for building his own version because although the BITAND function existed as far back as version 7 (and probably before), Oracle didn't document its existence until version 8.1.7. Performance-wise, there is no comparison. Comparing 50,000 executions of the home-grown BITAND PL/SQL function to its native counterpart using the SQL*Plus timing facility shows the dramatic difference.

SQL> declare
  2    x number;
  3 begin
  4   for i in 1 ..50000 loop
  5     x:=binary_and(i,i+1);
  6   end loop;
  7 end;
  8 /

PL/SQL procedure successfully completed.

Elapsed:00:00:07.07
SQL> declare
  2    x number;
  3 begin
  4   for i in 1 ..50000 loop
  5     x:=bitand(i,i+1);
  6   end loop;
  7 end;
  8 /

PL/SQL procedure successfully completed.

Elapsed:00:00:00.01