Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Efficiency of Procedures/Functions Over In-line Code ?

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

From: Dave Wotton <Dave.Wotton_at_dwotton.nospam.clara.co.uk>
Date: 1999/01/06
Message-ID: <dtFk2.958$hs6.1154@nnrp2.clara.net>#1/1

Pete Johnson wrote in message <3691d0c1.2615090_at_news.cafevik.icl.co.uk>...

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

Yes, I checked the access paths. I think you've missed the point. You're right that one method performs a full table scan and the other used indexes efficiently, but it was the function, with the bind variable, which used the the indexes and the select with outer join and a literal which used the full table scans. You seem to be expecting it to be the other way round.

For a full explanation of why the select with outer join is inefficient and the function is efficient, see my notes on this at

    http://home.clara.net/dwotton/dba/ojoin2.htm

>This is a real problem with stored procedures and functions.

You might be right, but you are over-generalising. Here are the access paths for a select with a literal condition and one with a bind variable. Both were called from PL/SQL ( although that's irrelevant ), Oracle version was 7.3.3

SELECT COLUMN1,COLUMN2
FROM
BIG_TABLE WHERE COLUMN1 = '0000 051 20008' Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: RULE
      1   TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF 'BIG_TABLE'
      2    INDEX (RANGE SCAN) OF 'BIG_TABLE$COL1' (NON-UNIQUE)

********************************************************************

SELECT COLUMN1,COLUMN2
FROM
BIG_TABLE WHERE COLUMN1 = :b1

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: RULE
      1   TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF 'BIG_TABLE'
      0    INDEX (RANGE SCAN) OF 'BIG_TABLE$COL1' (NON-UNIQUE)

*********************************************************************

Dave.

--
Remove "nospam" from my address to reply by email
Received on Wed Jan 06 1999 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US