這篇文章主要介紹“Oracle分頁(yè)查詢語(yǔ)句舉例分析”，在日常操作中，相信很多人在Oracle分頁(yè)查詢語(yǔ)句舉例分析問(wèn)題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”O(jiān)racle分頁(yè)查詢語(yǔ)句舉例分析”的疑惑有所幫助！接下來(lái)，請(qǐng)跟著小編一起來(lái)學(xué)習(xí)吧！
為雙橋等地區(qū)用戶提供了全套網(wǎng)頁(yè)設(shè)計(jì)制作服務(wù)，及雙橋網(wǎng)站建設(shè)行業(yè)解決方案。主營(yíng)業(yè)務(wù)為做網(wǎng)站、成都網(wǎng)站設(shè)計(jì)、雙橋網(wǎng)站設(shè)計(jì)，以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠(chéng)的服務(wù)。我們深信只要達(dá)到每一位用戶的要求，就會(huì)得到認(rèn)可，從而選擇與我們長(zhǎng)期合作。這樣，我們也可以走得更遠(yuǎn)！
ORDER BY STOPKEY和ORDER BY在翻頁(yè)查詢的最后幾頁(yè)的性能差異：
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 過(guò)程已成功完成。
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
對(duì)于翻頁(yè)查詢的前幾頁(yè)，采用ORDER BY STOPKEY的方式比ORDER BY性能上有很大的優(yōu)勢(shì)，那么對(duì)于分頁(yè)查詢的最后幾頁(yè)，ORDER BY STOPKEY是否和其他分頁(yè)查詢技術(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的方式在分頁(yè)查詢的最后幾頁(yè)性能也有明顯的下降，但是在和普通的ORDER BY相比，無(wú)論從邏輯讀、物理讀還是從執(zhí)行時(shí)間上看，二者都屬于一個(gè)數(shù)量級(jí)上的。
看來(lái)ORDER BY STOPKEY排序方式，在STOPKEY接近排序總量的時(shí)候也不會(huì)有明顯的性能下降。
到此，關(guān)于“Oracle分頁(yè)查詢語(yǔ)句舉例分析”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)?lái)更多實(shí)用的文章！
重慶分公司
重慶分公司
