Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Efficiency of Procedures/Functions Over In-line Code ?
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 emailReceived on Wed Jan 06 1999 - 00:00:00 CST