Oracle分頁查詢格式（十二）

路途中的人2012發表於2017-06-21
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

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

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

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

Oracle分頁查詢格式（十一）：http://yangtingkun.itpub.net/post/468/485481

 

 

上一篇文章已經介紹了利用分析函式來進行分頁的方法，總的來說，除非分頁到了最後部分，利用分析函式進行分頁的效率還是可以的。

綜合來說，除了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中，用分析函式的方式進行分頁，可能會帶來嚴重的效能問題。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29067253/viewspace-2141087/，如需轉載，請註明出處，否則將追究法律責任。

相關文章