Re: Efficiency of Procedures/Functions Over In-line Code ?

From: Pete Johnson <P.Johnson_at_ste0426.x400.icl.co.uk>
Date: 1999/01/05
Message-ID: <3691d0c1.2615090@news.cafevik.icl.co.uk>#1/1

On Tue, 05 Jan 1999 06:07:47 GMT, "Dave Wotton" <Dave.Wotton_at_dwotton.nospam.clara.co.uk> wrote:

>Hi,
>
>I agree with all the wise comments from Sybrand, Thomas and others on the
>careful use of functions.
>

In the example you give did you check the access paths? The first example uses a literal so Oracle will select on an index on that column, however your second example uses a variable in the query.

When Oracle parses the second query - when it stores the function - it will not know what the value of that variable is and therefore tends towards full table scans - this could be the reason for the time differences.

This is a real problem with stored procedures and functions. There seems to be no answer. In some cases we found that it was more efficient to use DBMS_SQL to dynamically create selects as at least we could supply literal values .

Pete Received on Tue Jan 05 1999 - 00:00:00 CST