Re: Oracle #1? Then why are these still missing...
Date: 1999/07/29
Message-ID: <37a0366f.87039736_at_newshost.us.oracle.com>
>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
gee, i found them to be useful -- and fast enough.
>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
lets see you do it *without* calling bitand 2 times -- its sort of important (it won't work unless you do). Also, in the first one (bitor) bitand is only called 2 times if the first call and compare is FALSE.
>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.
>
I could take the above code and implement it in java, I could take it an implement it in C. I found the above to be
- the most portable (everyone with 7.1 and up can use it)
- the easiest to understand and install (this is pretty important to me as I'd get thousands of emails with questions and installation issues for anything more complex)
- works *fast enough* for most things.
True -- its not as fast as C (i can say that in general about plsql) but it works, it does the job, and it is only one of at least 3 ways I know how to do this, each with various performance and implementation implications.
>Gary
-- See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA Opinions are mine and do not necessarily reflect those of Oracle CorporationReceived on Thu Jul 29 1999 - 00:00:00 CEST