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

Oracle分頁查詢格式（四）：http://yangtingkun.itpub.net/post/468/104867

Oracle分頁查詢格式（五）：http://yangtingkun.itpub.net/post/468/107934

Oracle分頁查詢格式（六）：http://yangtingkun.itpub.net/post/468/108677

Oracle分頁查詢格式（七）：http://yangtingkun.itpub.net/post/468/109834

前面提到了對於集操作和聚集操作，使用標準的分頁函式沒有太大的意義，下面透過幾篇文章專門討論集操作和聚集操作的情況。這裡首先討論集操作的情況。

當查詢需要分頁時，大多數情況都會包含排序操作，因為如果缺少排序操作，很難保證分頁返回的資料是連續不重複的。

因此這裡只考慮包含排序的情況：

SQL> CREATE TABLE T AS SELECT * FROM DBA_TABLES;

表已建立。

SQL> CREATE TABLE T1 AS SELECT * FROM DBA_INDEXES;

表已建立。

SQL> CREATE INDEX IND_T_OWNER ON T(OWNER);

索引已建立。

SQL> CREATE INDEX IND_T1_OWNER ON T1(OWNER);

索引已建立。

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

PL/SQL 過程已成功完成。

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

PL/SQL 過程已成功完成。

SQL> SET AUTOT ON
SQL> SELECT /*+ FIRST_ROWS */ OWNER, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OWNER, NAME
5 FROM
6 (
7 SELECT OWNER, TABLE_NAME NAME
8 FROM T
9 UNION ALL
10 SELECT OWNER, INDEX_NAME NAME
11 FROM T1
12 ORDER BY OWNER
13 )
14 WHERE ROWNUM <= 20
15 )
16 WHERE RN > 10;

OWNER NAME
------------------------------ ------------------------------
CTXSYS DR$SECTION_GROUP
CTXSYS DR$THS_BT
CTXSYS DR$THS_FPHRASE
CTXSYS DR$THS_PHRASE
CTXSYS DR$THS
CTXSYS DR$SQE
CTXSYS SYS_IOT_OVER_26472
CTXSYS DR$INDEX_OBJECT
CTXSYS DR$POLICY_TAB
CTXSYS DR$INDEX_PARTITION

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=17 Card=20 Bytes=940)
1 0 VIEW (Cost=17 Card=20 Bytes=940)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=17 Card=2877 Bytes=97818)
4 3 SORT (ORDER BY STOPKEY) (Cost=7 Card=2877 Bytes=76522)
5 4 UNION-ALL
6 5 TABLE ACCESS (FULL) OF 'T' (Cost=3 Card=1157 Bytes=30082)
7 5 TABLE ACCESS (FULL) OF 'T1' (Cost=4 Card=1720 Bytes=46440)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
44 consistent gets
0 physical reads
0 redo size
639 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 /*+ FIRST_ROWS */ OWNER, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OWNER, NAME
5 FROM
6 (
7 SELECT OWNER, TABLE_NAME NAME
8 FROM T
9 UNION ALL
10 SELECT OWNER, INDEX_NAME NAME
11 FROM T1
12 ORDER BY OWNER
13 )
14 )
15 WHERE RN > 10 AND RN <=20 ;

OWNER NAME
------------------------------ ------------------------------
CTXSYS DR$THS_BT
CTXSYS DR$THS_FPHRASE
CTXSYS DR$THS_PHRASE
CTXSYS DR$THS
CTXSYS DR$SQE
CTXSYS SYS_IOT_OVER_26472
CTXSYS DR$INDEX_OBJECT
CTXSYS DR$POLICY_TAB
CTXSYS DR$PART_STATS
CTXSYS DR$STATS

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=17 Card=2877 Bytes=135219)
1 0 VIEW (Cost=17 Card=2877 Bytes=135219)
2 1 COUNT
3 2 VIEW (Cost=17 Card=2877 Bytes=97818)
4 3 SORT (ORDER BY) (Cost=7 Card=2877 Bytes=76522)
5 4 UNION-ALL
6 5 TABLE ACCESS (FULL) OF 'T' (Cost=3 Card=1157 Bytes=30082)
7 5 TABLE ACCESS (FULL) OF 'T1' (Cost=4 Card=1720 Bytes=46440)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
44 consistent gets
0 physical reads
0 redo size
626 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的統計中看到，由於集操作的存在，導致了Oracle沒有辦法將ROWNUM資訊推入到查詢內部，導致標準分頁方式的效率和其他分頁方式效率差別不大。

當存在排序操作，且集操作為UNION ALL操作時，可以改寫SQL語句為：

SQL> SELECT /*+ FIRST_ROWS */ OWNER, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OWNER, NAME
5 FROM
6 (
7 SELECT OWNER, NAME
8 FROM
9 (
10 SELECT OWNER, TABLE_NAME NAME
11 FROM T
12 ORDER BY OWNER
13 )
14 WHERE ROWNUM <= 20
15 UNION ALL
16 SELECT *
17 FROM
18 (
19 SELECT OWNER, TABLE_NAME NAME
20 FROM T1
21 ORDER BY OWNER
22 )
23 WHERE ROWNUM <= 20
24 ORDER BY OWNER
25 )
26 WHERE ROWNUM <= 20
27 )
28 WHERE RN > 10;

OWNER NAME
------------------------------ ------------------------------
CTXSYS DR$POLICY_TAB
CTXSYS DR$INDEX_OBJECT
CTXSYS SYS_IOT_OVER_26472
CTXSYS DR$SQE
CTXSYS DR$THS
CTXSYS DR$THS_PHRASE
CTXSYS DR$THS_FPHRASE
CTXSYS DR$THS_BT
CTXSYS DR$SECTION_GROUP
CTXSYS DR$SECTION

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1654 Card=20 Bytes=940)
1 0 VIEW (Cost=1654 Card=20 Bytes=940)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=1654 Card=40 Bytes=1360)
4 3 SORT (ORDER BY STOPKEY) (Cost=1652 Card=40 Bytes=1360)
5 4 UNION-ALL
6 5 COUNT (STOPKEY)
7 6 VIEW (Cost=826 Card=1157 Bytes=39338)
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=1157 Bytes=30082)
9 8 INDEX (FULL SCAN) OF 'IND_T_OWNER' (NON-UNIQUE) (Cost=26 Card=1157)
10 5 COUNT (STOPKEY)
11 10 VIEW (Cost=826 Card=1720 Bytes=58480)
12 11 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=826 Card=1720 Bytes=39560)
13 12 INDEX (FULL SCAN) OF 'IND_T1_OWNER' (NON-UNIQUE) (Cost=26 Card=1720)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
2 physical reads
0 redo size
631 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

進行了上面的等價改寫，查詢的邏輯讀大大的減少。其實，這裡使用的方法就是人為的將ROWNUM推入到UNION ALL操作的每個子查詢中，使用這種方法保證查詢結果正確的同時，提高了查詢的效率。

不過上面給出的改寫方法只對包含排序的UNION ALL操作有效。而其他集操作不能使用這種方法，比如UNION操作使用這種方法可能導致查詢結果的數量小於查詢開始限定的數量。而對於另外兩種集操作，這種方法可能會導致錯誤的查詢結果。