DEFINE numb_mask = 'FM9999999999.99999'
DEFINE date_mask = 'YYYYMMDD'
DEFINE date_mast_l1 =  9 

SELECT TO_NUMBER(
         SUBSTR(
           (
           SELECT MAX(TO_CHAR(r.rdate, '&&date_mask') || TO_CHAR(r.rrate, '&&numb_mask', 'NLS_NUMERIC_CHARACTERS=".,"')) l
             FROM rates r
            WHERE r.rdate <= x.xdate
              AND r.rcurrency = x.xcurrency
            GROUP BY
                  r.rcurrency
           )
         , &&date_mast_l1
         )
       , '&&numb_mask'
       , 'NLS_NUMERIC_CHARACTERS=".,"'
       ) rate
  FROM transactions x
/

SQL> SELECT * FROM v$version WHERE ROWNUM =  1 ;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release  10 . 1 . 0 . 4 . 0  - Prod

SQL> CREATE TABLE transactions (xid NUMBER, xsum FLOAT, xdate DATE, xcurrency NUMBER);

Таблица создана.

SQL> CREATE TABLE rates (rcurrency NUMBER, rdate DATE, rrate FLOAT);

Таблица создана.

SQL> CREATE UNIQUE INDEX ux_rate_currency_date ON rates (rcurrency, rdate);

Индекс создан.

SQL> BEGIN
   2     dbms_stats.gather_table_stats(ownname => USER, tabname => 'RATES', CASCADE => TRUE);
   3     dbms_stats.gather_table_stats(ownname => USER, tabname => 'TRANSACTIONS');
   4   END;
   5   /

Процедура PL/SQL успешно завершена.

SQL> INSERT INTO transactions VALUES( 1 ,  1 , TRUNC(SYSDATE),  1 );

 1  строка создана.

SQL> INSERT INTO transactions VALUES( 1 ,  2 , TRUNC(SYSDATE),  2 );

 1  строка создана.

SQL> INSERT INTO transactions VALUES( 1 ,  2 , TRUNC(SYSDATE) -  1 ,  2 );

 1  строка создана.

SQL> INSERT INTO rates VALUES( 1 , TRUNC(SYSDATE) -  1 ,  1 . 2 );

 1  строка создана.

SQL> INSERT INTO rates VALUES( 1 , TRUNC(SYSDATE) -  2 ,  1 . 1 );

 1  строка создана.

SQL> INSERT INTO rates VALUES( 2 , TRUNC(SYSDATE) -  0 ,  1 . 0 );

 1  строка создана.

SQL> INSERT INTO rates VALUES( 2 , TRUNC(SYSDATE) -  1 ,  1 . 1 );

 1  строка создана.

SQL> INSERT INTO rates VALUES( 2 , TRUNC(SYSDATE) -  2 ,  1 . 2 );

 1  строка создана.

SQL> 
SQL> SET AUTOTRACE ON EXPL
SQL> SELECT  (
   2   	     SELECT  /*+ INDEX_DESC (r ux_rate_currency_date) */
   3   		     rrate
   4   	     FROM    rates r
   5   	     WHERE   r.rcurrency = x.xcurrency
   6   		     AND r.rdate <= x.xdate
   7   		     AND rownum =  1 
   8   	     ) AS eff_rate
   9   FROM    transactions x
  10   /

  EFF_RATE
----------
        1 , 2 
          1 
        1 , 1 


План выполнения
----------------------------------------------------------
    0       SELECT STATEMENT Optimizer=ALL_ROWS (Cost= 2  Card= 1  Bytes= 22 )
    1      0    COUNT (STOPKEY)
    2      1      TABLE ACCESS (BY INDEX ROWID) OF 'RATES' (TABLE)
    3      2        INDEX (RANGE SCAN DESCENDING) OF 'UX_RATE_CURRENCY_DATE' (INDEX (UNIQUE))
    4      0    TABLE ACCESS (FULL) OF 'TRANSACTIONS' (TABLE) (Cost= 2  Card= 1  Bytes= 22 )



SQL> ALTER SESSION SET -
> "_optimizer_ignore_hints"=TRUE
   2   /

Сеанс изменен.

SQL> SELECT  (
   2   	     SELECT  /*+ INDEX_DESC (r ux_rate_currency_date) */
   3   		     rrate
   4   	     FROM    rates r
   5   	     WHERE   r.rcurrency = x.xcurrency
   6   		     AND r.rdate <= x.xdate
   7   		     AND rownum =  1 
   8   	     ) AS eff_rate
   9   FROM    transactions x
  10   /

  EFF_RATE
----------
        1 , 1 
        1 , 2 
        1 , 2 


План выполнения
----------------------------------------------------------
    0       SELECT STATEMENT Optimizer=ALL_ROWS (Cost= 2  Card= 1  Bytes= 22 )
    1      0    COUNT (STOPKEY)
    2      1      TABLE ACCESS (BY INDEX ROWID) OF 'RATES' (TABLE)
    3      2        INDEX (RANGE SCAN) OF 'UX_RATE_CURRENCY_DATE' (INDEX (UNIQUE))
    4      0    TABLE ACCESS (FULL) OF 'TRANSACTIONS' (TABLE) (Cost= 2  Card= 1  Bytes= 22 )



SQL> DROP TABLE rates;

Таблица удалена.

SQL> DROP TABLE transactions;

Таблица удалена.

SQL> EXEC EXECUTE IMMEDIATE 'purge recyclebin';

Процедура PL/SQL успешно завершена.

SQL> SPOOL OFF

SQL> SELECT * FROM TABLE(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value:  204164600 

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|    0  | SELECT STATEMENT             |                   |      3  |     66  |      3    ( 0 )|  00 : 00 : 01  |
|    1  |  SORT GROUP BY NOSORT        |                   |      1  |     35  |      2    ( 0 )|  00 : 00 : 01  |
|*   2  |   TABLE ACCESS BY INDEX ROWID| RATES             |      1  |     35  |      2    ( 0 )|  00 : 00 : 01  |
|*   3  |    INDEX RANGE SCAN          | UX_RATES_FUNCTION |      1  |       |      1    ( 0 )|  00 : 00 : 01  |
|    4  |    TABLE ACCESS FULL         | TRANSACTIONS      |      3  |     66  |      3    ( 0 )|  00 : 00 : 01  |
--------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

    2  - filter("R"."RDATE"<=:B1)
    3  - access("R"."RCURRENCY"=:B1)

SELECT	/*+ INDEX (r ux_rates_function) */
	MAX(TO_CHAR(rdate, 'YYYYMMDD') ||
	TO_CHAR(rrate, 'FM9999999999.99999', 'NLS_NUMERIC_CHARACTERS=".,"'))
FROM	rates r
WHERE	rcurrency = :cur
	AND (TO_CHAR(rdate, 'YYYYMMDD') ||
	TO_CHAR(rrate, 'FM9999999999.99999', 'NLS_NUMERIC_CHARACTERS=".,"')) <= :xdate
GROUP BY
	rcurrency