I have a currency table and an exchange\_rate\_log table. The latter contains over a billion of records. The exchange\_rate\_log table contains the traded exchange rates for many currencies for the last couple of years.

Now I have to select for all available currencies (in the table currency) the latest valid traded exchange rate for a given exchange\_currency and a given date.

So if the given exchange\_currency would be "EUR" and the date would be yesterday. The result would return the latest trades of all available currencies into "EUR" in the time window from the first available entries in the table "exchange\_rate\_log" until yesterday.

The following query shows a possible way to get the answer. However the given query does not perform very well.

```
SELECT cur.name, log.price, log.valid_at
FROM currency cur
JOIN exchange_rate_log log ON (cur.id = log.currency_id)
WHERE log.valid_at = (SELECT max(log2.valid_at) 
                  FROM exchange_rate_log log2 
                  WHERE log2.currency_id = cur.id 
                  AND log2.exchange_currency = ?
                  AND log2.valid_at < ?);

```

Is there a possibility to get the same result with an adapted query which would perform better? Is it possible to create an index to boost the performance of the above query?

Remark: The target dbms is Oracle.