Well, without knowing how Oracle implemented their PL/SQL engine, and without empirical data (which truthfully I have not bothered to collect) this is a hard question to answer. However, with my little knowledge of compiler design, I'll give you my guess as something to think about and you can run with it.
PL/SQL is basically an interpreted language, which means it doesn't get that directly close to the assembly/machine code like a traditional compiled language does. For this reason, I don't think Oracle is bothering to optimize the PL/SQL engine that heavily into mapping variables onto registers; I'll assume that it's all basically allocated from Oracle's heap that it gets from the OS. Since you are contemplating a procedure versus a function where all things being equal, a resonable assumption would be that your procedure must have at least 1 OUT parameter that would take the place of the returned value from the function. Now here, it depends largely on how Oracle has implemented their PL/SQL parameter passing and management of PL/SQL stack. For a function, typically in other languages the return value is computed and stored on the stack or register, and after the function terminates, the return value is copied over to the variable that is on the left-hand-side of the assignment operator. For a procedure with a pass-by-reference variable, the original variable is accessed (since it's pass-by-reference) so there would be no need for a final copy operation. In this scenario, a procedure would be faster depending on the size of the return data (which dictates how much data would need to be copied.) But that is likely to be true ONLY in post 8i databases when the pass-by-reference parameter is marked as NOCOPY; if the pass-by-reference parameter is not NOCOPY, Oracle's parameter passing scheme would be copy-in and copy-out even for OUT parameters. -----Original Message----- Sent: Tuesday, July 08, 2003 6:04 AM To: Multiple recipients of list ORACLE-L Hi All, If I create a Database Procedure and a Database Function to achieve the same functionality, which one would be faster and why? TIA Regards Dhanvir -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
