Fastest way of getting value associated with max(...)

Hi all,
I was currently revising a trigger written by other people when I came across the following:

SELECT max(to_char(exch_date,'YYYYMMDD') || '-' || to_char(exch_rate))   INTO mystr
  FROM exchange

 WHERE curr_from = :new.curr_from_code 
   AND curr_to = :new.curr_to_code
   AND exch_date <= sysdate;

Later on in the trigger body, the content of <mystr> is manipulated with to_number and substr to get the value of exch_rate back (something that could be included in the SELECT right away to further optimize it).

In other words the code is trying to locate the exch_rate corresponding to the latest date against a certain pair of currencies.

The explain plan for this statement is:

Operation                    Name         Rows Bytes Cost
SELECT STATEMENT                          1    21    3
SORT AGGREGATE                            1    21
TABLE ACCESS BY INDEX ROWID  EXCHANGE     2    42    3
INDEX RANGE SCAN             W26_PK       2    2

Initially I thought this was just a cumbersome way of getting the value, but after trying various methods, it seems the most efficient according to TKPROF results.
This trigger code sometimes gets executed millions of times, so it is extremely important to achieve the best performance.

The best alternative way I found so far is:

select exch_rate
from exchange

where curr_from = :new.curr_from_code
  and curr_to = :new.curr_to_code
  and exch_date = (select max(exch_date)
                     from exchange
                    where curr_from = :new.curr_from_code
                      and curr_to = :new.curr_to_code
                      and exch_date <= sysdate);

whose execution plan is:

Operation                    Name      Rows  Bytes   Cost
SELECT STATEMENT                       1     21      2
TABLE ACCESS BY INDEX ROWID  EXCHANGE  1     21      2
INDEX UNIQUE SCAN            W26_PK    1             1
SORT AGGREGATE                         1     16 
FIRST ROW                              2     32      2
INDEX RANGE SCAN (MIN/MAX)   W26_PK    2             2

Now, when I examine TKPROF results, the latter method is always somewhat slower (or at least heavier in the number of blocks read, nearly 20% more), probably because it involves a double index access contrasted with the single access of the former method.

As said earlier, if you iterate the above statement a million of times, it may end up in a difference of several minutes.

I also tried with TOP-N queries and analytic functions without success.

So, my final question is:
is there any better way of pulling out a column value from the same record where the maximum of some_column is located other than sticking it as a string?

Bye,
Flavio Received on Wed Feb 25 2004 - 03:53:21 CST