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

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

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

綜合來說，除了SQL嵌套可以少寫一層外，并沒有什么特別的優(yōu)點(diǎn)來代替標(biāo)準(zhǔn)分頁函數(shù)的寫法。

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

SQL> ALTER TABLE T MODIFY OBJECT_NAME NOT NULL; 

表已更改。

SQL> CREATE INDEX IND_T_OBJECT_NAME ON T (OBJECT_NAME);

索引已創(chuàng)建。

為了Oracle可以利用這個索引，將索引列置為非空，首先測試標(biāo)準(zhǔn)分頁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

在標(biāo)準(zhǔn)SQL中為了使用索引和NESTED LOOP連接方式，一般還要加上FIRST_ROWS提示，現(xiàn)在還沒有加上FIRST_ROWS提示，Oracle就使用了索引全掃描代替了全表掃描，而且效率相當(dāng)?shù)母?，只需?span lang="EN-US">0.5秒就返回了結(jié)果。

再看分析函數(shù)的表現(xiàn)：

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

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

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

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中表的結(jié)構(gòu)與數(shù)據(jù)量和9i完全一致，但是默認(rèn)情況下，Oracle并沒有選擇使用索引掃描的方式。如果在SQL中加上FIRST_ROWS提示，那么Oracle選擇索引掃描，并以接近0秒的速度將結(jié)果返回。

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

如果總結(jié)一下，10g中使用分析函數(shù)來進(jìn)行分頁，已經(jīng)沒有什么問題了，但是在9i中，用分析函數(shù)的方式進(jìn)行分頁，可能會帶來嚴(yán)重的性能問題。

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