今天就跟大家聊聊有關(guān)Oracle分頁查詢格式指的是什么呢，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。
為鄒平等地區(qū)用戶提供了全套網(wǎng)頁設(shè)計(jì)制作服務(wù)，及鄒平網(wǎng)站建設(shè)行業(yè)解決方案。主營業(yè)務(wù)為成都網(wǎng)站建設(shè)、網(wǎng)站建設(shè)、鄒平網(wǎng)站設(shè)計(jì)，以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠的服務(wù)。我們深信只要達(dá)到每一位用戶的要求，就會(huì)得到認(rèn)可，從而選擇與我們長(zhǎng)期合作。這樣，我們也可以走得更遠(yuǎn)！
Oracle的分頁查詢語句基本上可以按照本文給出的格式來進(jìn)行套用。
綜合來說，除了SQL嵌套可以少寫一層外，并沒有什么特別的優(yōu)點(diǎn)來代替標(biāo)準(zhǔn)分頁函數(shù)的寫法。
不過上一篇測(cè)試所有的數(shù)據(jù)都是通過全表掃描得到的，如果在排序字段上存在索引，這兩種不同的分頁查詢效率如何呢，還是繼續(xù)進(jìn)行測(cè)試：
SQL> ALTER TABLE T MODIFY OBJECT_NAME NOT NULL;
表已更改。
SQL> CREATE INDEX IND_T_OBJECT_NAME ON T (OBJECT_NAME);
索引已創(chuàng)建。
為了Oracle可以利用這個(gè)索引，將索引列置為非空，首先測(cè)試標(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行。
已用時(shí)間: 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ù)母?，只需?.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行。
已用時(shí)間: 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行。
已用時(shí)間: 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í)行時(shí)間達(dá)到了1分鐘的話，那么第二次執(zhí)行仍舊高得離譜的三百多萬的邏輯讀，就很說明問題了。執(zhí)行時(shí)間居然要10秒多，比全表掃描效率還低，看執(zhí)行計(jì)劃就知道，這次STOP KEY沒有被推到分析函數(shù)的窗口排序中，導(dǎo)致Oracle掃描了所有的記錄。
這對(duì)于分頁來說，絕對(duì)是不可接受的。不過這是在9i的環(huán)境下進(jìn)行的測(cè)試：
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是否解決了這個(gè)問題：
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é)果返回。
對(duì)比9i和10g采用分析函數(shù)分頁的執(zhí)行計(jì)劃可以發(fā)現(xiàn)，92的執(zhí)行計(jì)劃為WINDOW (NOSORT)，而102為WINDOW NOSORT STOPKEY。顯然Oracle在10g解決了9i存在的問題，這也是在上一篇文章中提到的，Oracle可能會(huì)不斷完善分析函數(shù)的功能。
如果總結(jié)一下，10g中使用分析函數(shù)來進(jìn)行分頁，已經(jīng)沒有什么問題了，但是在9i中，用分析函數(shù)的方式進(jìn)行分頁，可能會(huì)帶來嚴(yán)重的性能問題。
看完上述內(nèi)容，你們對(duì)Oracle分頁查詢格式指的是什么呢有進(jìn)一步的了解嗎？如果還想了解更多知識(shí)或者相關(guān)內(nèi)容，請(qǐng)關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝大家的支持。
重慶分公司
重慶分公司
