Oracle按Doc分组查询指定日期及上一操作日期的SQL优化问题

方案一：用分析函数+前置过滤，不浪费索引 #

别担心分析函数用不上索引，我们可以先通过OperDate的索引把数据范围缩小，再计算上一个操作日期：

WITH RelevantLogs AS (
    SELECT 
        Doc, 
        OperDate,
        -- 按Doc分组，按操作日期倒序，取当前行的前一行日期
        LAG(OperDate) OVER (PARTITION BY Doc ORDER BY OperDate DESC) AS PrevOperDate
    FROM Log
    WHERE OperDate <= TRUNC(SYSDATE) -- 只取目标日期及更早的数据，用OperDate索引快速筛选
)
SELECT Doc, OperDate, PrevOperDate
FROM RelevantLogs
WHERE OperDate = TRUNC(SYSDATE); -- 最后只留目标日期的行

这个逻辑的优势：

• 第一步用OperDate <= 目标日期的条件，直接走OperDate的索引，不用扫全表，数据集瞬间缩小。
• 分组排序时，Doc的索引能降低排序成本。
• 整个过程只需要一次索引扫描+轻量排序，比原查询的多次索引访问高效得多。

方案二：给原查询加组合索引，直接提速 #

如果习惯用原有的关联子查询写法，建一个**(Doc, OperDate DESC)的组合索引**就能解决两次索引访问的问题：

CREATE INDEX idx_log_doc_opdate ON Log(Doc, OperDate DESC);

有了这个组合索引后，子查询里的max(B.OperDate)不用再单独扫索引——组合索引已经按Doc分组、OperDate倒序排好了，直接就能拿到每个Doc小于当前日期的最大OperDate，执行效率会大幅提升。

两种方案怎么选？ #

• 如果经常查询不同日期的上一个操作日期，组合索引方案更通用，不管查哪个日期都能快速定位。
• 如果只查特定日期，且当天的数据量很小，方案一的分析函数写法更省资源。

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