dcsimg
September 25, 2020
Hot Topics:

Sample Chapter: Efficient PL/SQL

  • By Beck, Kallman, Katz, Knox, and McDonald
  • Send Email »
  • More Articles »

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




Page 12 of 19



This article was originally published on February 5, 2004

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.


Thanks for your registration, follow us on our social networks to keep up-to-date