Oracle SQL Query (help/question about Oracle "HINTS")

Hi, I have the following query that gives the correct answer.

SELECT a.f1,a.f2,a.f3
 FROM a,b
  WHERE a.f7=16
    AND fn_calculateMovingRate(a.f1,b.fAX) > 65     AND fn_calculateDAR(a.f1,b.fDAR) > 16     AND a.f7=16
      ORDER BY a.f1 ASC, a.f2 ASC, a.f3 ASC;

fn_calculateDAR takes no time at all - basically a.f1 * b.fDAR (an exchange rate variance).

fn_calculateMovingRate typically takes 1.5 seconds when invoken directly, and involves a lookup to a stock ticker.

These two functions are stock Oracle functions we use in the company, and return numbers.

The query typically returns 4-5 rows every time it is executed, but takes over 90 seconds to execute. Is there a HINT I can give Oracle to tell it to use all the other conditions in the WHERE clause first, and at the very end filter the minimal temporary resultset down with the slow fn_calculateMovingRate (if I remove the AND fn_calculateMovingRate(a.f1,b.fAX) > 65 I normally get back 10 or 12 rows and the query is complete in under a second, so if the function was performed last, (10 to 12)*1.5seconds=15 to 18 seconds, which is a lot better than 90 seconds). I hope this argument is correct ;)

I am modifying a VBA application and I know VBA well but not Oracle. I am using 8.1.7.0.0 on a dedicated Windows NT 4.0 Server.

Thank you
Tony Received on Thu Aug 14 2003 - 05:07:07 CDT