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

May not be quicker, but here is 1 suggested thing to try...

Declare the following as a cursor in the declare section (doing this from memory so cursor syntax may be off):

Cursor xyz is
select exch_rate
from exchange
WHERE curr_from = :new.curr_from_code

    AND curr_to = :new.curr_to_code
    AND exch_date <= sysdate
order by exch_date desc;

Then after Begin, do the following in PlSql code:   Open the cursor, fetch the 1st row and then close the cursor.

Then do what you want in PlSql with the exch_rate column.

-- 

"FC" <flavio_at_tin.it> wrote in message
news:2bd78ddf.0402250153.6ccdaf6_at_posting.google.com...


> 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