Oracle分頁查詢語句舉例分析

這篇文章主要介紹“Oracle分頁查詢語句舉例分析”，在日常操作中，相信很多人在Oracle分頁查詢語句舉例分析問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”O(jiān)racle分頁查詢語句舉例分析”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！

ORDER BY STOPKEY和ORDER BY在翻頁查詢的最后幾頁的性能差異：

SQL> CREATE TABLE T AS SELECT A.* FROM DBA_OBJECTS A, DBA_USERS B, TAB;

表已創(chuàng)建。

SQL> SELECT COUNT(*) FROM T;

 COUNT(*)
----------
   458064

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

PL/SQL 過程已成功完成。

SQL> SET AUTOT TRACE
SQL> SET TIMING ON
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 ORDER BY TIMESTAMP
 8     )
 9    WHERE ROWNUM <= 20
10   )
11  WHERE RN >= 11;

已選擇10行。

已用時(shí)間:  00: 00: 00.03

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=13888 Card=20 Bytes=1840)
  1    0   VIEW (Cost=13888 Card=20 Bytes=1840)
  2    1     COUNT (STOPKEY)
  3    2       VIEW (Cost=13888 Card=458064 Bytes=36187056)
  4    3         SORT (ORDER BY STOPKEY) (Cost=13888 Card=458064 Bytes=18780624)
  5    4           TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)

Statistics
----------------------------------------------------------
         0  recursive calls
         0  db block gets
      5579  consistent gets
         0  physical reads
         0  redo size
       694  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 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 ORDER BY TIMESTAMP
 8     )
 9   )
10  WHERE RN BETWEEN 11 AND 20;

已選擇10行。

已用時(shí)間:  00: 00: 09.05

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=13888 Card=458064 Bytes=42141888)
  1    0   VIEW (Cost=13888 Card=458064 Bytes=42141888)
  2    1     COUNT
  3    2       VIEW (Cost=13888 Card=458064 Bytes=36187056)
  4    3         SORT (ORDER BY) (Cost=13888 Card=458064 Bytes=18780624)
  5    4           TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)

Statistics
----------------------------------------------------------
         0  recursive calls
        41  db block gets
      5579  consistent gets
      7935  physical reads
         0  redo size
       689  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)
         1  sorts (disk)
        10  rows processed

對于翻頁查詢的前幾頁，采用ORDER BY STOPKEY的方式比ORDER BY性能上有很大的優(yōu)勢，那么對于分頁查詢的最后幾頁，ORDER BY STOPKEY是否和其他分頁查詢技術(shù)一樣，性能比普通方式還要低很多：

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 ORDER BY TIMESTAMP
 8     )
 9    WHERE ROWNUM <= 458060
10   )
11  WHERE RN >= 458051;

已選擇10行。

已用時(shí)間:  00: 00: 09.07

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=13888 Card=458060 Bytes=42141520)
  1    0   VIEW (Cost=13888 Card=458060 Bytes=42141520)
  2    1     COUNT (STOPKEY)
  3    2       VIEW (Cost=13888 Card=458064 Bytes=36187056)
  4    3         SORT (ORDER BY STOPKEY) (Cost=13888 Card=458064 Bytes=18780624)
  5    4           TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)

Statistics
----------------------------------------------------------
         0  recursive calls
        41  db block gets
      5579  consistent gets
      7933  physical reads
         0  redo size
       667  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)
         1  sorts (disk)
        10  rows processed

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 ORDER BY TIMESTAMP
 8     )
 9   )
10  WHERE RN BETWEEN 458051 AND 458060;

已選擇10行。

已用時(shí)間:  00: 00: 10.01

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=13888 Card=458064 Bytes=42141888)
  1    0   VIEW (Cost=13888 Card=458064 Bytes=42141888)
  2    1     COUNT
  3    2       VIEW (Cost=13888 Card=458064 Bytes=36187056)
  4    3         SORT (ORDER BY) (Cost=13888 Card=458064 Bytes=18780624)
  5    4           TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)

Statistics
----------------------------------------------------------
         0  recursive calls
        41  db block gets
      5579  consistent gets
      7935  physical reads
         0  redo size
       649  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)
         1  sorts (disk)
        10  rows processed

出乎意料的是，雖然ORDER BY STOPKEY的方式在分頁查詢的最后幾頁性能也有明顯的下降，但是在和普通的ORDER BY相比，無論從邏輯讀、物理讀還是從執(zhí)行時(shí)間上看，二者都屬于一個(gè)數(shù)量級上的。

看來ORDER BY STOPKEY排序方式，在STOPKEY接近排序總量的時(shí)候也不會(huì)有明顯的性能下降。

到此，關(guān)于“Oracle分頁查詢語句舉例分析”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注億速云網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！