Function problem

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? Received on Thu Mar 20 2003 - 17:26:29 CST