如何让Oracle查询充分利用T2表的(name, finished_at)复合索引

表结构定义 #

CREATE TABLE t1
(
   name      VARCHAR2(50 CHAR),
   status    VARCHAR2(8 CHAR),
   CONSTRAINT t1_pk PRIMARY KEY(name)
);

CREATE TABLE t2
(
   id             NUMBER NOT NULL,
   name           VARCHAR2(50 CHAR),
   finished_at    DATE,
   status         VARCHAR2(8 CHAR),
   CONSTRAINT t2_pk PRIMARY KEY(id)
);

CREATE INDEX ix_t2_last
   ON t2(name, finished_at);

需求描述 #

获取T1表中指定name对应的name、status字段，同时关联获取T2表中该name下finished_at值最大的记录的status字段。

当前查询语句 #

SELECT name,
       status,
       (  SELECT t2.status
            FROM t2
           WHERE t2.name = t1.name
        ORDER BY t2.finished_at
           FETCH FIRST ROW ONLY)
  FROM t1
 WHERE t1.name = :name;

问题分析 #

当前查询的执行计划仅利用ix_t2_last索引进行name的范围扫描，未充分利用索引中finished_at字段的有序性。由于T2表中同一name对应大量数据，范围扫描会导致过多buffer gets，影响查询性能。

解决方案 #

1. 调整查询语句，引导Oracle利用索引有序性 #

当前子查询按finished_at升序排序，Oracle需要扫描所有同name的记录才能找到目标行。改为降序排序后，Oracle可以直接定位到索引中同name下finished_at最大的记录，无需扫描全部数据：

SELECT name,
       status,
       (  SELECT t2.status
            FROM t2
           WHERE t2.name = t1.name
        ORDER BY t2.finished_at DESC
           FETCH FIRST ROW ONLY)
  FROM t1
 WHERE t1.name = :name;

另外，也可以使用Oracle的KEEP聚合函数语法，更高效地获取目标值，避免子查询的额外开销：

SELECT 
    t1.name,
    t1.status,
    MAX(t2.status) KEEP (DENSE_RANK LAST ORDER BY t2.finished_at) AS t2_status
FROM t1
LEFT JOIN t2 ON t1.name = t2.name
WHERE t1.name = :name
GROUP BY t1.name, t1.status;

对于Oracle 12c及以上版本，还可以使用LATERAL JOIN语法，逻辑更清晰且性能优异：

SELECT 
    t1.name,
    t1.status,
    t2.status
FROM t1
LEFT JOIN LATERAL (
    SELECT status 
    FROM t2 
    WHERE t2.name = t1.name 
    ORDER BY finished_at DESC 
    FETCH FIRST 1 ROW ONLY
) t2 ON 1=1
WHERE t1.name = :name;

2. 调整索引实现覆盖查询 #

当前索引ix_t2_last仅包含name和finished_at，查询时需要回表获取status字段。如果将status加入索引形成覆盖索引，Oracle可以直接从索引中获取所有需要的数据，无需访问表，进一步降低IO开销：

-- 可选：删除原索引，保留也不影响但新索引更高效
DROP INDEX ix_t2_last;

-- 创建包含status且finished_at降序的复合索引
CREATE INDEX ix_t2_last ON t2(name, finished_at DESC, status);

这里将finished_at指定为降序，和查询中的排序方向一致，能让Oracle直接定位到目标记录，无需额外排序操作。

内容的提问来源于stack exchange，提问作者D. Mika