Oracle分頁查詢格式有哪些

本篇內容介紹了“Oracle分頁查詢格式有哪些”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

由于包含上述的集操作，Oracle必須處理完所有的數據才會將結果全部的提交給用戶。即使包含了ROWNUM，Oracle也只是在處理完所有的數據之后，將需要的數據進行返回。

不過并不是說前面提到的標準寫法在這里沒有意義，而是最耗時的部分已經處理完畢。通過ROWNUM來限制意義不大。

雖然標準分頁的寫法對于GROUP BY之類的操作意義不大，但是如果在執行分頁之前需要計算總數的話，那么可以對分頁的查詢語句稍做修改，將計算總數和查詢第一頁的SQL結合起來，避免一次計算總數的操作。

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

會話已更改。

SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;

表已創建。

SQL> ALTER TABLE T ADD PRIMARY KEY (ID);

表已更改。

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

PL/SQL 過程已成功完成。

SQL> SET AUTOT ON
SQL> SELECT *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT OBJECT_TYPE, CREATED, COUNT(*)
8 FROM T
9 GROUP BY OBJECT_TYPE, CREATED
10 ORDER BY CREATED
11 ) A
12 WHERE ROWNUM < 20
13 )
14 WHERE RN >= 10;

RN OBJECT_TYPE CREATED COUNT(*)
---------- ------------------ ------------------- ----------
10 TABLE 2003-11-13 01:41:01 16
11 CLUSTER 2003-11-13 01:41:02 3
12 INDEX 2003-11-13 01:41:02 31
13 LOB 2003-11-13 01:41:02 2
14 SEQUENCE 2003-11-13 01:41:02 4
15 TABLE 2003-11-13 01:41:02 20
16 INDEX 2003-11-13 01:41:03 16
17 LOB 2003-11-13 01:41:03 6
18 SEQUENCE 2003-11-13 01:41:03 2
19 SYNONYM 2003-11-13 01:41:03 1

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=19 Bytes=874)
1 0 VIEW (Cost=97 Card=19 Bytes=874)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=97 Card=32185 Bytes=1062105)
4 3 SORT (GROUP BY STOPKEY) (Cost=97 Card=32185 Bytes=547145)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
232 consistent gets
0 physical reads
0 redo size
759 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 *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT OBJECT_TYPE, CREATED, COUNT(*)
8 FROM T
9 GROUP BY OBJECT_TYPE, CREATED
10 ORDER BY CREATED
11 ) A
12 )
13 WHERE RN >= 10 AND RN < 20;

RN OBJECT_TYPE CREATED COUNT(*)
---------- ------------------ ------------------- ----------
10 TABLE 2003-11-13 01:41:01 16
11 CLUSTER 2003-11-13 01:41:02 3
12 INDEX 2003-11-13 01:41:02 31
13 LOB 2003-11-13 01:41:02 2
14 SEQUENCE 2003-11-13 01:41:02 4
15 TABLE 2003-11-13 01:41:02 20
16 INDEX 2003-11-13 01:41:03 16
17 LOB 2003-11-13 01:41:03 6
18 SEQUENCE 2003-11-13 01:41:03 2
19 SYNONYM 2003-11-13 01:41:03 1

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=32185 Bytes=1480510)
1 0 VIEW (Cost=97 Card=32185 Bytes=1480510)
2 1 COUNT
3 2 VIEW (Cost=97 Card=32185 Bytes=1062105)
4 3 SORT (GROUP BY) (Cost=97 Card=32185 Bytes=547145)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145)

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

由于查詢操作中GROUP BY操作是耗時最大的SQL，因此標準分頁方式在這里所帶來的性能提升十分有限。但是，如果在執行分頁查詢前需要執行COUNT(*)的話，那么可以考慮將COUNT(*)的結果和分頁結果一起返回，從而減少了一次計算總數所需的時間。

SQL> SELECT COUNT(*)
2 FROM
3 (
4 SELECT OBJECT_TYPE, CREATED, COUNT(*)
5 FROM T
6 GROUP BY OBJECT_TYPE, CREATED
7 ORDER BY CREATED
8 );

COUNT(*)
----------
3570

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=1)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=97 Card=32185)
3 2 SORT (GROUP BY) (Cost=97 Card=32185 Bytes=547145)
4 3 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
232 consistent gets
0 physical reads
0 redo size
377 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)
1 rows processed

SQL> SELECT *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT OBJECT_TYPE, CREATED, COUNT(*)
8 FROM T
9 GROUP BY OBJECT_TYPE, CREATED
10 ORDER BY CREATED
11 ) A
12 WHERE ROWNUM < 20
13 )
14 WHERE RN >= 10;

