在Oracle里跑一句“只取表里的第一行数据”几乎是所有Oracle开发新人绕不开的第一道坎。MySQL里一行LIMIT 1就完事了换成Oracle直接报ORA-00933因为Oracle压根不认识LIMIT去翻解决方案又会看到ROWNUM、FETCH FIRST、ROW_NUMBER、ROWID一堆名词同一个需求能翻出七八种写法有的写法甚至返回空。这篇文章就把这些写法彻底理一遍每种方案的SQL长什么样、底层原理是什么、适合什么场景、性能和坑在哪里也会给出一份可以直接抄的实操演示。适合刚从MySQL转过来的开发、写报表和分页接口的工程师也包括需要快速验证数据的DBA或运维。1. 先搞清楚Oracle里的“第一行”到底指什么1.1 为什么Oracle没有MySQL那种LIMITMySQL的LIMIT 1用习惯了会觉得“取第一行”是个特别自然的需求。但Oracle的SQL引擎从设计之初就没有LIMIT这个概念它提供的是一个叫ROWNUM的伪列。ROWNUM不是表里真实存在的一列它是在查询结果生成过程中Oracle按顺序给每一行分配的一个临时编号第一行是1第二行是2以此类推。关键点在于这个编号是“先分配、后过滤”的而且是在ORDER BY排序之前就分配完了。所以你和ORDER BY一起写的时候ROWNUM编号对应的是“排序前的物理顺序”不是“排序后的业务顺序”。还有一个隐藏问题Oracle默认的普通表是堆表Heap Table数据按插入顺序放在数据块里但没有“行号”这种稳定逻辑概念。准确地说物理上确实存在插入的第一行但你要把它找出来得靠ROWID而不是靠SQL的ORDER BY。业务上常说的“取第一行”绝大多数场景其实是想取“排序后的第一行”也就是最大值、最早记录、最新状态这类数据。1.2 业务里常见的三种“第一行”口径我接手过不少取第一行的需求归纳下来基本是下面三类。第一类任意一行。就是想看下表有没有数据、字段大概什么值。这种场景不关心是哪一行只要是表里的一行就行最常见的写法是SELECT * FROM t WHERE ROWNUM 1效率极高。第二类排序后的第一行。比如“订单金额最高的那条”“创建时间最早的那条”“最新状态的那条”。这种场景必须显式ORDER BY然后再在外面套一层ROWNUM过滤或者用12c以后才有的FETCH FIRST语法。第三类分组内的第一行。比如“每个部门工资最高的人”“每个用户最近一次登录记录”。这类需求要用窗口函数ROW_NUMBER()配合PARTITION BY实现单独用ROWNUM是做不了的。前两类是本文重点第三类属于扩展我会一并演示。1.3 哪种写法最适合你用先说结论不是每种写法都值得记但每种都是特定场合下的最优解。我做了个速查表后面每个写法都会展开讲。方案核心语法适用场景排序后取第一行是否可用主要注意点ROWNUMWHERE ROWNUM 1任何版本、任意一行、分页需要子查询配合别写ROWNUM 1以外的等值条件FETCH FIRSTFETCH FIRST 1 ROW ONLY12c及以后、最简洁可以直接配合ORDER BY老版本用不了窗口函数ROW_NUMBER() OVER (...)分组取第一行、去重本身就能排序慎用RANK取并列行ROWIDWHERE ROWID (SELECT MIN(ROWID) FROM t)调试、快速定位物理行不涉及排序表迁移后ROWID会变MIN/MAX子查询WHERE 主键 (SELECT MIN(主键) FROM t)取主键极值对应的行可用但只限主键需要唯一字段配合2. 五种取第一行的核心技术写法与原理2.1 ROWNUM最经典、也最容易翻车ROWNUM绝对是Oracle里最常用的取行方式但也是翻车率最高的。基础语法就一句SELECT * FROM emp WHERE ROWNUM 1;这条语句执行时Oracle从表中取第一行分配ROWNUM1然后判断ROWNUM 1成立返回。接着取第二行分配ROWNUM2判断2 1不成立丢弃。第三行同样分配3然后丢弃以此类推。所以结果就是第一条取到的行。很多人会顺手写WHERE ROWNUM 2想取第二行结果返回空。原因就是第一行分配ROWNUM1时就不满足ROWNUM2被丢弃第二行仍然从ROWNUM1开始编号还是不满足又被丢弃。只要第1行不满足条件后面所有行都排不上号。再讲讲ORDER BY的配合问题。直接写SELECT * FROM emp WHERE ROWNUM 1 ORDER BY sal DESC是错误的业务逻辑因为ROWNUM是在排序前分配的这行先被选出来然后才排序最终返回的是“表中物理第一条”完全不是你要的“工资最高那条”。正确写法是套子查询先排序再过滤SELECT * FROM ( SELECT * FROM emp ORDER BY sal DESC ) WHERE ROWNUM 1;这个写法就是Oracle 12c之前的“标准答案”。内层先排序外层再取第一行。排序后的结果作为临时结果集ROWNUM才是按业务排序后的顺序编号。性能上不带ORDER BY、只取一行时ROWNUM是效率最高的写法因为Oracle找到第一个满足条件的行就会停下来执行计划里会出现COUNT STOPKEY。但一旦内层有排序排序的开销跑不掉需要重点看排序列上有没有索引。2.2 FETCH FIRST12c以后最优雅的答案Oracle 12c推出了FETCH FIRST语法终于不用再套子查询了。这是我最推荐给新人的写法逻辑清楚、可读性好。SELECT * FROM emp FETCH FIRST 1 ROW ONLY;如果要按某个字段排序取第一行SELECT * FROM emp ORDER BY sal DESC FETCH FIRST 1 ROW ONLY;这条语句会先按sal降序排列再取排序后的第一行。注意FETCH FIRST是跟在ORDER BY后面的它在语义上天然排在排序之后不会像ROWNUM那样出现顺序错乱的问题。FETCH FIRST还支持几个扩展用法-- 取前10行 SELECT * FROM emp FETCH FIRST 10 ROWS ONLY; -- 取并列第一的所有行 SELECT * FROM emp ORDER BY sal DESC FETCH FIRST 1 ROW WITH TIES; -- 从第11行开始取10行等价于分页 SELECT * FROM emp ORDER BY sal DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;WITH TIES是相当实用的语法如果排序后有并列第一它会把所有并列行都返回而不只是随便挑一条。这解决了业务上“取最大值所有记录”的需求换成ROWNUM要额外写子查询判断。有人问FETCH FIRST 1 ROW ONLY和FETCH FIRST 1 ROWS ONLY有什么区别其实没有区别Oracle都支持无非单复数。写习惯了都行。2.3 窗口函数ROW_NUMBER()要拿每组第一行时选它ROWNUM和FETCH FIRST都只能取整体结果集的第一行。业务上真正高频的是“每个组里的第一行”例如每个部门工资最高的员工、每个用户最近的一条订单。这种需求得用窗口函数。SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rn FROM emp t ) WHERE rn 1;窗口函数ROW_NUMBER()会给每个PARTITION BY分组内的行按ORDER BY排序并从1开始编号。外层再过滤rn 1就拿到了每个组内的第一行。这里PARTITION BY deptno就是“按部门分组”ORDER BY sal DESC是“组内按工资降序”。如果要取并列第一可以把ROW_NUMBER()换成RANK()或DENSE_RANK()。区别在于RANK()遇到并列会跳号比如两个并列第一下一个就是第三名DENSE_RANK()不跳号下一个还是第二名。取分组最大值时RANK()更符合直觉因为它能把所有并列最大都返回。窗口函数实现取第一行的本质是全表排序后再计算。表数据量大时开销很高除非必要否则不如用KEEP分析函数或改写关联查询。但分组取第一行这个需求本身就无法用简单ROWNUM替代所以窗口函数是绕不开的工具。2.4 ROWID物理位置最快但别指望顺序ROWID是Oracle内部用来定位数据行物理存储位置的伪列格式类似AAAR3qAAEAAAACHAAB包含数据文件编号、数据块编号、行槽编号。你可以把它理解成每行数据在磁盘上的门牌号。利用ROWID可以取到“物理上第一行”SELECT * FROM emp WHERE ROWID (SELECT MIN(ROWID) FROM emp);MIN(ROWID)在逻辑上会拿到所有行ROWID中最小的那个也就是物理存储位置最早的那一行。这条语句不需要排序它的执行计划往往能走到索引全扫描的最小/最大值优化非常快。我为什么会用它一般是在调试场景里比如怀疑某张表的数据错乱、想快速随便看一行、或者想确认某行数据到底在哪个数据块上。日常业务SQL中不建议依赖ROWID原因有三个ROWID不具有业务含义。它只代表物理位置和订单号、客户ID没有任何关系。ROWID会变。表做过ALTER TABLE MOVE、导出导入、分区维护之后行的ROWID可能完全改变。长期把它存在业务表里当定位标识迟早会踩坑。不同数据库平台不通用。MySQL、SQL Server没有这个伪列写了就废。2.5 MIN/MAX函数本质是取极值行不是“第一行”有一类写法很容易混淆概念就是用聚合函数取极值再回表查整行SELECT * FROM emp WHERE empno (SELECT MIN(empno) FROM emp);这里子查询先算出最小的员工编号外层再按编号过滤结果就是编号最小的整行数据。看起来像“取第一行”实际是“取主键极值对应的行”。这个写法的特点是主键或唯一索引上执行MIN()时Oracle有专门的优化路径叫INDEX FULL SCAN (MIN/MAX)不需要扫描整个索引。如果业务场景恰好是按主键取最早或最新一行这个写法通常比ORDER BY FETCH FIRST更快因为后者必须完整排序而前者只用索引定位极值。但是要记住MIN(ROWID)和MIN(主键)都只能处理“单列极值”不能处理复合排序条件比如“按客户分组取最新订单”这类需求就必须回到窗口函数或分页写法。3. 实操演示一个订单表跑通全部写法3.1 先建一张测试表纸上谈兵没意思我直接模拟一张订单表把上面几种写法挨个跑一遍。先建表和插入数据CREATE TABLE demo_order ( order_id NUMBER PRIMARY KEY, customer_name VARCHAR2(50), order_amount NUMBER(10,2), order_date DATE ); INSERT INTO demo_order VALUES (1, 张三, 100, DATE 2024-01-05); INSERT INTO demo_order VALUES (2, 李四, 250, DATE 2024-01-03); INSERT INTO demo_order VALUES (3, 王五, 180, DATE 2024-01-07); INSERT INTO demo_order VALUES (4, 张三, 300, DATE 2024-01-01); INSERT INTO demo_order VALUES (5, 李四, 250, DATE 2024-01-06); INSERT INTO demo_order VALUES (6, 赵六, 90, NULL); COMMIT;我故意插入了三样东西一个NULL日期、两笔金额相同的订单250、客户有重复。这三样东西能帮你看清不同写法之间的差异也能复现排序陷阱。3.2 挨个跑一遍SQL、返回结果和执行情况第一种ROWNUM取任意一行SELECT * FROM demo_order WHERE ROWNUM 1;执行结果返回order_id1这一行。这是在物理扫描顺序下拿到的第一行。它不代表业务上的“最早订单”但如果你只是想确认表里有没有数据这条语句几毫秒就完成。第二种ROWNUM配合子查询取金额最高那行SELECT * FROM ( SELECT * FROM demo_order ORDER BY order_amount DESC ) WHERE ROWNUM 1;执行结果返回order_id4金额300。这才是“金额最高的订单”。注意如果按order_amount DESC排序Oracle默认会认为NULL最大意味着某行如果金额是NULL它会被排到最前面。如果业务不需要NULL记得加WHERE order_amount IS NOT NULL或者用NULLS LAST。第三种FETCH FIRST取金额最高那行SELECT * FROM demo_order ORDER BY order_amount DESC FETCH FIRST 1 ROW ONLY;返回结果同样是order_id4。第一眼看起来和第二种没差但它多了个优势可以直接加WITH TIES把所有并列金额都带出来。SELECT * FROM demo_order ORDER BY order_amount DESC FETCH FIRST 1 ROW WITH TIES;这条会返回两个结果order_id2和order_id5金额都是250。这里其实是因为WITH TIES要判断排序字段完全一致的行与第二条并列。而如果按ORDER BY order_amount DESC排序最高是300所以只会返回order_id4。我把并列数据放在250的目的是演示ORDER BY order_amount DESC取最大但要说清楚用FETCH FIRST 1 ROW WITH TIES时如果真有并列最大会有多行返回这个特性在分组统计排名时特别有用。如果只想取任意一个并列行就别加WITH TIES。第四种窗口函数取客户张三的最新订单SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (PARTITION BY customer_name ORDER BY order_date DESC) AS rn FROM demo_order t ) WHERE rn 1 AND customer_name 张三;返回order_id1这是张三的最新一笔订单。如果把外部过滤去掉会得到每个客户的最新订单一共四行。第五种ROWID取物理第一行SELECT * FROM demo_order WHERE ROWID (SELECT MIN(ROWID) FROM demo_order);返回order_id1。看起来和第一种结果一样但原理完全不同第一种是“取第一条就停”这一种是“算出最小ROWID再定位”。3.3 执行计划里看效率差异写完SQL一定要看执行计划不然你根本不知道Oracle实际做了什么。用SQL*Plus或PL/SQL Developer执行SET AUTOTRACE ON;然后再跑每条语句。重点看三个关键字COUNT STOPKEYOracle找到需要的行数就停止扫描。没有排序的ROWNUM 1会走这个效率最高适合“快速看数据”。SORT ORDER BY STOPKEY这是先排序、再取前N行的标志。第二种和第三种写法都会出现意味着Oracle要先把全表数据排序再停止取数IO开销比第一种大很多。INDEX FULL SCAN (MIN/MAX)第五种写法如果走主键索引会出现这个。扫描索引找最小/最大ROWID不需要访问具体数据行速度也很快。实战中的选择逻辑很简单只求任意一行直接用ROWNUM 1要求业务上排序取第一优先看ORDER BY字段上有没有索引有索引同时用FETCH FIRST没有索引又着急考虑能不能改成MIN/MAX子查询。4. 真实业务中常见问题与排查技巧实录4.1 常见报错和现象速查表现象原因解决办法写LIMIT 1报ORA-00933Oracle不支持LIMIT换成ROWNUM 1或FETCH FIRST 1 ROW ONLYWHERE ROWNUM 2返回0行ROWNUM分配机制导致等值条件失效用ROWNUM 2或配合子查询排序后取第一行结果不是业务期望的值把ROWNUM直接和ORDER BY混在一起写先子查询排序再外层取ROWNUM或直接用FETCH FIRST分页数据重复或丢失ORDER BY字段有重复值在ORDER BY里加唯一字段如ORDER BY order_date DESC, order_id DESC排序后NULL值跑到最前面Oracle默认DESC时NULL最大加NULLS LAST或先在子查询里过滤NULLFETCH FIRST语法报错版本低于12c或语法写错确认版本老版本用ROWNUM子查询方案字段重名导致ORA-00918子查询和多表关联时列名重复给列加别名或表前缀逐条展开说一下。ORA-00933是最常见的情况就是MySQL习惯没改过来。ORA-00918常见于内联视图取第一行时内层表和外层主查询有同名字段Oracle不知道该取哪一个给列起个别名最简单。ROWNUM 2返回0行我在前面原理部分讲过真的遇到了别慌用替代等值就好。分页数据重复十有八九是因为ORDER BY的字段不唯一排序不稳定每次执行计划换一下边界行可能就被重复或漏掉加个主键字段做次级排序就能解决。还有个容易忽略的细节FETCH FIRST 1 ROW ONLY在有并列值时会随机选一条不会稳定返回同一条。拿这种SQL做系统间数据比对可能两边跑出来的结果不一样。要稳定结果必须ORDER BY唯一字段或者用WITH TIES把所有并列行都返回。4.2 更新和删除“第一行”的正确姿势光查询第一行不稀奇业务里更常见的是“把最早待处理的任务取出来处理掉”这种更新/删除场景。Oracle里不允许直接UPDATE ... ORDER BY ... LIMIT 1必须借助子查询。我常用的写法是这样UPDATE task SET status PROCESSING WHERE task_id ( SELECT task_id FROM ( SELECT task_id FROM task WHERE status PENDING ORDER BY create_time ASC ) WHERE ROWNUM 1 );删除最早的记录写法类似DELETE FROM task WHERE task_id ( SELECT task_id FROM ( SELECT task_id FROM task WHERE status PENDING ORDER BY create_time ASC ) WHERE ROWNUM 1 );需要注意这一步实际上是三条SQL内层排序取ID外层再操作。如果多个人同时跑这个逻辑可能会取到同一条任务导致重复处理。Oracle的大并发任务拿数场景我更推荐用SELECT ... FOR UPDATE SKIP LOCKED它会在锁定行时跳过已经被别人锁住的行天然解决了并发抢任务的问题。SELECT task_id FROM task WHERE status PENDING ORDER BY create_time ASC FOR UPDATE SKIP LOCKED FETCH FIRST 1 ROW ONLY;这个写法要12c以上老版本得配合ROWNUM子查询。但核心经验是任务表取第一行的排序字段一定要加索引否则大量并发时全表排序会成为瓶颈。4.3 大表场景下“查第一行”怎么优化项目里动不动就是几千万行的表。在这种规模下取第一行无脑写ORDER BY ... FETCH FIRST是很危险的因为排序可能把临时表空间都撑爆。分三种情况处理。只想确认表里有没有数据用EXISTS或ROWNUM1SELECT 1 FROM demo_order WHERE ROWNUM 1;执行计划会用COUNT STOPKEY扫描到第一行就停下几乎瞬间完成。取主键的极值对应的行用MIN/MAX子查询SELECT * FROM demo_order WHERE order_id (SELECT MIN(order_id) FROM demo_order);只要主键索引存在就是一个索引最小/最大扫描完全不会全表扫描速度极快。按业务字段排序取第一行比如“创建时间最早的订单”SELECT * FROM ( SELECT * FROM demo_order ORDER BY create_time ASC ) WHERE ROWNUM 1;如果create_time上没有索引Oracle只能全表扫描后排序这时不管外面怎么写性能瓶颈都在排序上。必须在create_time字段建一个普通索引让Oracle走到INDEX FULL SCAN或者INDEX RANGE SCAN就能直接按顺序取数据。5. 扩展一步从“第一行”到“前N行”和分页5.1 Oracle经典三层分页写法取前10行和分页接口本质都是“取前N行”的变体。Oracle 12c之前没有OFFSET分页要写成三层嵌套子查询SELECT * FROM ( SELECT t.*, ROWNUM AS rn FROM ( SELECT * FROM demo_order ORDER BY order_date DESC, order_id DESC ) t WHERE ROWNUM 20 ) WHERE rn 10;为什么是三层先说结论内层排序中层取前20并编号外层再过滤出第11到第20行。少一层都不行。如果你只写两层在排序后的结果集上直接过滤ROWNUM BETWEEN 11 AND 20会返回空原因和ROWNUM 2一样ROWNUM编号从1开始生成第1行不满足条件就全丢了。这个写法性能已经不错了ROWNUM 20会让Oracle只保留20行再编号不会把整个表都编号。但如果页码很大比如ROWNUM 100020Oracle还是要扫描前面10万行才能定位到第10万行这属于分页方案的固有问题。5.2 12c的OFFSET FETCH写法与局限12c以后可以写得简洁很多SELECT * FROM demo_order ORDER BY order_date DESC, order_id DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;这条语句的意思一目了然跳过10行取接下来10行。但千万不要以为OFFSET写起来简单、性能就一定比三层好。Oracle为了实现OFFSET 1000000 ROWS FETCH NEXT 10 ROWS ONLY一样要扫描并丢弃一百万行这个过程完全不会省。大数据量深分页三层写法和OFFSET都不算优雅解决方案。5.3 千万级大表分页替代方案键集分页大表分页我更建议键集分页Keyset Pagination也叫Seek Method。核心思路是不用页码而是记住上一页最后一条数据的位置用主键条件直接过滤。SELECT * FROM demo_order WHERE order_id 100 ORDER BY order_id ASC FETCH NEXT 20 ROWS ONLY;第一页传order_id 0拿到最大值后第二页传order_id 上次最大值以此类推。这种方式分页越深越快因为每个查询只读取20条不需要扫描100万行。代价是无法直接跳转到第100页适合“下一页”式的业务不适应页码跳转。这个方案在几千万行的大表上实测效果非常明显能把分页接口从几百毫秒压到几十毫秒前提是主键有序、排序稳定。6. 一点小的实操体会做了这么多年Oracle开发和优化取第一行这事的核心其实就一句话先想清楚业务到底要“任意一行”还是“排序后的第一行”再决定写法。我自己的习惯是验证数据用SELECT * FROM demo_order WHERE ROWNUM 1业务取最大值、最早记录12c以上直接FETCH FIRST12c以下套子查询分组取第一行用窗口函数大表取极值优先MIN/MAX子查询分页接口在大表上坚决不用深OFFSET改用键集分页。还有个小技巧分享给大家写完SQL别急着跑业务随手加一句SET AUTOTRACE ON看一眼执行计划。只要看到了COUNT STOPKEY说明Oracle挺聪明知道提前停下来看到SORT ORDER BY STOPKEY也别慌确认一下排序列有没有索引就行。取第一行难度不大坑基本都在排序和伪列机制里把这些摸透了后面写存储过程、报表查询、接口分页都会顺手很多。
Oracle取第一行数据的五种写法：从ROWNUM到FETCH FIRST
在Oracle里跑一句“只取表里的第一行数据”几乎是所有Oracle开发新人绕不开的第一道坎。MySQL里一行LIMIT 1就完事了换成Oracle直接报ORA-00933因为Oracle压根不认识LIMIT去翻解决方案又会看到ROWNUM、FETCH FIRST、ROW_NUMBER、ROWID一堆名词同一个需求能翻出七八种写法有的写法甚至返回空。这篇文章就把这些写法彻底理一遍每种方案的SQL长什么样、底层原理是什么、适合什么场景、性能和坑在哪里也会给出一份可以直接抄的实操演示。适合刚从MySQL转过来的开发、写报表和分页接口的工程师也包括需要快速验证数据的DBA或运维。1. 先搞清楚Oracle里的“第一行”到底指什么1.1 为什么Oracle没有MySQL那种LIMITMySQL的LIMIT 1用习惯了会觉得“取第一行”是个特别自然的需求。但Oracle的SQL引擎从设计之初就没有LIMIT这个概念它提供的是一个叫ROWNUM的伪列。ROWNUM不是表里真实存在的一列它是在查询结果生成过程中Oracle按顺序给每一行分配的一个临时编号第一行是1第二行是2以此类推。关键点在于这个编号是“先分配、后过滤”的而且是在ORDER BY排序之前就分配完了。所以你和ORDER BY一起写的时候ROWNUM编号对应的是“排序前的物理顺序”不是“排序后的业务顺序”。还有一个隐藏问题Oracle默认的普通表是堆表Heap Table数据按插入顺序放在数据块里但没有“行号”这种稳定逻辑概念。准确地说物理上确实存在插入的第一行但你要把它找出来得靠ROWID而不是靠SQL的ORDER BY。业务上常说的“取第一行”绝大多数场景其实是想取“排序后的第一行”也就是最大值、最早记录、最新状态这类数据。1.2 业务里常见的三种“第一行”口径我接手过不少取第一行的需求归纳下来基本是下面三类。第一类任意一行。就是想看下表有没有数据、字段大概什么值。这种场景不关心是哪一行只要是表里的一行就行最常见的写法是SELECT * FROM t WHERE ROWNUM 1效率极高。第二类排序后的第一行。比如“订单金额最高的那条”“创建时间最早的那条”“最新状态的那条”。这种场景必须显式ORDER BY然后再在外面套一层ROWNUM过滤或者用12c以后才有的FETCH FIRST语法。第三类分组内的第一行。比如“每个部门工资最高的人”“每个用户最近一次登录记录”。这类需求要用窗口函数ROW_NUMBER()配合PARTITION BY实现单独用ROWNUM是做不了的。前两类是本文重点第三类属于扩展我会一并演示。1.3 哪种写法最适合你用先说结论不是每种写法都值得记但每种都是特定场合下的最优解。我做了个速查表后面每个写法都会展开讲。方案核心语法适用场景排序后取第一行是否可用主要注意点ROWNUMWHERE ROWNUM 1任何版本、任意一行、分页需要子查询配合别写ROWNUM 1以外的等值条件FETCH FIRSTFETCH FIRST 1 ROW ONLY12c及以后、最简洁可以直接配合ORDER BY老版本用不了窗口函数ROW_NUMBER() OVER (...)分组取第一行、去重本身就能排序慎用RANK取并列行ROWIDWHERE ROWID (SELECT MIN(ROWID) FROM t)调试、快速定位物理行不涉及排序表迁移后ROWID会变MIN/MAX子查询WHERE 主键 (SELECT MIN(主键) FROM t)取主键极值对应的行可用但只限主键需要唯一字段配合2. 五种取第一行的核心技术写法与原理2.1 ROWNUM最经典、也最容易翻车ROWNUM绝对是Oracle里最常用的取行方式但也是翻车率最高的。基础语法就一句SELECT * FROM emp WHERE ROWNUM 1;这条语句执行时Oracle从表中取第一行分配ROWNUM1然后判断ROWNUM 1成立返回。接着取第二行分配ROWNUM2判断2 1不成立丢弃。第三行同样分配3然后丢弃以此类推。所以结果就是第一条取到的行。很多人会顺手写WHERE ROWNUM 2想取第二行结果返回空。原因就是第一行分配ROWNUM1时就不满足ROWNUM2被丢弃第二行仍然从ROWNUM1开始编号还是不满足又被丢弃。只要第1行不满足条件后面所有行都排不上号。再讲讲ORDER BY的配合问题。直接写SELECT * FROM emp WHERE ROWNUM 1 ORDER BY sal DESC是错误的业务逻辑因为ROWNUM是在排序前分配的这行先被选出来然后才排序最终返回的是“表中物理第一条”完全不是你要的“工资最高那条”。正确写法是套子查询先排序再过滤SELECT * FROM ( SELECT * FROM emp ORDER BY sal DESC ) WHERE ROWNUM 1;这个写法就是Oracle 12c之前的“标准答案”。内层先排序外层再取第一行。排序后的结果作为临时结果集ROWNUM才是按业务排序后的顺序编号。性能上不带ORDER BY、只取一行时ROWNUM是效率最高的写法因为Oracle找到第一个满足条件的行就会停下来执行计划里会出现COUNT STOPKEY。但一旦内层有排序排序的开销跑不掉需要重点看排序列上有没有索引。2.2 FETCH FIRST12c以后最优雅的答案Oracle 12c推出了FETCH FIRST语法终于不用再套子查询了。这是我最推荐给新人的写法逻辑清楚、可读性好。SELECT * FROM emp FETCH FIRST 1 ROW ONLY;如果要按某个字段排序取第一行SELECT * FROM emp ORDER BY sal DESC FETCH FIRST 1 ROW ONLY;这条语句会先按sal降序排列再取排序后的第一行。注意FETCH FIRST是跟在ORDER BY后面的它在语义上天然排在排序之后不会像ROWNUM那样出现顺序错乱的问题。FETCH FIRST还支持几个扩展用法-- 取前10行 SELECT * FROM emp FETCH FIRST 10 ROWS ONLY; -- 取并列第一的所有行 SELECT * FROM emp ORDER BY sal DESC FETCH FIRST 1 ROW WITH TIES; -- 从第11行开始取10行等价于分页 SELECT * FROM emp ORDER BY sal DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;WITH TIES是相当实用的语法如果排序后有并列第一它会把所有并列行都返回而不只是随便挑一条。这解决了业务上“取最大值所有记录”的需求换成ROWNUM要额外写子查询判断。有人问FETCH FIRST 1 ROW ONLY和FETCH FIRST 1 ROWS ONLY有什么区别其实没有区别Oracle都支持无非单复数。写习惯了都行。2.3 窗口函数ROW_NUMBER()要拿每组第一行时选它ROWNUM和FETCH FIRST都只能取整体结果集的第一行。业务上真正高频的是“每个组里的第一行”例如每个部门工资最高的员工、每个用户最近的一条订单。这种需求得用窗口函数。SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rn FROM emp t ) WHERE rn 1;窗口函数ROW_NUMBER()会给每个PARTITION BY分组内的行按ORDER BY排序并从1开始编号。外层再过滤rn 1就拿到了每个组内的第一行。这里PARTITION BY deptno就是“按部门分组”ORDER BY sal DESC是“组内按工资降序”。如果要取并列第一可以把ROW_NUMBER()换成RANK()或DENSE_RANK()。区别在于RANK()遇到并列会跳号比如两个并列第一下一个就是第三名DENSE_RANK()不跳号下一个还是第二名。取分组最大值时RANK()更符合直觉因为它能把所有并列最大都返回。窗口函数实现取第一行的本质是全表排序后再计算。表数据量大时开销很高除非必要否则不如用KEEP分析函数或改写关联查询。但分组取第一行这个需求本身就无法用简单ROWNUM替代所以窗口函数是绕不开的工具。2.4 ROWID物理位置最快但别指望顺序ROWID是Oracle内部用来定位数据行物理存储位置的伪列格式类似AAAR3qAAEAAAACHAAB包含数据文件编号、数据块编号、行槽编号。你可以把它理解成每行数据在磁盘上的门牌号。利用ROWID可以取到“物理上第一行”SELECT * FROM emp WHERE ROWID (SELECT MIN(ROWID) FROM emp);MIN(ROWID)在逻辑上会拿到所有行ROWID中最小的那个也就是物理存储位置最早的那一行。这条语句不需要排序它的执行计划往往能走到索引全扫描的最小/最大值优化非常快。我为什么会用它一般是在调试场景里比如怀疑某张表的数据错乱、想快速随便看一行、或者想确认某行数据到底在哪个数据块上。日常业务SQL中不建议依赖ROWID原因有三个ROWID不具有业务含义。它只代表物理位置和订单号、客户ID没有任何关系。ROWID会变。表做过ALTER TABLE MOVE、导出导入、分区维护之后行的ROWID可能完全改变。长期把它存在业务表里当定位标识迟早会踩坑。不同数据库平台不通用。MySQL、SQL Server没有这个伪列写了就废。2.5 MIN/MAX函数本质是取极值行不是“第一行”有一类写法很容易混淆概念就是用聚合函数取极值再回表查整行SELECT * FROM emp WHERE empno (SELECT MIN(empno) FROM emp);这里子查询先算出最小的员工编号外层再按编号过滤结果就是编号最小的整行数据。看起来像“取第一行”实际是“取主键极值对应的行”。这个写法的特点是主键或唯一索引上执行MIN()时Oracle有专门的优化路径叫INDEX FULL SCAN (MIN/MAX)不需要扫描整个索引。如果业务场景恰好是按主键取最早或最新一行这个写法通常比ORDER BY FETCH FIRST更快因为后者必须完整排序而前者只用索引定位极值。但是要记住MIN(ROWID)和MIN(主键)都只能处理“单列极值”不能处理复合排序条件比如“按客户分组取最新订单”这类需求就必须回到窗口函数或分页写法。3. 实操演示一个订单表跑通全部写法3.1 先建一张测试表纸上谈兵没意思我直接模拟一张订单表把上面几种写法挨个跑一遍。先建表和插入数据CREATE TABLE demo_order ( order_id NUMBER PRIMARY KEY, customer_name VARCHAR2(50), order_amount NUMBER(10,2), order_date DATE ); INSERT INTO demo_order VALUES (1, 张三, 100, DATE 2024-01-05); INSERT INTO demo_order VALUES (2, 李四, 250, DATE 2024-01-03); INSERT INTO demo_order VALUES (3, 王五, 180, DATE 2024-01-07); INSERT INTO demo_order VALUES (4, 张三, 300, DATE 2024-01-01); INSERT INTO demo_order VALUES (5, 李四, 250, DATE 2024-01-06); INSERT INTO demo_order VALUES (6, 赵六, 90, NULL); COMMIT;我故意插入了三样东西一个NULL日期、两笔金额相同的订单250、客户有重复。这三样东西能帮你看清不同写法之间的差异也能复现排序陷阱。3.2 挨个跑一遍SQL、返回结果和执行情况第一种ROWNUM取任意一行SELECT * FROM demo_order WHERE ROWNUM 1;执行结果返回order_id1这一行。这是在物理扫描顺序下拿到的第一行。它不代表业务上的“最早订单”但如果你只是想确认表里有没有数据这条语句几毫秒就完成。第二种ROWNUM配合子查询取金额最高那行SELECT * FROM ( SELECT * FROM demo_order ORDER BY order_amount DESC ) WHERE ROWNUM 1;执行结果返回order_id4金额300。这才是“金额最高的订单”。注意如果按order_amount DESC排序Oracle默认会认为NULL最大意味着某行如果金额是NULL它会被排到最前面。如果业务不需要NULL记得加WHERE order_amount IS NOT NULL或者用NULLS LAST。第三种FETCH FIRST取金额最高那行SELECT * FROM demo_order ORDER BY order_amount DESC FETCH FIRST 1 ROW ONLY;返回结果同样是order_id4。第一眼看起来和第二种没差但它多了个优势可以直接加WITH TIES把所有并列金额都带出来。SELECT * FROM demo_order ORDER BY order_amount DESC FETCH FIRST 1 ROW WITH TIES;这条会返回两个结果order_id2和order_id5金额都是250。这里其实是因为WITH TIES要判断排序字段完全一致的行与第二条并列。而如果按ORDER BY order_amount DESC排序最高是300所以只会返回order_id4。我把并列数据放在250的目的是演示ORDER BY order_amount DESC取最大但要说清楚用FETCH FIRST 1 ROW WITH TIES时如果真有并列最大会有多行返回这个特性在分组统计排名时特别有用。如果只想取任意一个并列行就别加WITH TIES。第四种窗口函数取客户张三的最新订单SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (PARTITION BY customer_name ORDER BY order_date DESC) AS rn FROM demo_order t ) WHERE rn 1 AND customer_name 张三;返回order_id1这是张三的最新一笔订单。如果把外部过滤去掉会得到每个客户的最新订单一共四行。第五种ROWID取物理第一行SELECT * FROM demo_order WHERE ROWID (SELECT MIN(ROWID) FROM demo_order);返回order_id1。看起来和第一种结果一样但原理完全不同第一种是“取第一条就停”这一种是“算出最小ROWID再定位”。3.3 执行计划里看效率差异写完SQL一定要看执行计划不然你根本不知道Oracle实际做了什么。用SQL*Plus或PL/SQL Developer执行SET AUTOTRACE ON;然后再跑每条语句。重点看三个关键字COUNT STOPKEYOracle找到需要的行数就停止扫描。没有排序的ROWNUM 1会走这个效率最高适合“快速看数据”。SORT ORDER BY STOPKEY这是先排序、再取前N行的标志。第二种和第三种写法都会出现意味着Oracle要先把全表数据排序再停止取数IO开销比第一种大很多。INDEX FULL SCAN (MIN/MAX)第五种写法如果走主键索引会出现这个。扫描索引找最小/最大ROWID不需要访问具体数据行速度也很快。实战中的选择逻辑很简单只求任意一行直接用ROWNUM 1要求业务上排序取第一优先看ORDER BY字段上有没有索引有索引同时用FETCH FIRST没有索引又着急考虑能不能改成MIN/MAX子查询。4. 真实业务中常见问题与排查技巧实录4.1 常见报错和现象速查表现象原因解决办法写LIMIT 1报ORA-00933Oracle不支持LIMIT换成ROWNUM 1或FETCH FIRST 1 ROW ONLYWHERE ROWNUM 2返回0行ROWNUM分配机制导致等值条件失效用ROWNUM 2或配合子查询排序后取第一行结果不是业务期望的值把ROWNUM直接和ORDER BY混在一起写先子查询排序再外层取ROWNUM或直接用FETCH FIRST分页数据重复或丢失ORDER BY字段有重复值在ORDER BY里加唯一字段如ORDER BY order_date DESC, order_id DESC排序后NULL值跑到最前面Oracle默认DESC时NULL最大加NULLS LAST或先在子查询里过滤NULLFETCH FIRST语法报错版本低于12c或语法写错确认版本老版本用ROWNUM子查询方案字段重名导致ORA-00918子查询和多表关联时列名重复给列加别名或表前缀逐条展开说一下。ORA-00933是最常见的情况就是MySQL习惯没改过来。ORA-00918常见于内联视图取第一行时内层表和外层主查询有同名字段Oracle不知道该取哪一个给列起个别名最简单。ROWNUM 2返回0行我在前面原理部分讲过真的遇到了别慌用替代等值就好。分页数据重复十有八九是因为ORDER BY的字段不唯一排序不稳定每次执行计划换一下边界行可能就被重复或漏掉加个主键字段做次级排序就能解决。还有个容易忽略的细节FETCH FIRST 1 ROW ONLY在有并列值时会随机选一条不会稳定返回同一条。拿这种SQL做系统间数据比对可能两边跑出来的结果不一样。要稳定结果必须ORDER BY唯一字段或者用WITH TIES把所有并列行都返回。4.2 更新和删除“第一行”的正确姿势光查询第一行不稀奇业务里更常见的是“把最早待处理的任务取出来处理掉”这种更新/删除场景。Oracle里不允许直接UPDATE ... ORDER BY ... LIMIT 1必须借助子查询。我常用的写法是这样UPDATE task SET status PROCESSING WHERE task_id ( SELECT task_id FROM ( SELECT task_id FROM task WHERE status PENDING ORDER BY create_time ASC ) WHERE ROWNUM 1 );删除最早的记录写法类似DELETE FROM task WHERE task_id ( SELECT task_id FROM ( SELECT task_id FROM task WHERE status PENDING ORDER BY create_time ASC ) WHERE ROWNUM 1 );需要注意这一步实际上是三条SQL内层排序取ID外层再操作。如果多个人同时跑这个逻辑可能会取到同一条任务导致重复处理。Oracle的大并发任务拿数场景我更推荐用SELECT ... FOR UPDATE SKIP LOCKED它会在锁定行时跳过已经被别人锁住的行天然解决了并发抢任务的问题。SELECT task_id FROM task WHERE status PENDING ORDER BY create_time ASC FOR UPDATE SKIP LOCKED FETCH FIRST 1 ROW ONLY;这个写法要12c以上老版本得配合ROWNUM子查询。但核心经验是任务表取第一行的排序字段一定要加索引否则大量并发时全表排序会成为瓶颈。4.3 大表场景下“查第一行”怎么优化项目里动不动就是几千万行的表。在这种规模下取第一行无脑写ORDER BY ... FETCH FIRST是很危险的因为排序可能把临时表空间都撑爆。分三种情况处理。只想确认表里有没有数据用EXISTS或ROWNUM1SELECT 1 FROM demo_order WHERE ROWNUM 1;执行计划会用COUNT STOPKEY扫描到第一行就停下几乎瞬间完成。取主键的极值对应的行用MIN/MAX子查询SELECT * FROM demo_order WHERE order_id (SELECT MIN(order_id) FROM demo_order);只要主键索引存在就是一个索引最小/最大扫描完全不会全表扫描速度极快。按业务字段排序取第一行比如“创建时间最早的订单”SELECT * FROM ( SELECT * FROM demo_order ORDER BY create_time ASC ) WHERE ROWNUM 1;如果create_time上没有索引Oracle只能全表扫描后排序这时不管外面怎么写性能瓶颈都在排序上。必须在create_time字段建一个普通索引让Oracle走到INDEX FULL SCAN或者INDEX RANGE SCAN就能直接按顺序取数据。5. 扩展一步从“第一行”到“前N行”和分页5.1 Oracle经典三层分页写法取前10行和分页接口本质都是“取前N行”的变体。Oracle 12c之前没有OFFSET分页要写成三层嵌套子查询SELECT * FROM ( SELECT t.*, ROWNUM AS rn FROM ( SELECT * FROM demo_order ORDER BY order_date DESC, order_id DESC ) t WHERE ROWNUM 20 ) WHERE rn 10;为什么是三层先说结论内层排序中层取前20并编号外层再过滤出第11到第20行。少一层都不行。如果你只写两层在排序后的结果集上直接过滤ROWNUM BETWEEN 11 AND 20会返回空原因和ROWNUM 2一样ROWNUM编号从1开始生成第1行不满足条件就全丢了。这个写法性能已经不错了ROWNUM 20会让Oracle只保留20行再编号不会把整个表都编号。但如果页码很大比如ROWNUM 100020Oracle还是要扫描前面10万行才能定位到第10万行这属于分页方案的固有问题。5.2 12c的OFFSET FETCH写法与局限12c以后可以写得简洁很多SELECT * FROM demo_order ORDER BY order_date DESC, order_id DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;这条语句的意思一目了然跳过10行取接下来10行。但千万不要以为OFFSET写起来简单、性能就一定比三层好。Oracle为了实现OFFSET 1000000 ROWS FETCH NEXT 10 ROWS ONLY一样要扫描并丢弃一百万行这个过程完全不会省。大数据量深分页三层写法和OFFSET都不算优雅解决方案。5.3 千万级大表分页替代方案键集分页大表分页我更建议键集分页Keyset Pagination也叫Seek Method。核心思路是不用页码而是记住上一页最后一条数据的位置用主键条件直接过滤。SELECT * FROM demo_order WHERE order_id 100 ORDER BY order_id ASC FETCH NEXT 20 ROWS ONLY;第一页传order_id 0拿到最大值后第二页传order_id 上次最大值以此类推。这种方式分页越深越快因为每个查询只读取20条不需要扫描100万行。代价是无法直接跳转到第100页适合“下一页”式的业务不适应页码跳转。这个方案在几千万行的大表上实测效果非常明显能把分页接口从几百毫秒压到几十毫秒前提是主键有序、排序稳定。6. 一点小的实操体会做了这么多年Oracle开发和优化取第一行这事的核心其实就一句话先想清楚业务到底要“任意一行”还是“排序后的第一行”再决定写法。我自己的习惯是验证数据用SELECT * FROM demo_order WHERE ROWNUM 1业务取最大值、最早记录12c以上直接FETCH FIRST12c以下套子查询分组取第一行用窗口函数大表取极值优先MIN/MAX子查询分页接口在大表上坚决不用深OFFSET改用键集分页。还有个小技巧分享给大家写完SQL别急着跑业务随手加一句SET AUTOTRACE ON看一眼执行计划。只要看到了COUNT STOPKEY说明Oracle挺聪明知道提前停下来看到SORT ORDER BY STOPKEY也别慌确认一下排序列有没有索引就行。取第一行难度不大坑基本都在排序和伪列机制里把这些摸透了后面写存储过程、报表查询、接口分页都会顺手很多。
