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: Alec Macdonell
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).
