oracle ROWNUM

Trong Oracle Database, ROWNUM là thứ ai cũng quen, nhưng cũng là chỗ sinh ra nhiều câu query sai logic nặng nhất.

Dev lẫn Database Engineer hay vấp mấy câu hỏi khó hiểu sau:

Sao SELECT * FROM employees WHERE ROWNUM = 2 hay WHERE ROWNUM > 5 lúc nào cũng trả về 0 bản ghi?
Sao WHERE ROWNUM <= 10 ORDER BY salary DESC không lấy ra 10 người lương cao nhất, mà lấy đại 10 người rồi mới sắp xếp?
Bộ tối ưu chi phí (Cost-Based Optimizer - CBO) của Oracle dùng COUNT STOPKEY thế nào để khỏi phải Full Table Scan?
Từ Oracle 12c, cú pháp ANSI OFFSET n ROWS FETCH NEXT m ROWS ONLY có thật sự hơn kiểu ROWNUM lồng 3 tầng quen thuộc không?

Bài này sẽ gỡ từng chuyện một, nhìn từ thứ tự thực thi của SQL Engine và cách Oracle tối ưu hiệu năng.

# 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ị WHERE loại, ROWNUM KHÔNG TĂNG! Dòng tiếp theo vẫn được thử với ROWNUM = 1!

# vì sao WHERE ROWNUM = 2 (hoặc > 1) ra rỗng:

Dòng đầu tiên được quét, Oracle tạm gán ROWNUM = 1.
Kiểm tra điều kiện: 1 = 2 → FALSE.
Dòng đó bị loại! Bộ đếm ROWNUM vẫn đứng ở 1.
Dòng thứ hai được quét, Oracle lại gán ROWNUM = 1.
Kiểm tra: 1 = 2 → FALSE.
Cứ thế lặp hết cả bảng. Kết quả: không có dòng nào trả về!

# cái bẫy "lấy top 10" với ORDER BY

Xem câu query rất hay gặp nhưng sai logic nặng này:

-- 🔴 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:

WHERE chạy TRƯỚC ORDER 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 lấy được 10 người lương cao nhất công ty, mà chỉ lấy 10 người bất kỳ rồi xếp họ với nhau!

# cách sửa đúng: subquery hoặc index stopkey

Phải 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 sẽ lo: 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! CBO của Oracle khá khôn, nhờ có 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êm WHERE salary IS NOT NULL). B-Tree index một cột không chứa dòng có giá trị NULL, nên nếu salary cho 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ùng

Ưu điểm: chạy được trên mọi bản Oracle, kể cả đời rất cũ. Cận trên <= 20 giúp Oracle dừng quét sớm.
Nhược điểm: dài dòng, lồng 3 tầng nhìn rối.

# 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;

Ưu điểm: gọn hơn, làm được mấy kiểu phức tạp như phân trang theo nhóm (PARTITION BY department_id).
Lưu ý: nếu có index phù hợp, CBO vẫn có thể tối ưu thành WINDOW NOSORT STOPKEY. Analytic function như ROW_NUMBER() có từ Oracle 8i (8.1.6), nhưng phải tới 9i/10g thì CBO mới tối ưu dạng này tố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ữ. 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 trong execution plan!

# deep paging và kỹ thuật keyset pagination (seek method)

Dù bạn dùng ROWNUM, ROW_NUMBER() hay OFFSET-FETCH, rắc rối vẫn 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.

Độ phức tạp: O(log N), vì B-Tree index seek thẳng tới chỗ cần đọc.
Tốc độ: trang 1 hay trang 1,000,000 thì thời gian gần như không đổi, vì lần nào cũng chỉ đọc đúng 20 entry sau điểm seek.
Đánh đổi: không nhảy thẳng tới trang N được, chỉ đi tới/lui từng trang; client phải giữ lại giá trị khoá của dòng cuối.

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

Nhớ thứ tự thực thi: ROWNUM được đánh số ở WHERE, trước khi ORDER BY chạy. Cần sắp xếp rồi mới giới hạn thì luôn bọc inline view.
Đừng dùng ROWNUM > 1: điều kiện này luôn ra 0 bản ghi. Muốn phân trang thì bọc subquery hoặc dùng OFFSET-FETCH.
Để STOPKEY phát huy: cột trong ORDER BY nên có index để Oracle dùng được COUNT STOPKEY, tránh Full Table Scan.
Dùng cú pháp mới trên Oracle 12c+: OFFSET ... FETCH NEXT ... dễ đọc hơn, lại theo chuẩn ANSI SQL.
Coi chừng deep paging: dữ liệu cỡ chục triệu bản ghi thì chuyển sang Keyset Pagination (Seek Method).

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 😎 👍🏻 🚀 🔥.