[Oracle]分頁查詢

有限結果集

在Oracle 中， 使用好 ROWNUM 這個欄位， 將會非常有用。一般來說可以用它來做兩件事情：

1. 執行top-N的查詢。這和其他數據庫的 LIMIT 語法的作用類似。（查找前N筆數據）

2. 執行分頁查詢， 特別是在Web 這樣的無狀態的環境。

ROWNUM 是如何工作的

ROWNUM 是一個在查詢中的虛列（不是實際的列）。ROWNUM 的值是類似於： 1,,2,,3,4,...,N。ROWNUM 的值不是永久的分配給某行。所用不能使用 ROWNUM=5 這樣的方式來找到某行。

還有一個重要的概念是ROWNUM什麼時間被賦值。 ROWNUM 在某個Query語句通過查詢謂詞階段之後但是在查詢做任何排序和匯總之前。同樣，ROWNUM的值只有在分配之後才能增長。

像以下這樣的語句就得不到數據。

select * 
  from t 
 where ROWNUM > 1;

select ..., ROWNUM
  from t
 where <where clause>
 group by <columns>
having <having clause>
 order by <columns>;

1. FROM/WHERE 子句先行

2. ROWNUM 是從FROM/WHERE子句中分配和遞增給每一個輸出行。

3. SELECT 被應用

4. GROUP BY 被應用

5. HAVING 被應用

6. ORDER BY 被應用

這就是為什麼以下方式進行Query 來處理ROWNUM 是會有問題的了：

select * 
  from emp 
 where ROWNUM <= 5 
 order by sal desc;

這個語句會隨意的找五筆數據，而不是薪資最高的五筆數據。

所以， 正確的做法應該是：

先排序， 然後再使用 rownum. 可以這樣 

select *
  from  
( select * 
    from emp 
   order by sal desc ) 
 where ROWNUM <= 5;

查詢前 n 條數據

對與一張數據量很大（百萬級甚至更多） 且每個記錄的內容都比較大的表來說， 獲取前 10條或是100條數據的方式可以是：

1. Query 出所有數據， 取前 n 條 order by

2. 使用內部表的方式， 結合ROWNUM, 類似

SELECT * FROM ( your_query_here ) WHERE ROWNUM <= N.

除了從DB 中取的比較少的數據外，這兩種方式在性能上差異很大， 差在什麼地方呢？

先來看一下第一種方式的執行過程

1. 全表掃描

2. 對所有數據根據字段排序

3. 如果排序的內存不夠的話，從硬盤擴展

4. 結合擴展的臨時內存取出前 n 條數據

5. 釋放臨時內存。

而第二種方式的過程就要簡單得多

1. 全表掃描

2. 排序區先取n 條數據， n 條之後的數據每筆和這n 條進行比對， 滿足條件的話就替換。

也就是說， 排序區始終只有n 條數據。 也就不會從硬盤擴展。

筆者在實際開發的過程中， 在 2000萬筆數據的狀況下，

不使用ROWNUM 簡直就無法使用了。 使用ROWNUM 的狀況系統響應時間在 10s 內。

看例子

建立一個 EMPLOYEE 的表， 並插入一些數據 

CREATE TABLE EMPLOYEE(
  EMPID varchar2(10),
  EMPNAME varchar2(10),
  SALARY varchar2(60)
);

insert into EMPLOYEE values('001','zhao','7300');
insert into EMPLOYEE values('002','qian','7400');
insert into EMPLOYEE values('003','sun','7500');
insert into EMPLOYEE values('004','li','7200');
insert into EMPLOYEE values('005','zhou','7100');
insert into EMPLOYEE values('006','wu','7000');
insert into EMPLOYEE values('007','zheng','6500');
insert into EMPLOYEE values('008','wang','6000');
insert into EMPLOYEE values('009','feng','6100');
insert into EMPLOYEE values('010','chen','6200');
insert into EMPLOYEE values('011','zhu','6300');
insert into EMPLOYEE values('012','wei','6400');
insert into EMPLOYEE values('013','jiang','6700');
insert into EMPLOYEE values('014','shen','6600');
insert into EMPLOYEE values('015','han','6800');
insert into EMPLOYEE values('016','yang','6900');

1. 現在想查看薪資最高的五筆數據。

1）使用 ：

select * from EMPLOYEE where ROWNUM <= 5 order by SALARY desc;

得到 ：

2） select * from EMPLOYEE order by SALARY desc;

結果完全不相同。 驗證了以上部分的描述。

正確的寫法可以是：

select * from(
select * from EMPLOYEE  order by SALARY desc) where ROWNUM <= 5 ;