寻求更高效的Oracle SQL语句：获取特定PERSON_ID最新REFERENCE_ID记录

现有PERSON表结构及数据 #

需求说明 #

获取特定PERSON_ID下，每个相同REFERENCE_ID对应的最新CREATION_DATE记录。针对PERSON_ID=10066，预期返回3行结果：

现有实现SQL #

本人已写出可行的子查询SQL，但担忧其性能，希望得到更优方案（例如无需子查询的写法）：

SELECT * 
FROM PERSON_TABLE p 
WHERE CREATION_DATE = (
    SELECT MAX(CREATION_DATE) 
    FROM PERSON_TABLE 
    WHERE REFERENCE_ID = p.REFERENCE_ID AND PERSON_ID = 10066
);

优化方案 #

方案1：使用ROW_NUMBER()窗口函数 #

这是Oracle中处理“每组取最新记录”场景的高效写法，仅需扫描表一次：

SELECT PERSON_ID, REFERENCE_ID, COMMENT, CREATION_DATE
FROM (
    SELECT 
        t.*,
        ROW_NUMBER() OVER (PARTITION BY REFERENCE_ID ORDER BY CREATION_DATE DESC) AS rn
    FROM PERSON_TABLE t
    WHERE PERSON_ID = 10066
)
WHERE rn = 1;

• PARTITION BY REFERENCE_ID 按REFERENCE_ID分组
• ORDER BY CREATION_DATE DESC 每组内按创建时间倒序排列，最新记录排在首位
• 外层筛选rn=1即可得到每组的最新记录

方案2：使用分组MAX关联查询 #

如果偏好非窗口函数的写法，可先分组获取最新时间再关联原表：

SELECT t.PERSON_ID, t.REFERENCE_ID, t.COMMENT, t.CREATION_DATE
FROM PERSON_TABLE t
JOIN (
    SELECT 
        REFERENCE_ID,
        MAX(CREATION_DATE) AS max_date
    FROM PERSON_TABLE
    WHERE PERSON_ID = 10066
    GROUP BY REFERENCE_ID
) m ON t.REFERENCE_ID = m.REFERENCE_ID 
    AND t.CREATION_DATE = m.max_date
    AND t.PERSON_ID = 10066;

性能优化建议 #

无论采用哪种写法，建议创建复合索引提升查询效率：

CREATE INDEX idx_person_ref_date ON PERSON_TABLE(PERSON_ID, REFERENCE_ID, CREATION_DATE DESC);

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