Alec,
Have a look at the 2 explain plans and see how they are different.
Have a look at wait stats / 10046 trace for the two and see how they are different.
What version of Oracle?
Do you have histograms?
If 8i or below and using the function then you will be using bind values and not
getting best effect from your histograms.
If you put the values directly into the select then your histograms will be used for
selectivity determination in creating
Of course, it might be something else entirely...
HTH,
Bruce Reardon
-----Original Message-----
Sent: Friday, 21 March 2003 10:24 AM
I have written a function to return a drug price from our database. If I use
this function in a SQL statement it take a long time to return a value.
However running the main cursor in the function in SQL returns a value
immediately. DOing a little debugging I find that the function does 6000
physical reads compared to 8 as a SELECT statement.
Function looks like this
FUNCTION GetPrice(DrugID varchar2, PriceListID number) return number IS
cursor main(DrugId varchar2, PrcId number) is
SELECT price
FROM prices p
WHERE ndc = DrugId
AND price_list = PrcId
AND effective_begin (SELECT max(effective_begin)
FROM prices
WHERE ndc = p.ndc
AND price_list = p.price_list);
ReturnVal number(10,2);
BEGIN
OPEN main(DrugId,PriceListID);
FETCH main
INTO ReturnVal;
CLOSE main;
RETURN ReturnVal;
END Getprice;
IF I run 'SELECT GetPrice('1234',1) FROM dual;' it takes 6000 physical
reads.
If I run the select statement in main replacing DrugID and PrcID with values
it take 8 reads or less.
I know I will see a preformance hit for embedding a function in a select
statement but this seems a bit draconian. Could someone recommend a path
that might explain why I have so much overhead on this function?
Alec
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Reardon, Bruce (CALBBAY)
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).
