oracle的SQL执行问题

Question

我现在有一条SQL；
select * from test where rownum=1 order by workdate desc;
这条SQL执行时，查询条件where rownum=1位于 之前；并不能查到最新的一条记录；

但是执行下面这条 SQL时：
select * from test where workdate<'20250807' and rownum=1 order by workdate desc；

就能查到最新的一条记录；
请问这个执行时的执行计划如何，一般是where执行后会执行orderby，为什么rownum=1写在where中也能查到最新的一条记录？

Answer 1

下午好🌅🌅🌅
本答案参考通义千问

你的问题涉及到 Oracle SQL 中 ROWNUM 的使用 以及 执行顺序与优化器行为 的理解。我们来详细分析你的两条 SQL 语句，并解释为什么在某些情况下 ROWNUM=1 可以获取到“最新”的记录。

---

📌 一、SQL 语句解析

✅ 第一条 SQL：

SELECT * FROM test 
WHERE ROWNUM = 1 
ORDER BY workdate DESC;

✅ 第二条 SQL：

SELECT * FROM test 
WHERE workdate < '20250807' 
AND ROWNUM = 1 
ORDER BY workdate DESC;

---

🧠 二、关键知识点：ROWNUM 和 ORDER BY 的执行顺序

重点：

• ROWNUM 是在 ORDER BY 之前应用的。
• Oracle 的执行顺序是：
  1. FROM / JOIN
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. ORDER BY
  6. ROWNUM

因此，当 ROWNUM=1 写在 WHERE 子句中时，它会在 ORDER BY 之前被应用，导致无法正确排序后取第一条记录。

---

🔍 三、为什么第二条 SQL 能查到最新的一条记录？

在第二条 SQL 中，虽然 ROWNUM=1 仍然在 WHERE 子句中，但你同时添加了 workdate < '20250807' 这个条件，这可能限制了查询的数据范围，使得优化器选择了一个更小的扫描范围，从而有可能在 ORDER BY 之后才应用 ROWNUM=1。

但这并不意味着 ROWNUM=1 在 ORDER BY 之后生效，只是因为数据量少，或者索引使用得当，导致实际执行计划中 ROWNUM 在排序后才被应用。

---

🧪 四、如何正确获取“最新”记录（即按 workdate 排序后的第一条）？

要获取按 workdate 排序后的第一条记录，必须确保 ROWNUM=1 是在 ORDER BY 之后应用的。

✅ 正确写法如下：

SELECT * 
FROM (
    SELECT * 
    FROM test 
    ORDER BY workdate DESC
) 
WHERE ROWNUM = 1;

这是标准做法，通过子查询先排序，再限制行数。

---

🔄 五、执行计划分析（建议使用 EXPLAIN PLAN）

你可以用以下命令查看执行计划：

EXPLAIN PLAN FOR
SELECT * 
FROM (
    SELECT * 
    FROM test 
    ORDER BY workdate DESC
) 
WHERE ROWNUM = 1;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

执行计划会显示：

• SORT ORDER BY（排序）
• FILTER（过滤 ROWNUM=1）

---

🛠 六、修改建议（代码示例）

如果你希望在不使用子查询的情况下获取最新记录，可以考虑使用 RANK() 或 ROW_NUMBER() 窗口函数：

✅ 使用 ROW_NUMBER()：

SELECT *
FROM (
    SELECT t.*, ROW_NUMBER() OVER (ORDER BY workdate DESC) AS rn
    FROM test t
)
WHERE rn = 1;

这种方式更加灵活，且避免了 ROWNUM 在 ORDER BY 前的问题。

---

📌 七、总结

| 问题 | 解释 | |------|------| | 为什么 ROWNUM=1 写在 WHERE 中不能获取最新记录？ | 因为 ROWNUM 在 ORDER BY 之前应用，导致排序无效。 | | 为什么第二条 SQL 能查到最新记录？ | 数据量较小或索引使用得当，可能使 ROWNUM 在排序后应用，但不是规范写法。 | | 如何正确获取最新记录？ | 使用子查询先排序，再限制 ROWNUM=1；或使用窗口函数如 ROW_NUMBER()。 |

---

✅ 最佳实践推荐

始终使用子查询结构来获取排序后的第一条记录：

SELECT * 
FROM (
    SELECT * 
    FROM test 
    ORDER BY workdate DESC
) 
WHERE ROWNUM = 1;

这样可以确保排序后再限制行数，避免因优化器行为导致的意外结果。