這篇文章主要介紹“數(shù)據(jù)庫(kù)分頁(yè)查詢(xún)的排序問(wèn)題分析”，在日常操作中，相信很多人在數(shù)據(jù)庫(kù)分頁(yè)查詢(xún)的排序問(wèn)題分析問(wèn)題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”數(shù)據(jù)庫(kù)分頁(yè)查詢(xún)的排序問(wèn)題分析”的疑惑有所幫助！接下來(lái)，請(qǐng)跟著小編一起來(lái)學(xué)習(xí)吧！
創(chuàng)新互聯(lián)建站致力于互聯(lián)網(wǎng)網(wǎng)站建設(shè)與網(wǎng)站營(yíng)銷(xiāo)，提供網(wǎng)站設(shè)計(jì)、成都網(wǎng)站制作、網(wǎng)站開(kāi)發(fā)、seo優(yōu)化、網(wǎng)站排名、互聯(lián)網(wǎng)營(yíng)銷(xiāo)、小程序開(kāi)發(fā)、公眾號(hào)商城、等建站開(kāi)發(fā)，創(chuàng)新互聯(lián)建站網(wǎng)站建設(shè)策劃專(zhuān)家，為不同類(lèi)型的客戶提供良好的互聯(lián)網(wǎng)應(yīng)用定制解決方案，幫助客戶在新的全球化互聯(lián)網(wǎng)環(huán)境中保持優(yōu)勢(shì)。
SQL> CREATE TABLE TEST AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;
表已創(chuàng)建。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST')
PL/SQL 過(guò)程已成功完成。
SQL> SET AUTOT ON EXP
SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER
9 )
10 WHERE ROWNUM <= 10
11 )
12 WHERE RN >= 1;
ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6231 AL CCC
6232 AL_I_2 CCC
6233 AL_I_FNAME_STATUS CCC
6236 BCB CCC
6235 AL_U1 CCC
6234 AL_P CCC
6240 BCF_U1 CCC
6239 BCF_P CCC
6238 BCF CCC
6237 BCB_U1 CCC
已選擇10行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=10 Bytes=1090)
1 0 VIEW (Cost=72 Card=10 Bytes=1090)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY STOPKEY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)
上面例子給出的就是分頁(yè)查詢(xún)的標(biāo)準(zhǔn)寫(xiě)法，對(duì)于查詢(xún)前N條數(shù)據(jù)具有最高的效率。
但是這種分頁(yè)排序語(yǔ)句存在一個(gè)問(wèn)題：
SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER
9 )
10 WHERE ROWNUM <= 20
11 )
12 WHERE RN >= 11;
ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6249 BP_P CCC
6248 BP_I_DEVICE_HANDLE_STATUS CCC
6247 BP CCC
6245 BDF_P CCC
6243 BDF_I_BS_KEY CCC
6241 BCF_U2 CCC
6239 BCF_P CCC
6237 BCB_U1 CCC
6236 BCB CCC
6235 AL_U1 CCC
已選擇10行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=20 Bytes=2180)
1 0 VIEW (Cost=72 Card=20 Bytes=2180)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY STOPKEY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)
對(duì)比這次的結(jié)果和第一次的結(jié)果，就會(huì)發(fā)現(xiàn)ID為6235的數(shù)據(jù)出現(xiàn)了兩次。第一次在前10條返回記錄中，6235出現(xiàn)了，而第二次在11到第20條記錄中，6235又出現(xiàn)了。一條數(shù)據(jù)重復(fù)出現(xiàn)兩次，就必然意味著有數(shù)據(jù)在兩次查詢(xún)中都不會(huì)出現(xiàn)。
其實(shí)造成這個(gè)問(wèn)題的原因很簡(jiǎn)單，是由于排序列不唯一造成的。Oracle這里使用的排序算法不具有穩(wěn)定性，也就是說(shuō)，對(duì)于鍵值相等的數(shù)據(jù)，這種算法完成排序后，不保證這些鍵值相等的數(shù)據(jù)保持排序前的順序。
在這個(gè)例子中，OWNER列包含了大量鍵值為CCC的數(shù)據(jù)，而且Oracle的排序算法不具有穩(wěn)定性，因此前10行記錄和前20行記錄中鍵值的順序不能保證一致。因此，就造成某些數(shù)據(jù)會(huì)重復(fù)出現(xiàn)，而有些數(shù)據(jù)不會(huì)出現(xiàn)的現(xiàn)象。
解決這個(gè)問(wèn)題其實(shí)也很簡(jiǎn)單。有兩種方法可以考慮。
一，在使用不唯一的字段排序時(shí)，后面跟一個(gè)唯一的字段。
SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER, ID
9 )
10 WHERE ROWNUM <= 10
11 )
12 WHERE RN >= 1;
ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6231 AL CCC
6232 AL_I_2 CCC
6233 AL_I_FNAME_STATUS CCC
6234 AL_P CCC
6235 AL_U1 CCC
6236 BCB CCC
6237 BCB_U1 CCC
6238 BCF CCC
6239 BCF_P CCC
6240 BCF_U1 CCC
已選擇10行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=10 Bytes=1090)
1 0 VIEW (Cost=72 Card=10 Bytes=1090)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY STOPKEY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)
SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER, ID
9 )
10 WHERE ROWNUM <= 20
11 )
12 WHERE RN >= 11;
ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6241 BCF_U2 CCC
6242 BDF CCC
6243 BDF_I_BS_KEY CCC
6244 BDF_I_DF_KEY CCC
6245 BDF_P CCC
6246 BDF_U1 CCC
6247 BP CCC
6248 BP_I_DEVICE_HANDLE_STATUS CCC
6249 BP_P CCC
6250 BP_U1 CCC
已選擇10行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=20 Bytes=2180)
1 0 VIEW (Cost=72 Card=20 Bytes=2180)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY STOPKEY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)
一般在排序字段后面跟一個(gè)主鍵就可以了，如果表不存在主鍵，跟ROWID也可以。
這種方法最簡(jiǎn)單，且對(duì)性能的影響最小。另一種方法就是使用前面給出過(guò)多次的BETWEEN AND的方法。
SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER
9 )
10 )
11 WHERE RN BETWEEN 1 AND 10;
ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6231 AL CCC
6232 AL_I_2 CCC
6233 AL_I_FNAME_STATUS CCC
6234 AL_P CCC
6238 BCF CCC
6240 BCF_U1 CCC
6242 BDF CCC
6244 BDF_I_DF_KEY CCC
6246 BDF_U1 CCC
6255 BRL_U1 CCC
已選擇10行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=6363 Bytes=693567)
1 0 VIEW (Cost=72 Card=6363 Bytes=693567)
2 1 COUNT
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)
SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER
9 )
10 )
11 WHERE RN BETWEEN 11 AND 20;
ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6254 BRL_P CCC
6253 BRL_I_DTS CCC
6252 BRL_I_BS_KEY CCC
6251 BRL CCC
6250 BP_U1 CCC
6249 BP_P CCC
6248 BP_I_DEVICE_HANDLE_STATUS CCC
6247 BP CCC
6264 CCF CCC
6263 CCB_U1 CCC
已選擇10行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=6363 Bytes=693567)
1 0 VIEW (Cost=72 Card=6363 Bytes=693567)
2 1 COUNT
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)
這種方式由于采用表數(shù)據(jù)的全排序，每次只取全排序中的某一部分?jǐn)?shù)據(jù)，因此不會(huì)出現(xiàn)上面提到的重復(fù)數(shù)據(jù)問(wèn)題。
但是正是由于使用了全排序，而且ROWNUM信息無(wú)法推到查詢(xún)內(nèi)部，導(dǎo)致這種寫(xiě)法的執(zhí)行效率很低。
到此，關(guān)于“數(shù)據(jù)庫(kù)分頁(yè)查詢(xún)的排序問(wèn)題分析”的學(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í)用的文章！
重慶分公司
重慶分公司
