Oracle分頁查詢語句（四）

Oracle的分頁查詢語句基本上可以按照本文給出的格式來進行套用。

Oracle分頁查詢語句（一）：http://yangtingkun.itpub.net/post/468/100278

Oracle分頁查詢語句（二）：http://yangtingkun.itpub.net/post/468/101703

Oracle分頁查詢語句（三）：http://yangtingkun.itpub.net/post/468/104595

最後的例子說明內部迴圈包含排序的情況：

SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;

表已建立。

SQL> CREATE INDEX IND_T_OBJECT_NAME ON T (OBJECT_NAME);

索引已建立。

SQL> ALTER TABLE T MODIFY OBJECT_NAME NOT NULL;

表已更改。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')

PL/SQL 過程已成功完成。

下面進行測試包含排序操作的分頁查詢。可以簡單的將查詢分為兩種不同情況，第一種排序列就是索引列，這種可以利用索引讀取，第二種排序列沒有索引。

第一種情況又可以細分為：完全索引掃描和透過索引掃描定位到表記錄兩種情況。

無論是那種情況，都可以透過索引的全掃描來避免排序的產生。看下面的例子：

SQL> SET AUTOT TRACE
SQL> SELECT OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_NAME FROM T ORDER BY OBJECT_NAME
8 )
9 WHERE ROWNUM <= 20
10 )
11 WHERE RN >= 11;

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=20 Bytes=1580)
1 0 VIEW (Cost=26 Card=20 Bytes=1580)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=26 Card=6361 Bytes=419826)
4 3 INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=6361 Bytes=108137)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
576 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

這種情況下，透過索引可以完全得到查詢的結果，因此可以避免表掃描的產生，而且，由於索引已經是排序過的，因此透過索引的全掃描，連排序操作都省略了。

SQL> SELECT OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY OBJECT_NAME
8 )
9 WHERE ROWNUM <= 20
10 )
11 WHERE RN >= 11;

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=43 Card=20 Bytes=1840)
1 0 VIEW (Cost=43 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=43 Card=6361 Bytes=502519)
4 3 SORT (ORDER BY STOPKEY) (Cost=43 Card=6361 Bytes=133581)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=6361 Bytes=133581)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
81 consistent gets
0 physical reads
0 redo size
673 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed

由於不能僅僅透過索引掃描得到查詢結果，這裡Oracle選擇了表掃描。這是由於初始化引數設定決定的。因此，建議在分頁的時候使用FIRST_ROWS提示。

SQL> SELECT /*+ FIRST_ROWS */ OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY OBJECT_NAME
8 )
9 WHERE ROWNUM <= 20
10 )
11 WHERE RN >= 11;

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=826 Card=20 Bytes=1840)
1 0 VIEW (Cost=826 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=826 Card=6361 Bytes=502519)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=6361 Bytes=133581)
5 4 INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=6361)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
673 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

使用了FIRST_ROWS提示後，Oracle不需要掃描全表，而且避免了排序操作。

下面討論最後一種情況，排序列不是索引列。這個時候排序不可避免，但是利用給出分頁格式，Oracle不會對所有資料進行排序，而是隻排序前N條記錄。

SQL> SELECT OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP
8 )
9 )
10 WHERE RN BETWEEN 11 AND 20;

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=6361 Bytes=585212)
1 0 VIEW (Cost=64 Card=6361 Bytes=585212)
2 1 COUNT
3 2 VIEW (Cost=64 Card=6361 Bytes=502519)
4 3 SORT (ORDER BY) (Cost=64 Card=6361 Bytes=260801)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=6361 Bytes=260801)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
81 consistent gets
0 physical reads
0 redo size
690 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed

SQL> SELECT OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP
8 )
9 WHERE ROWNUM <= 20
10 )
11 WHERE RN >= 11;

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=20 Bytes=1840)
1 0 VIEW (Cost=64 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=64 Card=6361 Bytes=502519)
4 3 SORT (ORDER BY STOPKEY) (Cost=64 Card=6361 Bytes=260801)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=6361 Bytes=260801)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
81 consistent gets
0 physical reads
0 redo size
690 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed

觀察兩種不同寫法的ORDER BY步驟，一個是帶STOPKEY的ORDER BY，另一個不帶。在大資料量需要排序的情況下，帶STOPKEY的效率要比不帶STOPKEY排序的效率高得多。

SQL> INSERT INTO T SELECT T.* FROM T, USER_OBJECTS;

已建立407104行。

SQL> COMMIT;

提交完成。

SQL> SELECT OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME FROM
5 (
6 SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP
7 )
8 WHERE ROWNUM <= 20
9 )
10 WHERE RN >= 11;

已選擇10行。

已用時間: 00: 00: 03.78

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=20 Bytes=1840)
1 0 VIEW (Cost=64 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=64 Card=6361 Bytes=502519)
4 3 SORT (ORDER BY STOPKEY) (Cost=64 Card=6361 Bytes=260801)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=6361 Bytes=260801)

Statistics
----------------------------------------------------------
268 recursive calls
0 db block gets
6215 consistent gets
6013 physical reads
0 redo size
740 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
10 rows processed

SQL> SELECT OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME FROM
5 (
6 SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP
7 )
8 )
9 WHERE RN BETWEEN 11 AND 20;

已選擇10行。

已用時間: 00: 00: 11.86

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=6361 Bytes=585212)
1 0 VIEW (Cost=64 Card=6361 Bytes=585212)
2 1 COUNT
3 2 VIEW (Cost=64 Card=6361 Bytes=502519)
4 3 SORT (ORDER BY) (Cost=64 Card=6361 Bytes=260801)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=6361 Bytes=260801)

Statistics
----------------------------------------------------------
26 recursive calls
12 db block gets
6175 consistent gets
9219 physical reads
0 redo size
737 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
10 rows processed

觀察兩個查詢語句的執行時間，以及統計資訊中的排序資訊。對於第一個查詢語句，Oracle利用了ORDER BY STOPKEY方式進行排序，排序操作只排序需要的TOP N的資料，因此排序操作放到了記憶體中，而對於第二個查詢語句來說，進行的資料的全排序，排序資料量大，排序操作不得不在磁碟上完成，因此耗時比較多。

透過上面的例子可以看出給出的標準分頁查詢格式，對於包含排序的操作仍然可以在很大程度上提高分頁查詢效能。