先問一個看起來很簡單的問題在 Oracle 數(shù)據(jù)庫里怎么查出表中第一行數(shù)據(jù)這個問題我拿來面試過不少人也經(jīng)常在技術(shù)社群里看到有人問。有意思的是能一次答對的不到一半。有的人脫口而出WHERE ROWNUM 1有的人上來就寫LIMIT 1明顯是寫 MySQL 寫慣了還有人直接ORDER BY 1 FETCH FIRST 1 ROW ONLY——語法看著沒錯但放到 11g 的生產(chǎn)庫上直接報錯?！叭〉谝恍小边@個需求做開發(fā)的十有八九都寫過但它背后藏著的 Oracle 底層邏輯和那些反直覺的坑才是真正值得掰開揉碎講清楚的東西。這篇文章就專門圍繞這個問題展開先說最常見的錯誤寫法為什么錯再講 ROWNUM 的底層機(jī)制然后給出不同 Oracle 版本下的正確姿勢和性能優(yōu)化思路最后聊聊隨機(jī)取行、分組取首行這些進(jìn)階場景以及我實際踩過的兩個隱藏比較深的坑。1. “取第一行”看起來簡單第一個坑就翻車先說個典型場景。一張訂單表orders里面有幾百萬行數(shù)據(jù)業(yè)務(wù)方說“給我查一條訂單看看字段長什么樣”。這種需求其實很常見——不是為了精確取哪一條而是快速看一眼表里的數(shù)據(jù)形態(tài)。很多人的第一反應(yīng)是SELECT * FROM orders WHERE ROWNUM 1;這條 SQL 能跑也能返回一行數(shù)據(jù)但它有一個致命的問題你根本不知道返回的是哪一行。這不叫“取第一行”這叫“取任意一行”——Oracle 從表中讀到哪一行ROWNUM 就給哪一行發(fā)號先被讀到的就先拿到 1 號。而“先被讀到的”取決于執(zhí)行計劃怎么掃數(shù)據(jù)。可能是全表掃描的第一行可能是索引掃描命中的第一行沒有任何業(yè)務(wù)上的確定性。另一個更隱蔽的坑是下面這種寫法SELECT * FROM orders WHERE ROWNUM 1 ORDER BY create_time DESC;很多人寫這段代碼的本意是“先按時間倒序排好再拿第一條”但實際執(zhí)行過程完全不是這樣。SQL 的語義順序里WHERE的過濾發(fā)生在ORDER BY排序之前。所以這條 SQL 的真實邏輯是先隨便抓一行抓住的那行編號為 1返回然后排序——排序排的是已經(jīng)被截斷后的那一行排了等于沒排。這個坑我親眼見過有人踩。當(dāng)時一個同事要查“最近創(chuàng)建的一筆訂單”寫了類似上面的 SQL結(jié)果返回的是表里最早的一條記錄。排查了半天最后發(fā)現(xiàn)根本不是數(shù)據(jù)問題是邏輯順序搞反了。再往下說還有一個寫法連語法都過不去SELECT * FROM orders ORDER BY create_time DESC WHERE ROWNUM 1;這個直接在 Oracle 上報 ORA-00933因為ORDER BY必須放在WHERE之后。有 MySQL 習(xí)慣的人特別容易踩這個畢竟 MySQL 里L(fēng)IMIT是放在最后的導(dǎo)致一些朋友誤以為 Oracle 也能把條件寫后面。所以你看光是“取第一行”四個字就能拆出三種完全不同的需求需求描述真實意圖常見錯誤隨便拿一條看看取任意一行以為 ROWNUM1 是確定性的把所有行排完序后取第一條取排序后的首行WHERE 和 ORDER BY 順序搞反取物理存儲上的第一行按塊掃描順序取首行意識到物理順序不可控這里給新手一個最基本的建議寫“取第一行”之前先搞清楚你要的是哪種“第一行”。沒有排序邏輯的第一行在 Oracle 里沒有任何確定性依賴它就是給自己埋雷。2. ROWNUM的偽列機(jī)制為什么必須嵌套子查詢才行要徹底理解上面那些坑就得從 ROWNUM 的底層機(jī)制說起。ROWNUM 是 Oracle 提供的一個偽列它不是一個真實存儲在表中的列而是查詢結(jié)果集生成過程中Oracle 給每一行臨時分配的序號。聽起來很抽象打個比方你就懂了。想象一下你去銀行柜臺辦事。取號機(jī)上出的號就是 ROWNUM。但有個特殊規(guī)則只有你已經(jīng)坐到柜臺前的椅子上叫號器才會給你發(fā)號。如果你排在第 2 位但第一位辦完走了、第二位又沒來那叫號器會一直叫 1 號永遠(yuǎn)不會叫 2 號。在這個規(guī)則下2 號永遠(yuǎn)不可能被叫到——除非 1 號先被處理完。Oracle 的 ROWNUM 就是這套“坐著才發(fā)號”的邏輯Oracle 讀取結(jié)果集第一行給它標(biāo)號 ROWNUM 1然后檢查 WHERE 條件如果條件不成立這一行被丟掉繼續(xù)讀下一行下一行重新標(biāo)號 ROWNUM 1再檢查條件以此類推。所以WHERE ROWNUM 1能返回數(shù)據(jù)是因為第一行檢查時條件成立。但WHERE ROWNUM 2永遠(yuǎn)查不到數(shù)據(jù)因為每一行被讀到的時候都先被編號為 1壓根等不到編號 2 就被條件過濾掉了。同理WHERE ROWNUM 1也永遠(yuǎn)返回空。這也就解釋了為什么必須先嵌套一層子查詢SELECT * FROM ( SELECT * FROM orders ORDER BY create_time DESC ) WHERE ROWNUM 1;執(zhí)行順序是內(nèi)層子查詢先把所有行排序生成一個完整的有序結(jié)果集外層查詢在這個有序結(jié)果集上從頭取第一行。這個結(jié)果集是“已經(jīng)排序完的實體”所以第一行確定就是你要的那條。注意一個細(xì)節(jié)很多人聽說嵌套子查詢后會寫成這樣SELECT * FROM ( SELECT * FROM orders WHERE ROWNUM 1 ORDER BY create_time DESC );把這個寫法和正確寫法對比一下差別就在于ROWNUM 截斷發(fā)生在子查詢內(nèi)部還是外部。上面這種把 ROWNUM 放在內(nèi)層子查詢里的寫法又是“先取任意一行再排序”完全失去了嵌套的意義。理解了這個機(jī)制很多相關(guān)的坑都能一眼看出來。比如有的同學(xué)問“為什么我加了 ROWNUM 1 之后查詢變快了”——因為 Oracle 讀到第一行滿足條件的行后就直接停止繼續(xù)掃描了。這在全表掃描時確實能大幅減少 IO屬于物理上的短路優(yōu)化。順便說一句ROWNUM 和 ROWID 是兩個很容易混淆的概念。ROWID 是行的物理地址表示這行數(shù)據(jù)存在哪個文件的哪個塊的第幾行ROWNUM 是邏輯序號表示這行數(shù)據(jù)在當(dāng)前查詢結(jié)果集中的位置。一個對應(yīng)物理位置一個對應(yīng)邏輯順序用途完全不同排查問題時別搞混。3. 按排序取首行的完整寫法與Oracle版本差異理解了 ROWNUM 機(jī)制后下面把“排序后取首行”的各種寫法完整梳理一遍。日常開發(fā)里90% 以上的“取第一行”都是這個意思——按某個業(yè)務(wù)字段排序取最前的那條。3.1 嵌套子查詢 ROWNUM12c 之前的標(biāo)準(zhǔn)答案SELECT * FROM ( SELECT * FROM orders ORDER BY create_time DESC ) WHERE ROWNUM 1;這是 11g 及更早版本里的標(biāo)準(zhǔn)寫法也是面試?yán)镒钕Ｍ愦鸪鰜淼哪莻€。注意兩個細(xì)節(jié)第一ROWNUM 1和ROWNUM 1在這里等價工程上更推薦 1因為語義上更明確是“取一條”不容易被誤讀。第二內(nèi)層子查詢里建議加上完整的排序條件。比如按時間排序時create_time可能出現(xiàn)相同值這時候最好追加一個唯一鍵做二級排序SELECT * FROM ( SELECT * FROM orders ORDER BY create_time DESC, order_id DESC ) WHERE ROWNUM 1;否則 create_time 相同的情況下返回哪一條又變成不確定的了。3.2 FETCH FIRST ROW ONLY12c 及以后的官方推薦Oracle 從 12c 開始引入了 ANSI 標(biāo)準(zhǔn)的FETCH FIRST子句完全就是為了簡化這種“取前 N 條”的語義而生的SELECT * FROM orders ORDER BY create_time DESC FETCH FIRST 1 ROW ONLY;這個寫法和嵌套子查詢 ROWNUM 在大多數(shù)場景下性能相當(dāng)?shù)勺x性好太多——SQL 從前往后讀先明確排序再明確取幾條非常符合直覺。如果你需要取前 5 條寫法是FETCH FIRST 5 ROW ONLY。如果要取百分之一寫作FETCH FIRST 1 PERCENT ROW ONLY。如果要取第 2 條到第 3 條配合 OFFSETSELECT * FROM orders ORDER BY create_time DESC OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY;這就是 Oracle 分頁的另一種實現(xiàn)方式。提到分頁做開發(fā)的朋友應(yīng)該馬上會聯(lián)想到ROWNUM三層嵌套分頁——那個經(jīng)典寫法其實是本篇文章討論內(nèi)容的直接延伸外層固定總行數(shù)中間層算頁碼偏移最內(nèi)層排序。這里提醒一句如果你的生產(chǎn)庫還在 11gFETCH FIRST會直接報 ORA-00933。遷移老項目代碼時這個坑很常見從 12c 代碼庫往 11g 環(huán)境回遷必須把 FETCH FIRST 改回嵌套子查詢寫法。3.3 ROW_NUMBER() 窗口函數(shù)還有一種寫法用分析函數(shù)SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (ORDER BY create_time DESC) rn FROM orders t ) WHERE rn 1;這個寫法的通用性最強(qiáng)因為ROW_NUMBER()不僅能取整體第一行還能配合PARTITION BY取分組內(nèi)的第一行。但代價是它需要對所有行計算序號然后才能過濾執(zhí)行計劃里通常多一個 WINDOW SORT 步驟性能相比 ROWNUM 截斷要差。三種寫法放在一起對比一下寫法版本要求可讀性性能特征推薦場景嵌套子查詢 ROWNUM全版本中掃描到首行即停老版本兼容、追求性能FETCH FIRST12c最好同樣支持首行截斷新項目首選ROW_NUMBER()全版本中必須全量計算序號分組取首行等復(fù)雜場景從我個人的使用習(xí)慣來說12c 以上的新庫無腦用 FETCH FIRST老庫用嵌套子查詢 ROWNUMROW_NUMBER() 只在需要分組或者需要行號做二次處理時才用。4. 千萬行大表取首行執(zhí)行計劃與索引提案前面講的都是寫法層面的問題實際生產(chǎn)環(huán)境里另一個折磨人的問題就是性能。尤其是“幾千萬行大表”這種場景下取首行SQL 寫對了也可能會跑出讓人崩潰的執(zhí)行時間和 IO 消耗。先說一個結(jié)論如果只是“隨便取一行”WHERE ROWNUM 1加上FIRST_ROWS(n)之類的提示在大表上也能很快返回因為 Oracle 讀到第一行就停了。真正的性能坑集中在“按非索引列排序取首行”這個場景——它必須先把整張表的數(shù)據(jù)讀完、排完序才能找出第一條。舉個實際例子。某張流水表account_flow有 3000 萬行業(yè)務(wù)要查“金額最大的一筆流水”直接寫法SELECT * FROM ( SELECT * FROM account_flow ORDER BY amount DESC ) WHERE ROWNUM 1;我在測試環(huán)境跑過全表掃描加排序耗時接近 40 秒。這個結(jié)果不意外——排序本身要把 3000 萬行的 amount 字段全部讀出來放到臨時表空間排序性能瓶頸在 IO 和排序空間上。優(yōu)化思路有兩個方向。方向一把排序字段做成索引。如果amount上有索引Oracle 可以直接走索引的有序掃描從頭讀第一個索引條目就能拿到最大值。雖然還是 INDEX FULL SCAN但掃描到第一條就停了不會讀完整個索引CREATE INDEX idx_account_flow_amount ON account_flow(amount DESC); SELECT * FROM ( SELECT * FROM account_flow ORDER BY amount DESC ) WHERE ROWNUM 1;建了降序索引之后執(zhí)行計劃會變成 INDEX FULL SCAN (MIN/MAX) 類型的路徑執(zhí)行時間從 40 秒縮短到幾十毫秒級別。這里提醒一句索引建立后別忘了收集統(tǒng)計信息EXEC DBMS_STATS.GATHER_INDEX_STATS(USER, IDX_ACCOUNT_FLOW_AMOUNT);方向二如果業(yè)務(wù)只關(guān)心“最大/最小的某個字段值”而不是完整的一行數(shù)據(jù)直接用聚合函數(shù)更高效。比如只要最大金額是多少SELECT MAX(amount) FROM account_flow;Oracle 對MAX/MIN有專門的優(yōu)化路徑在普通 B 樹索引上做 MIN/MAX 掃描只需要讀兩個索引塊就能拿到結(jié)果連表數(shù)據(jù)都不用碰。這在高并發(fā)場景下是性價比最高的方案。再說一個容易忽略的執(zhí)行計劃細(xì)節(jié)。很多人以為嵌套子查詢里寫了ORDER BY子查詢就會把全部數(shù)據(jù)排完序再交給外層。實際上優(yōu)化器在特定條件下可以做排序消除sort elimination——如果排序字段本身就是索引的有序鍵優(yōu)化器會直接把排序操作省掉改用索引掃描的有序輸出。這也是為什么取首行時執(zhí)行計劃里到底有沒有SORT ORDER BY這一步很重要。用 EXPLAIN PLAN 看一眼EXPLAIN PLAN FOR SELECT * FROM ( SELECT * FROM account_flow ORDER BY amount DESC ) WHERE ROWNUM 1; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);如果執(zhí)行計劃里出現(xiàn)了SORT ORDER BY說明優(yōu)化器老老實實排了序如果顯示的是INDEX FULL SCAN (MIN/MAX)或者直接從索引取數(shù)說明走了優(yōu)化路徑。養(yǎng)成看執(zhí)行計劃的習(xí)慣比死記硬背優(yōu)化規(guī)則靠譜得多。另外涉及大表取首行時還要警惕熱塊競爭。如果業(yè)務(wù)上高頻執(zhí)行“取最新一條”這類查詢所有人都去搶索引最右端那個葉塊就會產(chǎn)生 buffer busy wait。解決辦法通常是反向索引或者減少查詢頻率這屬于另一層級的優(yōu)化話題在這里先提一句供遇到性能問題的朋友排查時參考。5. 隨機(jī)行、分組首行、空表判斷幾個特殊場景前面講的都是“按規(guī)則取第一行”但實際開發(fā)中“取第一行”還有幾個容易被人問起、又容易寫錯的變體我集中放到這一節(jié)講。5.1 隨機(jī)取一行如果業(yè)務(wù)需求是“從表里隨機(jī)抽一條”很多人會寫出SELECT * FROM orders ORDER BY DBMS_RANDOM.VALUE FETCH FIRST 1 ROW ONLY;這個寫法語義上完全沒問題大表上的性能就是另一回事了。DBMS_RANDOM.VALUE會給每一行生成一個隨機(jī)數(shù)然后全量排序代價極大。3000 萬行的大表跑一次這種查詢幾秒鐘是少不了的。大表隨機(jī)取行的優(yōu)化思路通常是先估算表行數(shù)隨機(jī)一個偏移量然后從中間位置取。但這是另一個話題了這里只想表達(dá)一個觀點ORDER BY 隨機(jī)函數(shù)的寫法只適合小表大表面試時答這個會被直接追問性能。5.2 分組取每組的第一行這是數(shù)據(jù)分析和報表里非常常見的需求——“每個客戶取最新一筆訂單”“每個商品分類取價格最低的一款”。很多人會用三層嵌套子查詢寫代碼又長又難調(diào)試。最優(yōu)雅的做法是前面提到的 ROW_NUMBER()SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY create_time DESC) rn FROM orders t ) WHERE rn 1;PARTITION BY把數(shù)據(jù)按客戶分組組內(nèi)按時間倒序編號最后取每組編號為 1 的行。這個寫法配合ORDER BY create_time DESC的復(fù)合索引customer_id, create_time DESC能跑出不錯的性能。順便對比一下老開發(fā)可能習(xí)慣用NOT EXISTS實現(xiàn)同樣的需求SELECT * FROM orders a WHERE NOT EXISTS ( SELECT 1 FROM orders b WHERE b.customer_id a.customer_id AND b.create_time a.create_time );語義是“找一張表里不存在比我更新的訂單”——也就是每組最新的一條。這個寫法在客戶數(shù)少、每人訂單多的情況下性能可以但如果客戶數(shù)量大關(guān)聯(lián)查詢的代價會成倍上漲。我從實測來看ROW_NUMBER() 復(fù)合索引是更穩(wěn)妥的選擇。5.3 判斷空表和取首行時的邊界處理還有一個經(jīng)常被忽略的細(xì)節(jié)表里沒有數(shù)據(jù)時“取第一行”會返回什么用FETCH FIRST 1 ROW ONLY查詢空表結(jié)果集是空的程序代碼里做fetch()會返回NO_DATA_FOUND。如果你是在 PL/SQL 里處理就要考慮這個異常分支BEGIN SELECT create_time INTO v_create_time FROM orders ORDER BY create_time DESC FETCH FIRST 1 ROW ONLY; EXCEPTION WHEN NO_DATA_FOUND THEN v_create_time : NULL; END;這種寫法在取最新時間戳?xí)r很常用但它有一個隱患如果create_time本身允許 NULL排序后第一條可能恰恰是 NULL。換句話說你取到了“存在但為 NULL”的時間跟“表里沒有數(shù)據(jù)”在 PL/SQL 里表現(xiàn)完全不一樣。寫代碼判斷空表時COUNT(*)或者EXISTS更直接SELECT COUNT(*) INTO v_cnt FROM orders WHERE ...; IF v_cnt 0 THEN -- 空表邏輯 END IF;如果你是在存儲過程里動態(tài)拼 SQL 取首行記住動態(tài) SQL 和靜態(tài) SQL 的 ROWNUM 行為一致但綁定變量和字面量的執(zhí)行計劃可能不同這在 11g 的綁定變量窺探bind peeking機(jī)制下尤其要注意。6. 我用ROWNUM踩過的兩個真實坑與驗證方法理論講完說實踐。最后分享兩個我實際踩過的坑都跟“取第一行/取前幾行”有關(guān)希望能幫各位少走彎路。6.1 坑一PL/SQL 游標(biāo)里 ROWNUM 放錯了層當(dāng)時要寫一個報表存儲過程邏輯是“從子表里取最近三條記錄然后循環(huán)處理”。我一開始寫的代碼是FOR rec IN ( SELECT * FROM child_table WHERE parent_id p_parent_id AND ROWNUM 3 ORDER BY create_time DESC ) LOOP ... END LOOP;一眼看過去覺得沒問題又是過濾又是排序。但實際跑出來的結(jié)果完全不對——取到的三條根本不是最新的三條。原因就是前面講的那個機(jī)制WHERE ROWNUM 3在ORDER BY之前執(zhí)行先把物理掃描的前三條抓走了然后才排序。正確寫法還是那招——嵌套子查詢FOR rec IN ( SELECT * FROM ( SELECT * FROM child_table WHERE parent_id p_parent_id ORDER BY create_time DESC ) WHERE ROWNUM 3 ) LOOP ... END LOOP;這個坑的問題在于它不像語法報錯那樣直接暴露而是數(shù)據(jù)結(jié)果不對。數(shù)據(jù)量的變化也可能讓問題時隱時現(xiàn)——小表掃描順序碰巧和排序一致時結(jié)果是對的數(shù)據(jù)一多物理順序變了就出現(xiàn)偶發(fā)錯誤。這類“偶爾錯、偶爾對”的問題在排查時最難定位。6.2 坑二ORDER BY 的列沒進(jìn) SELECT 列表排序被優(yōu)化器陰了一把第二個坑更隱蔽。當(dāng)時有個分頁查詢外層是 ROWNUM 控制頁大小內(nèi)層子查詢排序。為了“精簡結(jié)果集”內(nèi)層 SELECT 只選了業(yè)務(wù)要展示的字段排序字段在子查詢里沒出現(xiàn)在 SELECT 列表中SELECT * FROM ( SELECT order_id, order_amount, status FROM orders ORDER BY create_time DESC ) WHERE ROWNUM 20;Oracle 文檔里明確說明ORDER BY的列需要出現(xiàn)在 SELECT 列表中否則不保證排序結(jié)果。但實際執(zhí)行時它也不一定報錯——優(yōu)化器可能會自行處理在某些執(zhí)行路徑下排序結(jié)果符合預(yù)期換了一種執(zhí)行計劃后結(jié)果就變了。我在 19c 上測試這種寫法在某些索引組合下會出現(xiàn)返回行亂序的情況排查了很久才定位到是排序字段被優(yōu)化器“優(yōu)化”掉了。解決辦法有兩個一是把create_time也放進(jìn)子查詢的 SELECT 列表二是設(shè)計復(fù)合索引(create_time DESC, order_id)讓排序完全走索引。6.3 驗證寫法正確性的通用方法最后分享一個通用的驗證思路。不管用哪種寫法寫完后先問三個問題SQL 的語義執(zhí)行順序是什么WHERE 過濾、排序、行數(shù)截斷哪個先哪個后Oracle 里 WHERE 一定在 ORDER BY 之前窗口函數(shù)在 ORDER BY 之后別搞反。執(zhí)行計劃里有沒有多余的全表排序EXPLAIN PLAN 看有沒有 SORT ORDER BY有就說明排序躲不掉如果業(yè)務(wù)能接受沒排序的“第一行”直接 ROWNUM1 完事性能差著兩個數(shù)量級。結(jié)果是不是確定性的連續(xù)跑三次如果三次返回一樣的行才算穩(wěn)定。如果業(yè)務(wù)對“第一行”的定義模糊干脆把結(jié)果設(shè)為任意行并讓產(chǎn)品接受這個現(xiàn)實。這三個問題想明白基本上“取第一行”相關(guān)的 SQL 就不會再翻車了。最后再順手分享一個小筆記Oracle 的OFFSET ... FETCH語法其實從 12c 開始一直沿用到現(xiàn)在如果你開發(fā)環(huán)境是 21c 或 23ai可以放心用如果你要兼容 11g 的老生產(chǎn)環(huán)境嵌套子查詢 ROWNUM 才是那塊壓艙石。碰到取首行需求先問清楚業(yè)務(wù)意圖再選對應(yīng)的 SQL 形態(tài)這樣寫出來的查詢既高效又經(jīng)得起推敲。