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

A copy of this was sent to gary_at_onegoodidea.com (Gary O'Keefe) (if that email address didn't require changing) On Thu, 29 Jul 1999 09:24:07 GMT, you wrote:

>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.
>

plsql is an interpreted language remember, it is a step above scripting but it *is not a compiled into object code binary language*. I would assume you would have to hate plsql because its not compiled directly into object code (it may be some day but it certainly isn't now).

even if bitor/bitxor was done internally -- it would take the overhead of pushing the function params onto the stack, jumping, popping, assigning, etc, etc, etc. "=" in plsql is a function call. "<" is a function call. AND is a function call -- they all are. Every thing you just listed above is true of any plsql function.

>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.

If i didn't call bitand 2 times -- then you could say "argghhh", your algorithm is botched. I actually spent a bit of time trying many different implementations to discover this to be the fastest I could code.

>this - Voyage to the Bottom of the Performance? What is the point in

No. If you call bitor, bitxor a couple of dozen times

>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.
>

Lets see -- plsql doesn't have bitor. I find a need to do bitor. I write a bitor. Misguided me I guess -- I just gave myself the ability to do something I needed to do (and presumably will have to do one way or another) and previously could not.

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 Corporation Received on Thu Jul 29 1999 - 06:21:01 CDT