Re: Oracle #1? Then why are these still missing...

From: Gary O'Keefe <gary_at_onegoodidea.com>
Date: 1999/07/29
Message-ID: <37a015dd.1336350_at_news.hydro.co.uk>#1/1


Paul wrote:

[ earlier stuff about bitand snipped ]

>Thomas Kyte wrote:
>>and you can always write your own bitor and other functions. for example:
>>
>>CREATE OR replace FUNCTION bitor( x IN NUMBER, y IN NUMBER ) RETURN NUMBER
>> AS
>> l_x PLS_INTEGER DEFAULT x;
>> l_y PLS_INTEGER DEFAULT y;
>> l_r PLS_INTEGER DEFAULT 0;
>> l_tmp PLS_INTEGER := 1;
>>BEGIN
>> FOR i IN 1 .. 32 LOOP
>> IF ( bitand(l_x,l_tmp) = l_tmp OR bitand(l_y,l_tmp) = l_tmp )
>> THEN
>> l_r := l_r + l_tmp;
>> END IF;
>> l_tmp := l_tmp * 2;
>> exit when ( l_tmp >= l_x AND l_tmp >= l_y );
>> END LOOP;
>>
>> RETURN l_r;
>>END;
>>/

[ misc stuff snipped ]

>>>14. Binary operator XOR.
>>
>>as easy as bitor
>>
>>SQL> CREATE OR replace FUNCTION bitxor( x IN NUMBER, y IN NUMBER ) RETURN NUMBER
>> 2 AS
>> 3 l_x PLS_INTEGER DEFAULT x;
>> 4 l_y PLS_INTEGER DEFAULT y;
>> 5 l_r PLS_INTEGER DEFAULT 0;
>> 6 l_tmp PLS_INTEGER := 1;
>> 7 BEGIN
>> 8 FOR i IN 1 .. 32 LOOP
>> 9 IF ( bitand(l_x,l_tmp) <> bitand(l_y,l_tmp) )
>> 10 THEN
>> 11 l_r := l_r + l_tmp;
>> 12 END IF;
>> 13 l_tmp := l_tmp * 2;
>> 14 exit when ( l_tmp >= l_x AND l_tmp >= l_y );
>> 15 END LOOP;
>> 16
>> 16 RETURN l_r;
>> 17 END;
>> 18 /
>>
>>Function created.

Not to put you down Thomas, but these are horrible. On any modern processor (i.e. designed after the invention of the transistor) AND, OR, or XOR take 1 clock cycle to execute. Here with OR and XOR you have the overhead of pushing the function params onto the stack, jumping to the function's address space, popping the values off the stack, assigning the memory to 5 variables (one implicitly), assignments, loops, conditional branches, pushing the return value back onto the stack and clearing up after the function has finished. God alone knows how long this will take to execute. You have managed to replace a 1 clock cycle instruction with one that will take >1000, 3 orders of magnitude longer than it would take if implemented natively. This is not an achievement.

Argghhh! I just noticed that you call bitand twice per loop. What is this - Voyage to the Bottom of the Performance? What is the point in buying a dog and barking yourself? The ALU is an integral component of the processor - use it! I appreciate the effort in implementing these algorithms, but it is misguided to attempt these sorts of operations in PL/SQL.

Gary

--
Gary O'Keefe
gary_at_onegoodidea.com

You know the score - my current employer has nothing to do with what I post
Received on Thu Jul 29 1999 - 00:00:00 CEST

Original text of this message