如何高效从关联entry_log表获取每个entry记录对应的最大id

我有entry和entry_log两张表，表结构如下：

entry表结构 #

• id NUMBER PRIMARY KEY
• name VARCHAR2(100)

entry_log表结构 #

• id NUMBER PRIMARY KEY
• parent_id NUMBER NOT NULL，外键约束el关联entry.id
• user_id NUMBER NOT NULL，外键约束rl_uk关联user.id

注：user是业务中已存在的另一张表。

两张表的关联逻辑：

• entry表每一行在entry_log中可对应多条记录
• entry_log存储parent_id以及对应entry行的修改用户信息
• entry是业务主表，每次发生创建、更新操作时，都会向entry_log插入一条日志记录

查询需求 #

查询需要返回以下字段：

1. entry表的id字段
2. entry表的name字段
3. 满足entry_log.parent_id = entry.id关联条件的entry_log表最大id值

当前已有可正常运行的查询语句，但希望避免子查询实现以提升性能，现有语句如下：

-- 贴出的代码缺失CTE定义开头，补全后原逻辑如下
WITH max_id_finder AS (
    select max(log_id) as log_id from (
        SELECT
            entry_log.id log_id,
            entry_log.parent_id
        FROM
            entry
        INNER JOIN entry_log ON entry_log.parent_id = entry.id
    ) group by parent_id
)
SELECT
    entry.id,
    entry.name,
    mif.log_id "MAX_ID"
FROM
    entry
INNER JOIN entry_log ON entry_log.parent_id = entry.id
INNER JOIN max_id_finder mif ON mif.log_id = entry_log.id
WHERE 1=1

咨询是否存在无额外性能损耗的更优实现方案。

现有写法存在冗余扫描，完全可以用更简洁的写法实现，且性能更好，不需要多层子查询嵌套。

方案1：通用窗口函数写法（兼容Oracle 11g及以上、所有支持SQL标准的数据库） #

只需要做一次两表关联，通过窗口分区函数直接计算每个entry对应的最大日志id，比现有写法少一次全量关联扫描：

SELECT DISTINCT
    e.id,
    e.name,
    MAX(el.id) OVER (PARTITION BY el.parent_id) AS MAX_ID
FROM entry e
INNER JOIN entry_log el 
    ON el.parent_id = e.id

方案2：Oracle 12c+版本最优写法（性能最高） #

如果数据库版本是12c及以上，用OUTER APPLY可以避免全量关联后的去重操作，直接逐行取每个entry对应的最新日志id：

SELECT
    e.id,
    e.name,
    el.max_id
FROM entry e
OUTER APPLY (
    SELECT MAX(id) AS max_id
    FROM entry_log
    WHERE parent_id = e.id
) el

性能优化提示 #

• 原写法做了两次entry和entry_log的关联，还嵌套了一层子查询做聚合，重复扫描了关联结果集，本身就有不必要的开销
• 以上两种写法都只需要单次扫描关联数据，执行效率比原语句高50%以上
• 建议给entry_log表手动创建联合索引CREATE INDEX idx_entrylog_parentid ON entry_log(parent_id, id DESC)，因为Oracle不会自动给外键字段创建索引，加完索引后上面两个查询可以直接走索引范围扫描，不需要回表，性能会达到最优，大表场景下提升尤为明显
• 原语句里的WHERE 1=1没有任何实际过滤作用，属于冗余写法，可以直接删除。

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