Oracle SQL查询优化请求：附查询语句、表结构及执行计划

原查询语句 #

select /*+ INDEX(ex1 EXCH_RATE_BK_IDX) */
    ex1.currency_id as currency_id,
    ex1.underly_currency_id as underly_currency_id,
    ex1.type_id as type_id,
    ex1.third_id as third_id,
    ex1.market_third_id as market_third_id,
    ex1.exch_d as exch_d,
    ex1.daily_dflt_f as daily_dflt_f,
    ex1.exch_rate as exch_rate,
    ex1.external_seq_no as external_seq_no,
    ex1.creation_d as creation_d,
    ex1.creation_user_id as creation_user_id,
    ex1.last_modif_d as last_modif_d,
    ex1.last_user_id as last_user_id
    /* security_level_e = 0 */
from exch_rate ex1
inner join ( 
    select max(exch_d) as max_exch_d, type_id, third_id, market_third_id
    from exch_rate
    where currency_id = '1004'
      and underly_currency_id = '2'
      and exch_d >= TO_DATE('24-12-2009 00:00:00', 'DD-MM-YYYY HH24:MI:SS') 
      and exch_d <= TO_DATE('14-10-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
    group by type_id, third_id, market_third_id
) ex3
on  ex1.exch_d = ex3.max_exch_d 
and (ex1.type_id = ex3.type_id or (ex1.type_id is NULL and ex3.type_id is NULL)) 
and (ex1.third_id = ex3.third_id or (ex1.third_id is NULL and ex3.third_id is NULL)) 
and (ex1.market_third_id = ex3.market_third_id or (ex1.market_third_id is NULL and ex3.market_third_id is NULL))
where ex1.currency_id = '1004' 
  and ex1.underly_currency_id = '2' 
  and ex1.exch_d >= TO_DATE('24-12-2009 00:00:00', 'DD-MM-YYYY HH24:MI:SS') 
  and ex1.exch_d <= TO_DATE('14-10-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
order by exch_d desc nulls last;

表结构与索引信息 #

CREATE TABLE "EXCH_RATE" 
(    
    "CURRENCY_ID" NUMBER(14,0) NOT NULL ENABLE, 
    "UNDERLY_CURRENCY_ID" NUMBER(14,0) NOT NULL ENABLE, 
    "TYPE_ID" NUMBER(14,0), 
    "THIRD_ID" NUMBER(14,0), 
    "MARKET_THIRD_ID" NUMBER(14,0), 
    "EXCH_D" TIMESTAMP (6) NOT NULL ENABLE, 
    "DAILY_DFLT_F" NUMBER(3,0) DEFAULT 0 NOT NULL ENABLE, 
    "EXCH_RATE" NUMBER(23,14) NOT NULL ENABLE, 
    "EXTERNAL_SEQ_NO" NUMBER(20,0), 
    "CREATION_D" TIMESTAMP (6), 
    "CREATION_USER_ID" NUMBER(14,0), 
    "LAST_MODIF_D" TIMESTAMP (6), 
    "LAST_USER_ID" NUMBER(14,0), 
    CONSTRAINT "EXCH_RATE_DAILY_DFLT_CHK" CHECK (daily_dflt_f in (0, 1)) ENABLE, 
    CONSTRAINT "FK_701001" FOREIGN KEY ("CURRENCY_ID")
        REFERENCES "D_ORA_22_1_AAAMAINDB"."CURRENCY" ("ID") ON DELETE CASCADE ENABLE, 
    CONSTRAINT "FK_701002" FOREIGN KEY ("UNDERLY_CURRENCY_ID")
        REFERENCES "D_ORA_22_1_AAAMAINDB"."CURRENCY" ("ID") ON DELETE CASCADE ENABLE, 
    CONSTRAINT "FK_701003" FOREIGN KEY ("TYPE_ID")
        REFERENCES "D_ORA_22_1_AAAMAINDB"."TYPE" ("ID") ENABLE, 
    CONSTRAINT "FK_701004" FOREIGN KEY ("THIRD_ID")
        REFERENCES "D_ORA_22_1_AAAMAINDB"."THIRD_PARTY" ("ID") ENABLE, 
    CONSTRAINT "FK_701005" FOREIGN KEY ("MARKET_THIRD_ID")
        REFERENCES "D_ORA_22_1_AAAMAINDB"."THIRD_PARTY" ("ID") ENABLE
);

CREATE UNIQUE INDEX "EXCH_RATE_BK_IDX" ON "EXCH_RATE" ("CURRENCY_ID", "EXCH_D", "UNDERLY_CURRENCY_ID", "TYPE_ID", "THIRD_ID", "MARKET_THIRD_ID");

原执行计划 #

Explain plan
Plan hash value: 471245728
 
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                  |     1 |   259 |    29   (4)| 00:00:01 |
|*  1 |  FILTER                                |                  |       |       |            |          |
|   2 |   SORT GROUP BY                        |                  |     1 |   259 |    29   (4)| 00:00:01 |
|*  3 |    HASH JOIN                           |                  |     1 |   259 |    28   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                   | EXCH_RATE_BK_IDX |  2291 |   174K|    14   (0)| 00:00:01 |
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED| EXCH_RATE        |  2291 |   404K|    14   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN                  | EXCH_RATE_BK_IDX |     9 |       |    14   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("EX1"."EXCH_D"=MAX("EXCH_D"))
   3 - access(SYS_OP_MAP_NONNULL("EX1"."TYPE_ID")=SYS_OP_MAP_NONNULL("TYPE_ID") AND 
              SYS_OP_MAP_NONNULL("EX1"."THIRD_ID")=SYS_OP_MAP_NONNULL("THIRD_ID") AND 
              SYS_OP_MAP_NONNULL("EX1"."MARKET_THIRD_ID")=SYS_OP_MAP_NONNULL("MARKET_THIRD_ID"))
   4 - access("CURRENCY_ID"=1004 AND "EXCH_D">=TIMESTAMP' 2009-12-24 00:00:00' AND 
              "UNDERLY_CURRENCY_ID"=2 AND "EXCH_D"<=TIMESTAMP' 2012-10-14 00:00:00')
       filter("UNDERLY_CURRENCY_ID"=2)
   6 - access("EX1"."CURRENCY_ID"=1004 AND "EX1"."EXCH_D">=TIMESTAMP' 2009-12-24 00:00:00' AND 
              "EX1"."UNDERLY_CURRENCY_ID"=2 AND "EX1"."EXCH_D"<=TIMESTAMP' 2012-10-14 00:00:00')
       filter("EX1"."UNDERLY_CURRENCY_ID"=2)
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

优化建议与改写后的查询 #

1. 调整索引顺序，提升过滤效率 #

原索引EXCH_RATE_BK_IDX将范围列EXCH_D放在等值列UNDERLY_CURRENCY_ID之前，导致执行计划中出现额外的filter("UNDERLY_CURRENCY_ID"=2)。建议重建索引，将等值条件列前置：

CREATE UNIQUE INDEX "EXCH_RATE_OPTIMIZED_IDX" ON "EXCH_RATE" (
    "CURRENCY_ID", 
    "UNDERLY_CURRENCY_ID", 
    "EXCH_D", 
    "TYPE_ID", 
    "THIRD_ID", 
    "MARKET_THIRD_ID"
);
-- 原索引可根据实际情况保留或删除

调整后，索引可直接通过CURRENCY_ID+UNDERLY_CURRENCY_ID+EXCH_D范围快速定位数据，无需额外过滤。

2. 简化NULL值比较逻辑 #

原连接条件中(a = b OR (a IS NULL AND b IS NULL))可通过SYS_OP_MAP_NONNULL函数简化，Oracle已在执行计划中自动使用该函数，显式写出可提升可读性：

AND SYS_OP_MAP_NONNULL(ex1.type_id) = SYS_OP_MAP_NONNULL(ex3.type_id)
AND SYS_OP_MAP_NONNULL(ex1.third_id) = SYS_OP_MAP_NONNULL(ex3.third_id)
AND SYS_OP_MAP_NONNULL(ex1.market_third_id) = SYS_OP_MAP_NONNULL(ex3.market_third_id)

3. 使用窗口函数重写查询，避免重复扫描 #

原查询通过子查询分组取最大值再连接，需要两次扫描表。改用ROW_NUMBER()窗口函数，一次扫描即可获取每组最新的记录：

SELECT 
    currency_id,
    underly_currency_id,
    type_id,
    third_id,
    market_third_id,
    exch_d,
    daily_dflt_f,
    exch_rate,
    external_seq_no,
    creation_d,
    creation_user_id,
    last_modif_d,
    last_user_id
FROM (
    SELECT 
        er.*,
        ROW_NUMBER() OVER (
            PARTITION BY type_id, third_id, market_third_id 
            ORDER BY exch_d DESC NULLS LAST
        ) AS rn
    FROM exch_rate er
    WHERE currency_id = '1004'
      AND underly_currency_id = '2'
      AND exch_d >= TO_DATE('24-12-2009 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
      AND exch_d <= TO_DATE('14-10-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
) t
WHERE rn = 1
ORDER BY exch_d DESC NULLS LAST;

该写法减少了一次表扫描和哈希连接，执行效率更高。

4. 移除不必要的强制索引提示 #

原查询中的/*+ INDEX(ex1 EXCH_RATE_BK_IDX) */强制指定索引，若已创建优化后的索引，建议移除该提示，让Oracle优化器自动选择最优执行路径。

5. 避免重复条件 #

原查询中ex1的WHERE条件与子查询完全重复，改写后的窗口函数版本已自然避免这一问题。

内容的提问来源于stack exchange，提问作者jagannath Bhoi