Oracle分頁查詢格式指的是什么呢

今天就跟大家聊聊有關Oracle分頁查詢格式指的是什么呢，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結了以下內容，希望大家根據這篇文章可以有所收獲。

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

綜合來說，除了SQL嵌套可以少寫一層外，并沒有什么特別的優點來代替標準分頁函數的寫法。

不過上一篇測試所有的數據都是通過全表掃描得到的，如果在排序字段上存在索引，這兩種不同的分頁查詢效率如何呢，還是繼續進行測試：

SQL> ALTER TABLE T MODIFY OBJECT_NAME NOT NULL; 

表已更改。

SQL> CREATE INDEX IND_T_OBJECT_NAME ON T (OBJECT_NAME);

索引已創建。

為了Oracle可以利用這個索引，將索引列置為非空，首先測試標準分頁SQL語句：

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
  8             ORDER BY OBJECT_NAME
  9     )
 10     WHERE ROWNUM <= 20
 11  )
 12  WHERE RN >= 11;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant

已選擇10行。

已用時間:  00: 00: 00.05

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (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=4584838 Bytes=362202202)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=4584838 Bytes=132960302)
   5    4           INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=4584838)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          3  physical reads
          0  redo size
        578  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中為了使用索引和NESTED LOOP連接方式，一般還要加上FIRST_ROWS提示，現在還沒有加上FIRST_ROWS提示，Oracle就使用了索引全掃描代替了全表掃描，而且效率相當的高，只需要0.5秒就返回了結果。

再看分析函數的表現：

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

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant

已選擇10行。

已用時間:  00: 01: 09.17

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=826 Card=4584838 Bytes=421805096)
   1    0   VIEW (Cost=826 Card=4584838 Bytes=421805096)
   2    1     WINDOW (NOSORT) (Cost=826 Card=4584838 Bytes=132960302)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=4584838 Bytes=132960302)
   4    3         INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=4584838)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    3197229  consistent gets
     118443  physical reads
          0  redo size
        578  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 OBJECT_NAME, OBJECT_ID, 
  5             ROW_NUMBER() OVER(ORDER BY OBJECT_NAME) RN
  6     FROM T
  7  )
  8  WHERE RN BETWEEN 11 AND 20;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant

已選擇10行。

已用時間:  00: 00: 10.65

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=826 Card=4584838 Bytes=421805096)
   1    0   VIEW (Cost=826 Card=4584838 Bytes=421805096)
   2    1     WINDOW (NOSORT) (Cost=826 Card=4584838 Bytes=132960302)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=4584838 Bytes=132960302)
   4    3         INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=4584838)

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

如果說第一次執行是由于大量物理讀沒有緩存，導致執行時間達到了1分鐘的話，那么第二次執行仍舊高得離譜的三百多萬的邏輯讀，就很說明問題了。執行時間居然要10秒多，比全表掃描效率還低，看執行計劃就知道，這次STOP KEY沒有被推到分析函數的窗口排序中，導致Oracle掃描了所有的記錄。

這對于分頁來說，絕對是不可接受的。不過這是在9i的環境下進行的測試：

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

看看10g中Oracle是否解決了這個問題：

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

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

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     30166 /1000e8d1_LinkedHashMapValueIt
     30165 /1000e8d1_LinkedHashMapValueIt
     30166 /1000e8d1_LinkedHashMapValueIt
     30165 /1000e8d1_LinkedHashMapValueIt
     30165 /1000e8d1_LinkedHashMapValueIt
     30165 /1000e8d1_LinkedHashMapValueIt
     30165 /1000e8d1_LinkedHashMapValueIt
     30165 /1000e8d1_LinkedHashMapValueIt
     30165 /1000e8d1_LinkedHashMapValueIt
     30165 /1000e8d1_LinkedHashMapValueIt

10 rows selected.

Elapsed: 00:00:02.04

Execution Plan
----------------------------------------------------------
Plan hash value: 3047187157

-----------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |  4969K|   436M|       | 41652   (1)| 00:09:44 |
|*  1 |  VIEW                    |      |  4969K|   436M|       | 41652   (1)| 00:09:44 |
|*  2 |   WINDOW SORT PUSHED RANK|      |  4969K|   132M|   342M| 41652   (1)| 00:09:44 |
|   3 |    TABLE ACCESS FULL     | T    |  4969K|   132M|       | 17375   (1)| 00:04:04 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN">=11 AND "RN"<=20)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_NAME")<=20)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      52137  consistent gets
          0  physical reads
          0  redo size
        725  bytes sent via SQL*Net to client
        492  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 */ OBJECT_ID, OBJECT_NAME
  2  FROM
  3  (
  4     SELECT OBJECT_NAME, OBJECT_ID, 
  5             ROW_NUMBER() OVER(ORDER BY OBJECT_NAME) RN
  6     FROM T
  7  )
  8  WHERE RN BETWEEN 11 AND 20;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     30165 /1000e8d1_LinkedHashMapValueIt
     30166 /1000e8d1_LinkedHashMapValueIt
     30165 /1000e8d1_LinkedHashMapValueIt
     30166 /1000e8d1_LinkedHashMapValueIt
     30165 /1000e8d1_LinkedHashMapValueIt
     30166 /1000e8d1_LinkedHashMapValueIt
     30165 /1000e8d1_LinkedHashMapValueIt
     30166 /1000e8d1_LinkedHashMapValueIt
     30165 /1000e8d1_LinkedHashMapValueIt
     30166 /1000e8d1_LinkedHashMapValueIt

10 rows selected.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3257002816

-----------------------------------------------------------------------------------------
|Id |Operation                     |Name             |Rows  |Bytes|Cost (%CPU)|Time     |
-----------------------------------------------------------------------------------------
|  0|SELECT STATEMENT              |                 | 4969K| 436M| 3679K  (1)|14:18:35 |
|* 1| VIEW                         |                 | 4969K| 436M| 3679K  (1)|14:18:35 |
|* 2|  WINDOW NOSORT STOPKEY       |                 | 4969K| 132M| 3679K  (1)|14:18:35 |
|  3|   TABLE ACCESS BY INDEX ROWID|T                | 4969K| 132M| 3679K  (1)|14:18:35 |
|  4|    INDEX FULL SCAN           |IND_T_OBJECT_NAME| 4969K|     |11703   (1)|00:02:44 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN">=11 AND "RN"<=20)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_NAME")<=20)

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

10g中表的結構與數據量和9i完全一致，但是默認情況下，Oracle并沒有選擇使用索引掃描的方式。如果在SQL中加上FIRST_ROWS提示，那么Oracle選擇索引掃描，并以接近0秒的速度將結果返回。

對比9i和10g采用分析函數分頁的執行計劃可以發現，92的執行計劃為WINDOW (NOSORT)，而102為WINDOW NOSORT STOPKEY。顯然Oracle在10g解決了9i存在的問題，這也是在上一篇文章中提到的，Oracle可能會不斷完善分析函數的功能。

如果總結一下，10g中使用分析函數來進行分頁，已經沒有什么問題了，但是在9i中，用分析函數的方式進行分頁，可能會帶來嚴重的性能問題。

看完上述內容，你們對Oracle分頁查詢格式指的是什么呢有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注億速云行業資訊頻道，感謝大家的支持。