目錄
分頁(yè)查詢框架
分頁(yè)查詢注意事項(xiàng)?
有序/無(wú)序分頁(yè)
事務(wù)帶來(lái)的影響
分頁(yè)查詢與索引
排序字段索引實(shí)驗(yàn)
組合索引實(shí)驗(yàn)
利用ROWNUM進(jìn)行分頁(yè)查詢的方法在各版本都是適用的，11g,12c,19c都可以使用該方法哦。在分享分頁(yè)查詢方法之前，需要先聊下ROWNUM，這玩意兒要是聊不清楚，那這個(gè)方法就不太好理解。
- 偽列
ROWNUM是Oracle的一個(gè)偽列，并不真實(shí)存在于表結(jié)構(gòu)中。
- 行號(hào)
ROWNUM作用記錄是返回結(jié)果集中的每一行的行號(hào)，是在查詢結(jié)果返回之后才計(jì)算的。
在了解ROWNUM以上兩個(gè)特性之后，可以開(kāi)始分享根據(jù)ROWNUM進(jìn)行的分頁(yè)查詢方法了。?
分頁(yè)查詢框架
SELECT *
FROM(SELECT *
FROM (SELECT
sp.*,
ROWNUM rn
FROM (/*需要分頁(yè)的SQL*/) sp)
WHERE ROWNUM <= x)
WHERE rn >=y在上述代碼中
x表示查詢的結(jié)束行
y表示查詢的起始行
分頁(yè)查詢注意事項(xiàng)?
有序/無(wú)序分頁(yè)
Attention Please！?。?/strong>
下面將是一大段文字描述，因?yàn)槲以趧偨佑|分頁(yè)查詢的時(shí)候吃了不少虧，也有許多不理解的地方，現(xiàn)在我把我的淺薄理解寫出來(lái)，供大家參考！
如果您時(shí)間寶貴與緊張，可以不看下面的描述，只需要記住一點(diǎn)：
進(jìn)行分頁(yè)查詢優(yōu)化的目標(biāo)SQL需要根據(jù)實(shí)際場(chǎng)景看是否進(jìn)行排序！
分頁(yè)查詢，顧名思義即為將表中的數(shù)據(jù)分成若干頁(yè)，且指定每頁(yè)行數(shù)進(jìn)行展示；目的就是為了避免目標(biāo)表中的數(shù)據(jù)量太大，而一次性查詢?nèi)恳鸬牟樵冃实拖?。大家可以想象一下，我們?cè)陂喿x一本新書的時(shí)候，是用什么樣的方式閱讀呢？正常人肯定是從第1頁(yè)開(kāi)始，一頁(yè)一頁(yè)的往后按照順序進(jìn)行閱讀。Oracle如果擬人化肯定也是個(gè)正常人，因?yàn)樗龅囊磺卸际呛虾趵硇缘模凰矔?huì)從第一頁(yè)開(kāi)始按照順序往后閱讀。
那么重點(diǎn)就來(lái)了：“順序”。
在做分頁(yè)查詢的時(shí)候，是需要保證進(jìn)行分頁(yè)查詢的目標(biāo)SQL要有一個(gè)合理的排序。前文已經(jīng)敘述過(guò)ROWNUM是在查詢返回后計(jì)算的一個(gè)行號(hào)，如果查詢的結(jié)果集本身是排序是混亂的，那么具體每頁(yè)展示的數(shù)據(jù)就不會(huì)是我們期待的一個(gè)結(jié)果。
用我們?cè)趯W(xué)習(xí)Oracle時(shí)的一個(gè)老朋友scott用戶舉個(gè)例子，scott用戶下有張EMP表，表里有各個(gè)職員的薪水。在對(duì) “SELECT * FROM EMP” 這個(gè)SQL進(jìn)行分頁(yè)查詢優(yōu)化時(shí)，如果按照薪水從高到低的需要去查看這些數(shù)據(jù)，那么理想分頁(yè)情況就應(yīng)該是第1頁(yè)展示薪水排前N名的職工信息，第2頁(yè)展示薪水排第N+1~2N名的職工信息，以此類推。但如果不對(duì)salary字段進(jìn)行降序查詢的話，是達(dá)不到期待效果的。
例如要查詢公司薪水排名6~10的員工信息，以scott.emp表為例子進(jìn)行查詢，那么分頁(yè)查詢SQL代碼如下。
SELECT *
FROM(SELECT *
FROM (SELECT
sp.*,
ROWNUM rn
FROM (SELECT * FROM emp ORDER BY sal DESC) sp)
WHERE ROWNUM <= 10)
WHERE rn >=6當(dāng)然了，如果您覺(jué)得無(wú)序分頁(yè)對(duì)您的查詢沒(méi)有什么影響的話，也就沒(méi)有必要進(jìn)行排序查詢了；這個(gè)肯定還是要根據(jù)實(shí)際場(chǎng)景來(lái)決定。
事務(wù)帶來(lái)的影響
想象一下，您在閱讀的是一本電子書，您已經(jīng)閱讀完當(dāng)前頁(yè)了，就開(kāi)始往后翻，但是這個(gè)叼電子書系統(tǒng)突然抽風(fēng)把您已經(jīng)閱讀過(guò)的前面頁(yè)數(shù)的內(nèi)容更改了，這個(gè)時(shí)候您讀到的信息就不一定是準(zhǔn)確的了。
Oracle也一樣，可能每時(shí)每刻都在發(fā)生著事務(wù)；這些事務(wù)都會(huì)對(duì)正在進(jìn)行分頁(yè)查詢的SQL結(jié)果集造成影響，所以在進(jìn)行分頁(yè)查詢時(shí)需要考慮數(shù)據(jù)的一致性。有些分頁(yè)查詢的場(chǎng)景是不需要考慮事務(wù)帶來(lái)的數(shù)據(jù)變化；但有的場(chǎng)景是需要的，就比如說(shuō)做ETL的，在同步數(shù)據(jù)到數(shù)據(jù)倉(cāng)庫(kù)的時(shí)候，就需要考慮這些事務(wù)帶來(lái)的影響。
分頁(yè)查詢與索引
這里所指的分頁(yè)查詢是有序分頁(yè)。
如果您的查詢SQL有進(jìn)行排序的話，那么需要在進(jìn)行排序的字段上建立索引哦。為什么呢？因?yàn)樗饕且呀?jīng)進(jìn)行過(guò)排序的，可以利用索引的這個(gè)特性來(lái)進(jìn)一步優(yōu)化分頁(yè)語(yǔ)句。
下面做個(gè)小實(shí)驗(yàn)哦。（我下面對(duì)實(shí)驗(yàn)分個(gè)三級(jí)標(biāo)題哦，可以讓整篇文章看著更清晰些。同時(shí)也感覺(jué)我的排版能力菜的一批！）
排序字段索引實(shí)驗(yàn)
- 先建立一張測(cè)試表
create table HR.spage_0406 as select * from dba_objects
- 進(jìn)行分析查詢改寫
SELECT *
FROM(SELECT *
FROM (SELECT
sp.*,
ROWNUM rn
FROM (select * from HR.spage_0406 order by object_id) sp)
WHERE ROWNUM <= 10)
WHERE rn >=1- 查看當(dāng)前分頁(yè)查詢執(zhí)行計(jì)劃
可以發(fā)現(xiàn)現(xiàn)在走的是全表掃描，且A-ROWS是72695
SQL_ID 9xkcnduur1d6p, child number 0
-------------------------------------
SELECT * FROM(SELECT * FROM (SELECT SP.*, ROWNUM RN FROM (SELECT * FROM
HR.SPAGE_0406 ORDER BY OBJECT_ID) SP) WHERE ROWNUM <= 10) WHERE RN >=1
Plan hash value: 2601037360
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 2541 (100)| | 10 |00:00:00.07 | 1416 | | | |
|* 1 | VIEW | | 1 | 10 | 4940 | | 2541 (1)| 00:00:01 | 10 |00:00:00.07 | 1416 | | | |
|* 2 | COUNT STOPKEY | | 1 | | | | | | 10 |00:00:00.07 | 1416 | | | |
| 3 | VIEW | | 1 | 72695 | 34M| | 2541 (1)| 00:00:01 | 10 |00:00:00.07 | 1416 | | | |
| 4 | COUNT | | 1 | | | | | | 10 |00:00:00.07 | 1416 | | | |
| 5 | VIEW | | 1 | 72695 | 33M| | 2541 (1)| 00:00:01 | 10 |00:00:00.07 | 1416 | | | |
| 6 | SORT ORDER BY | | 1 | 72695 | 9370K| 13M| 2541 (1)| 00:00:01 | 10 |00:00:00.07 | 1416 | 14M| 1431K| 12M (0)|
| 7 | TABLE ACCESS FULL| SPAGE_0406 | 1 | 72695 | 9370K| | 395 (1)| 00:00:01 | 72695 |00:00:00.01 | 1416 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
- 給排序字段添加索引
create index IDX_SPAGE_OBJECTID on HR.spage_0406(object_id,0)- 再次查看分頁(yè)查詢執(zhí)行計(jì)劃
可以發(fā)現(xiàn)現(xiàn)在走的是索引全掃描，且A-ROWS是10。現(xiàn)在這張表還是不夠大，還是體現(xiàn)不出來(lái)這種優(yōu)化方式的優(yōu)勢(shì)，越大的表越能實(shí)際感受的到它的優(yōu)勢(shì)。
SQL_ID 9xkcnduur1d6p, child number 0
-------------------------------------
SELECT * FROM(SELECT * FROM (SELECT SP.*, ROWNUM RN FROM (SELECT * FROM
HR.SPAGE_0406 ORDER BY OBJECT_ID) SP) WHERE ROWNUM <= 10) WHERE RN >=1
Plan hash value: 1210249890
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1762 (100)| | 10 |00:00:00.01 | 3 |
|* 1 | VIEW | | 1 | 10 | 4940 | 1762 (1)| 00:00:01 | 10 |00:00:00.01 | 3 |
|* 2 | COUNT STOPKEY | | 1 | | | | | 10 |00:00:00.01 | 3 |
| 3 | VIEW | | 1 | 72695 | 34M| 1762 (1)| 00:00:01 | 10 |00:00:00.01 | 3 |
| 4 | COUNT | | 1 | | | | | 10 |00:00:00.01 | 3 |
| 5 | VIEW | | 1 | 72695 | 33M| 1762 (1)| 00:00:01 | 10 |00:00:00.01 | 3 |
| 6 | TABLE ACCESS BY INDEX ROWID| SPAGE_0406 | 1 | 72695 | 9370K| 1762 (1)| 00:00:01 | 10 |00:00:00.01 | 3 |
| 7 | INDEX FULL SCAN | IDX_SPAGE_OBJECTID | 1 | 72695 | | 182 (0)| 00:00:01 | 10 |00:00:00.01 | 2 |
-------------------------------------------------------------------------------------------------------------------------------------------------組合索引實(shí)驗(yàn)
那么分頁(yè)查詢與索引的故事到這里就結(jié)束了嗎？當(dāng)然不是啦，還有還有呢。請(qǐng)大家耐心看下面的敘述哦！
上面的查詢是沒(méi)有謂詞過(guò)濾的，也就是WHERE條件。如果查詢中有謂詞條件，大家是可以考慮創(chuàng)建聯(lián)合索引；將謂詞字段與排序字段放在一起創(chuàng)建組合索引，且盡量將排序字段作為組合索引的前導(dǎo)列，也就是創(chuàng)建組合索引時(shí)的一個(gè)字段。
例如下面這個(gè)分頁(yè)查詢，加進(jìn)了謂詞過(guò)濾，執(zhí)行計(jì)劃立馬就變差了。
SELECT *
FROM(SELECT *
FROM (SELECT
sp.*,
ROWNUM rn
FROM (select * from HR.spage_0406 where owner='SYS' order by object_id) sp)
WHERE ROWNUM <= 10)
WHERE rn >=1SQL_ID 67hjvw5r90c9g, child number 1
-------------------------------------
SELECT * FROM(SELECT * FROM (SELECT SP.*, ROWNUM RN FROM (SELECT * FROM
HR.SPAGE_0406 WHERE OWNER='SYS' ORDER BY OBJECT_ID) SP) WHERE ROWNUM <=
10) WHERE RN >=1
Plan hash value: 2601037360
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 483 (100)| | 10 |00:00:00.04 | 1416 | | | |
|* 1 | VIEW | | 1 | 10 | 4940 | | 483 (1)| 00:00:01 | 10 |00:00:00.04 | 1416 | | | |
|* 2 | COUNT STOPKEY | | 1 | | | | | | 10 |00:00:00.04 | 1416 | | | |
| 3 | VIEW | | 1 | 2908 | 1402K| | 483 (1)| 00:00:01 | 10 |00:00:00.04 | 1416 | | | |
| 4 | COUNT | | 1 | | | | | | 10 |00:00:00.04 | 1416 | | | |
| 5 | VIEW | | 1 | 2908 | 1365K| | 483 (1)| 00:00:01 | 10 |00:00:00.04 | 1416 | | | |
| 6 | SORT ORDER BY | | 1 | 2908 | 374K| 552K| 483 (1)| 00:00:01 | 10 |00:00:00.04 | 1416 | 10M| 1258K| 9559K (0)|
|* 7 | TABLE ACCESS FULL| SPAGE_0406 | 1 | 2908 | 374K| | 394 (1)| 00:00:01 | 52493 |00:00:00.01 | 1416 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------那么我們可以創(chuàng)建組合索引，代碼如下：
create index IDX_SPAGE_OWID on HR.spage_0406(object_id,owner)
然后再看該分頁(yè)查詢的執(zhí)行計(jì)劃，欸，變好了！
SQL_ID 67hjvw5r90c9g, child number 0
-------------------------------------
SELECT * FROM(SELECT * FROM (SELECT SP.*, ROWNUM RN FROM (SELECT * FROM
HR.SPAGE_0406 WHERE OWNER='SYS' ORDER BY OBJECT_ID) SP) WHERE ROWNUM <=
10) WHERE RN >=1
Plan hash value: 961832651
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 275 (100)| | 10 |00:00:00.01 | 3 | 1 |
|* 1 | VIEW | | 1 | 10 | 4940 | 275 (1)| 00:00:01 | 10 |00:00:00.01 | 3 | 1 |
|* 2 | COUNT STOPKEY | | 1 | | | | | 10 |00:00:00.01 | 3 | 1 |
| 3 | VIEW | | 1 | 2908 | 1402K| 275 (1)| 00:00:01 | 10 |00:00:00.01 | 3 | 1 |
| 4 | COUNT | | 1 | | | | | 10 |00:00:00.01 | 3 | 1 |
| 5 | VIEW | | 1 | 2908 | 1365K| 275 (1)| 00:00:01 | 10 |00:00:00.01 | 3 | 1 |
| 6 | TABLE ACCESS BY INDEX ROWID| SPAGE_0406 | 1 | 2908 | 374K| 275 (1)| 00:00:01 | 10 |00:00:00.01 | 3 | 1 |
|* 7 | INDEX FULL SCAN | IDX_SPAGE_OWID | 1 | 2908 | | 211 (1)| 00:00:01 | 10 |00:00:00.01 | 2 | 1 |
------------------------------------------------------------------------------------------------------------------------------------------------------上面羅里吧嗦了一堆內(nèi)容，但對(duì)于有序分頁(yè)查詢來(lái)講，還有下面兩點(diǎn)內(nèi)容需要提醒大家：
1、索引字段或組合索引先導(dǎo)列字段中的值能否最大程度最充分的完成排序；
2、索引的排序方式和SQL的實(shí)際排序方式是否一致，別一個(gè)是升序一個(gè)是降序；
扯些閑話：
最后我給自己再留一個(gè)作業(yè)吧，上面所有的敘述其實(shí)都是講的單表分頁(yè)查詢；多表分頁(yè)查詢和單表分頁(yè)查詢的框架是一致的，但再進(jìn)一步優(yōu)化上是有區(qū)別的。今天受限于時(shí)間，下次我再對(duì)多表關(guān)聯(lián)分頁(yè)查詢進(jìn)行分享，或者有時(shí)間了我直接在本篇文章基礎(chǔ)上進(jìn)行編輯了。文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-847374.html
最后就是，明天清明假期就結(jié)束了，要開(kāi)始上班了，但我不想上班??????文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-847374.html
到了這里，關(guān)于（Oracle）SQL優(yōu)化技巧（一）：分頁(yè)查詢的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
