RE: Function problem

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-----
From: Alec Macdonell [mailto:amacdonell_at_usscript.com] 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? Received on Thu Mar 20 2003 - 18:07:32 CST