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