RN OBJECT_TYPE CREATED COUNT(*)
---------- ------------------ ------------------- ----------
10 TABLE 2003-11-13 01:41:01 16
11 CLUSTER 2003-11-13 01:41:02 3
12 INDEX 2003-11-13 01:41:02 31
13 LOB 2003-11-13 01:41:02 2
14 SEQUENCE 2003-11-13 01:41:02 4
15 TABLE 2003-11-13 01:41:02 20
16 INDEX 2003-11-13 01:41:03 16
17 LOB 2003-11-13 01:41:03 6
18 SEQUENCE 2003-11-13 01:41:03 2
19 SYNONYM 2003-11-13 01:41:03 1

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=19 Bytes=874)
1 0 VIEW (Cost=97 Card=19 Bytes=874)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=97 Card=32185 Bytes=1062105)
4 3 SORT (GROUP BY STOPKEY) (Cost=97 Card=32185 Bytes=547145)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
232 consistent gets
0 physical reads
0 redo size
759 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語句來實現：

SQL> SELECT *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT COUNT(*) OVER() CNT, OBJECT_TYPE, CREATED, COUNT(*) CN
8 FROM T
9 GROUP BY OBJECT_TYPE, CREATED
10 ORDER BY CREATED
11 ) A
12 WHERE ROWNUM < 20
13 )
14 WHERE RN >= 10;

RN CNT OBJECT_TYPE CREATED CN
---------- ---------- ------------------ ------------------- ----------
10 3570 TABLE 2003-11-13 01:41:01 16
11 3570 CLUSTER 2003-11-13 01:41:02 3
12 3570 INDEX 2003-11-13 01:41:02 31
13 3570 LOB 2003-11-13 01:41:02 2
14 3570 SEQUENCE 2003-11-13 01:41:02 4
15 3570 TABLE 2003-11-13 01:41:02 20
16 3570 INDEX 2003-11-13 01:41:03 16
17 3570 LOB 2003-11-13 01:41:03 6
18 3570 SEQUENCE 2003-11-13 01:41:03 2
19 3570 SYNONYM 2003-11-13 01:41:03 1

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=19 Bytes=1121)
1 0 VIEW (Cost=97 Card=19 Bytes=1121)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=97 Card=32185 Bytes=1480510)
4 3 WINDOW (BUFFER) (Cost=97 Card=32185 Bytes=547145)
5 4 SORT (GROUP BY) (Cost=97 Card=32185 Bytes=547145)
6 5 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145)

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

SQL> SELECT *
2 FROM
3 (
4 SELECT COUNT(*) OVER() CNT, ROWNUM RN, A.*
5 FROM
6 (
7 SELECT OBJECT_TYPE, CREATED, COUNT(*) CN
8 FROM T
9 GROUP BY OBJECT_TYPE, CREATED
10 ORDER BY CREATED
11 ) A
12 )
13 WHERE RN >= 10 AND RN < 20;

CNT RN OBJECT_TYPE CREATED CN
---------- ---------- ------------------ ------------------- ----------
3570 10 TABLE 2003-11-13 01:41:01 16
3570 11 CLUSTER 2003-11-13 01:41:02 3
3570 12 INDEX 2003-11-13 01:41:02 31
3570 13 LOB 2003-11-13 01:41:02 2
3570 14 SEQUENCE 2003-11-13 01:41:02 4
3570 15 TABLE 2003-11-13 01:41:02 20
3570 16 INDEX 2003-11-13 01:41:03 16
3570 17 LOB 2003-11-13 01:41:03 6
3570 18 SEQUENCE 2003-11-13 01:41:03 2
3570 19 SYNONYM 2003-11-13 01:41:03 1

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=32185 Bytes=1898915)
1 0 VIEW (Cost=97 Card=32185 Bytes=1898915)
2 1 WINDOW (BUFFER) (Cost=97 Card=32185 Bytes=1062105)
3 2 COUNT
4 3 VIEW (Cost=97 Card=32185 Bytes=1062105)
5 4 SORT (GROUP BY) (Cost=97 Card=32185 Bytes=547145)
6 5 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145)

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

第一種方法采用了標準分頁方式，效率相對更高一些，但是第一種方法需要對原始SQL進行修改，而第二種方式不需要修改原始SQL，直接在原始SQL外面添加一些代碼就可以實現。

“Oracle分頁查詢格式有哪些”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！