Oracle查询优化：如何无需子查询获取当前日期所属周期？

我的数据库结构如下：

年度结束日期为2023-03-31。我需要基于current_date编写查询，返回当前日期所属的周期（比如当前日期属于Period 6时返回对应记录）。我当前的查询是：

SELECT period FROM table1 as a
WHERE
a.start_date = (SELECT MAX(start_date) FROM table1 as b WHERE 
b.start_date <=current_date) and ROWNUM <= 1

请问有没有办法优化该查询，避免使用子查询？

当然可以优化，而且能完全去掉子查询。核心思路是筛选出所有早于等于当前日期的周期起始记录，再按起始日期倒序取第一条，这就是当前日期所属的周期：

适用于Oracle 12c及以上版本（推荐） #

SELECT period
FROM table1
WHERE start_date <= CURRENT_DATE
ORDER BY start_date DESC
FETCH FIRST 1 ROW ONLY;

适用于Oracle 12c之前的版本 #

如果你的Oracle版本不支持FETCH FIRST语法，可以用ROWNUM实现，虽然用到了嵌套查询，但相比原查询逻辑更清晰，性能也更优：

SELECT period
FROM (
    SELECT period
    FROM table1
    WHERE start_date <= CURRENT_DATE
    ORDER BY start_date DESC
)
WHERE ROWNUM <= 1;

处理年度结束后的边界情况 #

如果当前日期超过年度结束日2023-03-31，你可能希望仍然返回最后一个周期（Period 12），可以用LEAST函数限制日期范围：

SELECT period
FROM table1
WHERE start_date <= LEAST(CURRENT_DATE, DATE '2023-03-31')
ORDER BY start_date DESC
FETCH FIRST 1 ROW ONLY;

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