ROWNUM trong Oracle: bẫy ROWNUM > 1, Top-N và phân trang đúng cách

- Published on
- 12 mins read
Cần lấy nhân viên thứ hai trong bảng, bạn gõ SELECT * FROM employees WHERE ROWNUM = 2. Oracle trả về 0 dòng. Không lỗi, không cảnh báo gì cả.
Đổi sang WHERE ROWNUM > 5 cũng vậy. Còn WHERE ROWNUM <= 10 ORDER BY salary DESC thì ra đủ 10 dòng thật, có điều đó không phải 10 người lương cao nhất. Đó là 10 người bất kỳ, được sắp xếp lại với nhau cho đẹp.
ROWNUM là thứ ai làm Oracle cũng quen, mà cũng là chỗ sinh ra nhiều query sai logic nhất. Cả hai lỗi trên chung một gốc: ROWNUM được đánh số ở thời điểm nào trong lúc câu query chạy. Nắm được chỗ đó rồi thì mấy chuyện còn lại, như COUNT STOPKEY giúp CBO khỏi Full Table Scan hay OFFSET ... FETCH của 12c có hơn kiểu lồng 3 tầng không, sẽ dễ theo hơn nhiều.
# thứ tự thực thi: vì sao WHERE ROWNUM > 1 luôn trả về rỗng?
Muốn hiểu ROWNUM, bạn phải nắm được thứ tự đánh giá (Evaluation Order) của Oracle SQL Engine:
# ROWNUM thật ra là gì:
ROWNUM là một cột giả (pseudocolumn) tạm thời. Nó không hề được lưu trong block dữ liệu trên đĩa. Cách nó hoạt động:
- Khi một dòng vượt qua hết các điều kiện lọc trong
WHERE, Oracle gán cho nóROWNUM = 1. - Nếu dòng đó được nhận vào tập kết quả, bộ đếm tăng lên, dòng thoả mãn tiếp theo sẽ có
ROWNUM = 2. - Nếu dòng đó bị
WHEREloại thì bộ đếm không tăng. Dòng tiếp theo vẫn được thử vớiROWNUM = 1.
# vì sao WHERE ROWNUM = 2 (hoặc > 1) ra rỗng:
Thử chạy tay WHERE ROWNUM = 2. Dòng đầu tiên được đọc lên, Oracle gán tạm cho nó ROWNUM = 1 rồi đem so với điều kiện: 1 = 2 là FALSE, loại.
Mấu chốt ở đây: dòng bị loại thì bộ đếm không nhích, vẫn đứng ở 1. Dòng thứ hai lên, lại được gán ROWNUM = 1, lại trượt 1 = 2, lại bị vứt. Cứ thế tới dòng cuối bảng. Không dòng nào qua được mốc 1 để bộ đếm lên 2, nên kết quả luôn là 0 dòng.
# cái bẫy "lấy top 10" với ORDER BY
Câu này gặp rất nhiều, đọc lướt thấy hợp lý, mà sai:
-- 🔴 TRUY VẤN SAI HOÀN TOÀN:
SELECT employee_id, salary
FROM employees
WHERE ROWNUM <= 10
ORDER BY salary DESC;# sao câu này ra kết quả sai?
Nhìn lại sơ đồ thứ tự thực thi ở phần trên:
WHEREchạy TRƯỚCORDER BY.- Oracle lấy 10 bản ghi đầu tiên nó tình cờ gặp trong data block trên đĩa (theo thứ tự lưu trữ vật lý).
- Lấy đủ 10 bản ghi "ngẫu nhiên" đó rồi, Oracle mới sắp xếp 10 bản ghi này theo
salary DESC. - Hậu quả: bạn không có 10 người lương cao nhất công ty, chỉ có 10 người bất kỳ được xếp hạng với nhau.
# cách sửa đúng: subquery hoặc index stopkey
Cách sửa là bọc phần ORDER BY vào một subquery (inline view), rồi mới áp ROWNUM ở ngoài:
-- 🟢 TRUY VẤN CHUẨN XÁC:
SELECT *
FROM (
SELECT employee_id, salary
FROM employees
ORDER BY salary DESC
)
WHERE ROWNUM <= 10;# cbo tối ưu thế nào: COUNT STOPKEY
Viết đúng câu Top-N bằng subquery rồi, có thể bạn lại lo chuyện khác: Oracle có phải sort nguyên bảng 10 triệu dòng trong subquery, rồi mới lọc lấy 10 dòng ở ngoài không?
Không cần. CBO của Oracle khôn hơn thế, nhờ COUNT STOPKEY.
Xem execution plan (EXPLAIN PLAN FOR, plan dưới đây là minh hoạ, số liệu Rows/Cost tuỳ dữ liệu thật):
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 260 | 4 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 10 | 260 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 10 | 120 | 4 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN DESCENDING | EMP_SAL_IX | 10 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)Index EMP_SAL_IX chỉ có cột salary, nên với mỗi entry đọc được Oracle phải nhảy về bảng (TABLE ACCESS BY INDEX ROWID) để lấy employee_id. Đọc 10 entry thì cũng chỉ 10 lần nhảy.
Nếu cột salary có index, COUNT STOPKEY sẽ ngừng duyệt index ngay khi đủ 10 bản ghi đầu, chi phí từ O(N log N) (sort cả bảng) xuống còn cỡ O(log N + K) (với K = 10): đi từ gốc B-Tree xuống lá rồi đọc K entry.
Có hai điều kiện hay bị bỏ sót:
- Cột phải
NOT NULL(hoặc query có thêmWHERE salary IS NOT NULL). B-Tree index một cột không chứa dòng có giá trị NULL, nên nếusalarycho phép NULL thì Oracle không thể dựa vào index để trả đúng thứ tự cho cả bảng, và sẽ quay về quét bảng rồi sort. - Không có index phù hợp thì Oracle vẫn phải đọc hết bảng, nhưng plan sẽ là
SORT ORDER BY STOPKEY: nó chỉ giữ 10 dòng tốt nhất trong bộ nhớ trong lúc quét chứ không sort toàn bộ 10 triệu dòng. Nhẹ hơn sort đầy đủ nhiều, nhưng vẫn tốn I/O đọc cả bảng.
# phân trang qua các thời kỳ: từ 3 tầng subquery tới ANSI OFFSET-FETCH
# thời 1: subquery 3 tầng kinh điển (oracle 8i - 11g)
Lấy trang 2 (bản ghi 11 tới 20):
SELECT *
FROM (
SELECT inner_query.*, ROWNUM AS rnum
FROM (
SELECT employee_id, full_name, salary
FROM employees
ORDER BY salary DESC
) inner_query
WHERE ROWNUM <= 20 -- Giới hạn cận trên tại tầng 2 để kích hoạt STOPKEY!
)
WHERE rnum >= 11; -- Lọc cận dưới tại tầng ngoài cùngLồng 3 tầng nhìn rối mắt thật. Bù lại nó chạy được trên mọi phiên bản Oracle từ 8i tới nay, và điều kiện cận trên ROWNUM <= 20 ở tầng giữa vẫn kích hoạt COUNT STOPKEY để dừng quét sớm.
# thời 2: window function ROW_NUMBER() (oracle 8i+)
SELECT employee_id, full_name, salary
FROM (
SELECT employee_id, full_name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees
)
WHERE rn BETWEEN 11 AND 20;Với ROW_NUMBER(), câu query bớt được một tầng lồng, và dễ mở rộng sang bài toán khó hơn như lấy top N theo từng phòng ban (PARTITION BY department_id). Nếu có index phù hợp, CBO vẫn có thể tối ưu thành WINDOW NOSORT STOPKEY. Dù các hàm analytic đã xuất hiện từ Oracle 8i (8.1.6), phải từ bản 9i/10g trở đi thì CBO mới tối ưu dạng phân trang này thật sự mượt.
# thời 3: chuẩn ANSI SQL:2008 trong oracle 12c+
Từ Oracle 12c Release 1 (12.1), Oracle hỗ trợ chính thức cú pháp phân trang mới:
SELECT employee_id, full_name, salary
FROM employees
ORDER BY salary DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;# bên dưới engine của oracle 12c+
Oracle không viết lại engine lưu trữ cho cú pháp này. Khi bạn chạy OFFSET-FETCH, CBO tự viết lại câu query (Query Rewrite) thành dạng window function ROW_NUMBER(), nằm ẩn bên trong execution plan. Nói cách khác, đây vẫn là thời 2, chỉ là bạn không phải tự gõ.
# deep paging và kỹ thuật keyset pagination (seek method)
Dùng ROWNUM, ROW_NUMBER() hay OFFSET-FETCH thì cũng thế, rắc rối tới khi người dùng mở các trang rất sâu (Deep Pages):
-- Trang thứ 10,000 (Lấy từ dòng 100,001 đến 100,020)
OFFSET 100000 ROWS FETCH NEXT 20 ROWS ONLY;# cách xử lý: keyset pagination (seek method)
Thay vì đếm số dòng cần bỏ qua (OFFSET), mình dùng giá trị khoá của dòng cuối trang trước, kết hợp với composite index B-Tree (salary, employee_id):
-- Lấy trang tiếp theo ngay lập tức dựa trên giá trị của bản ghi cuối cùng trang trước!
SELECT employee_id, full_name, salary
FROM employees
WHERE salary <= :last_seen_salary -- điều kiện thừa về logic nhưng giúp Oracle có access predicate trên index
AND (salary < :last_seen_salary
OR (salary = :last_seen_salary AND employee_id < :last_seen_id))
ORDER BY salary DESC, employee_id DESC
FETCH FIRST 20 ROWS ONLY;Oracle không hỗ trợ so sánh theo bộ giá trị kiểu (salary, employee_id) < (:a, :b) như PostgreSQL, nên phải viết bằng OR. Chỉ có mỗi vế OR thì optimizer nhiều khi không biết bắt đầu range scan từ đâu, thêm salary <= :last_seen_salary ở ngoài là để nó seek thẳng tới đúng vị trí trên index. employee_id đưa vào để thứ tự là duy nhất, hai người cùng lương không bị lặp hay sót giữa hai trang.
B-Tree index nhảy thẳng tới điểm neo trong O(log N) rồi chỉ đọc đúng 20 entry kế tiếp, nên lấy trang thứ 1.000.000 cũng nhanh gần bằng trang đầu. Đổi lại, client phải giữ lại cặp khoá (last_seen_salary, last_seen_id) của dòng cuối trang trước và người dùng chỉ lật tiếp/lùi từng trang chứ không nhảy cóc thẳng tới trang thứ 500 được.
TIP
Keyset Pagination vs Offset: Với API infinite scroll trên mobile hoặc dữ liệu cả triệu dòng, nên ưu tiên Keyset Pagination (cursor-based). Offset Pagination chỉ nên dùng khi bắt buộc phải nhảy thẳng tới một trang bất kỳ (ví dụ trang 5, trang 10) trong admin dashboard nội bộ.
# phân biệt các hàm xếp hạng: ROWNUM vs ROW_NUMBER vs RANK vs DENSE_RANK
| Hàm / Kỹ thuật | Khi giá trị bằng nhau (Ties) | Có nhảy số thứ tự (Gaps)? | Dùng trong WHERE? | Use case hay gặp |
|---|---|---|---|---|
ROWNUM | Đánh số theo thứ tự fetch, coi như ngẫu nhiên | Không | Có (<, <=, = 1) | Giới hạn Top-N đơn giản. |
ROW_NUMBER() | Đánh số liên tục, không trùng (1, 2, 3, 4) | Không | Gián tiếp (qua subquery) | Phân trang chuẩn, deduplication. |
RANK() | Bằng nhau thì chung hạng (1, 2, 2, 4) | CÓ | Gián tiếp | Bảng xếp hạng thi đấu thể thao. |
DENSE_RANK() | Bằng nhau thì chung hạng (1, 2, 2, 3) | KHÔNG | Gián tiếp | Tìm mức lương cao thứ N thực tế. |
# tổng kết & checklist khi làm thật
Thay vì đọc lại cả bài, đây là mấy việc có thể làm ngay trên codebase Oracle đang có:
- Grep
ROWNUMtrong code và mapper. Câu nào cóROWNUMvàORDER BYở cùng một tầng query là câu Top-N sai, bọc nó vào inline view. Câu nào lọc kiểuROWNUM > 1hayROWNUM = 2thì chắc chắn đang trả về rỗng. - Chạy
EXPLAIN PLANcho các câu Top-N quan trọng. ThấyCOUNT STOPKEYđi cùng index scan là ổn. ThấySORT ORDER BY STOPKEYnghĩa là vẫn đang đọc cả bảng: kiểm tra cột sort đã có index chưa và cột đó cóNOT NULLkhông. - Trên 12c+, ưu tiên
OFFSET ... FETCH NEXT ...cho code mới. Dễ đọc hơn và theo chuẩn ANSI. - Tìm các API phân trang mà người dùng có thể lật rất sâu (infinite scroll, export, job đồng bộ). Mấy chỗ đó nên chuyển sang keyset pagination.
Muốn đi tiếp phần tối ưu query Oracle nói chung, đọc thêm bài tối ưu SQL trong Oracle: đọc execution plan, E-Rows vs A-Rows và SPM.
Chỉ là những ghi chép cá nhân với hy vọng mang lại chút giá trị. Nếu thấy hữu ích, đừng ngại chia sẻ cho bạn bè & đồng nghiệp nhé!
Happy coding 😎 👍🏻 🚀 🔥.
On this page
- # thứ tự thực thi: vì sao WHERE ROWNUM > 1 luôn trả về rỗng?
- # ROWNUM thật ra là gì:
- # vì sao WHERE ROWNUM = 2 (hoặc > 1) ra rỗng:
- # cái bẫy "lấy top 10" với ORDER BY
- # sao câu này ra kết quả sai?
- # cách sửa đúng: subquery hoặc index stopkey
- # cbo tối ưu thế nào: COUNT STOPKEY
- # phân trang qua các thời kỳ: từ 3 tầng subquery tới ANSI OFFSET-FETCH
- # thời 1: subquery 3 tầng kinh điển (oracle 8i - 11g)
- # thời 2: window function ROW_NUMBER() (oracle 8i+)
- # thời 3: chuẩn ANSI SQL:2008 trong oracle 12c+
- # bên dưới engine của oracle 12c+
- # deep paging và kỹ thuật keyset pagination (seek method)
- # cách xử lý: keyset pagination (seek method)
- # phân biệt các hàm xếp hạng: ROWNUM vs ROW_NUMBER vs RANK vs DENSE_RANK
- # tổng kết & checklist khi làm thật