Oracle分頁查詢語句（三）

Oracle的分頁查詢語句基本上可以按照本文給出的格式來進行套用。

創(chuàng)新互聯(lián)專注為客戶提供全方位的互聯(lián)網綜合服務，包含不限于網站設計制作、成都網站設計、慈溪網絡推廣、微信小程序定制開發(fā)、慈溪網絡營銷、慈溪企業(yè)策劃、慈溪品牌公關、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運營等，從售前售中售后，我們都將竭誠為您服務，您的肯定，是我們最大的嘉獎；創(chuàng)新互聯(lián)為所有大學生創(chuàng)業(yè)者提供慈溪建站搭建服務，24小時服務熱線：18980820575，官方網址：muchs.cn

Oracle分頁查詢語句（一）：http://yangtingkun.itpub.net/post/468/100278

Oracle分頁查詢語句（二）：http://yangtingkun.itpub.net/post/468/101703

繼續(xù)看查詢的第二種情況，包含表連接的情況：

SQL> CREATE TABLE T AS SELECT * FROM DBA_USERS;

表已創(chuàng)建。

SQL> CREATE TABLE T1 AS SELECT * FROM DBA_SOURCE;

表已創(chuàng)建。

SQL> ALTER TABLE T ADD CONSTRAINT PK_T PRIMARY KEY (USERNAME);

表已更改。

SQL> ALTER TABLE T1 ADD CONSTRAINT FK_T1_OWNER FOREIGN KEY (OWNER)
2 REFERENCES T(USERNAME);

表已更改。

SQL> CREATE INDEX IND_T1_OWNER ON T1(NAME);

索引已創(chuàng)建。

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

PL/SQL 過程已成功完成。

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

PL/SQL 過程已成功完成。

創(chuàng)建了T表和T1表，默認情況下，HASH JOIN的效率要比NESTED LOOP高很多：

SQL> SET AUTOT TRACE
SQL> SELECT * FROM T, T1 WHERE T.USERNAME = T1.OWNER;

已選擇96985行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=844 Card=96985 Bytes=46164860)
1 0 HASH JOIN (Cost=844 Card=96985 Bytes=46164860)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=12 Bytes=1044)
3 1 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=37727165)

Statistics
----------------------------------------------------------
39 recursive calls
0 db block gets
14475 consistent gets
7279 physical reads
0 redo size
37565579 bytes sent via SQL*Net to client
71618 bytes received via SQL*Net from client
6467 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
96985 rows processed

SQL> SELECT /*+ FIRST_ROWS */ * FROM T, T1 WHERE T.USERNAME = T1.OWNER;

已選擇96985行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=97811 Card=96985 Bytes=46164860)
1 0 NESTED LOOPS (Cost=97811 Card=96985 Bytes=46164860)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=37727165)
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=87)
4 3 INDEX (UNIQUE SCAN) OF 'PK_T' (UNIQUE)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
117917 consistent gets
7268 physical reads
0 redo size
37565579 bytes sent via SQL*Net to client
71618 bytes received via SQL*Net from client
6467 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
96985 rows processed

但是如果分頁查詢的內層是這種連接查詢的話，使用NESTED LOOP可以更快的得到前N條記錄。

下面看一下這種情況下的分頁查詢情況：

SQL> SELECT USER_ID, USERNAME, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME
5 FROM
6 (
7 SELECT T.USER_ID, T.USERNAME, T1.NAME
8 FROM T, T1
9 WHERE T.USERNAME = T1.OWNER
10 )
11 WHERE ROWNUM <= 20
12 )
13 WHERE RN >= 11;

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=830 Card=20 Bytes=1200)
1 0 VIEW (Cost=830 Card=20 Bytes=1200)
2 1 COUNT (STOPKEY)
3 2 HASH JOIN (Cost=830 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=12 Bytes=132)
5 3 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=1842715)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
7 physical reads
0 redo size
574 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 /*+ FIRST_ROWS */ USER_ID, USERNAME, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME
5 FROM
6 (
7 SELECT T.USER_ID, T.USERNAME, T1.NAME
8 FROM T, T1
9 WHERE T.USERNAME = T1.OWNER
10 )
11 WHERE ROWNUM <= 20
12 )
13 WHERE RN >= 11;

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=97811 Card=20 Bytes=1200)
1 0 VIEW (Cost=97811 Card=20 Bytes=1200)
2 1 COUNT (STOPKEY)
3 2 NESTED LOOPS (Cost=97811 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=1842715)
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=11)
6 5 INDEX (UNIQUE SCAN) OF 'PK_T' (UNIQUE)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
574 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

看上去似乎HASH JOIN效率更高，難道上面說錯了。

其實這個現(xiàn)象是由于這個例子的特殊性造成的。T表是根據(jù)DBA_USERS創(chuàng)建，這張表很小。HASH JOIN中第一步也就是第一張表的全表掃描是無法應用STOPKEY的，這就是上面提到的NESTED LOOP比HASH JOIN優(yōu)勢的地方。但是，這個例子中，恰好第一張表很小，對這張表的全掃描的代價極低，因此，顯得HASH JOIN效率更高。但是，這不具備共性，如果兩張表的大小相近，或者Oracle錯誤的選擇了先掃描大表，則使用HASH JOIN的效率就會低得多。

SQL> SELECT USER_ID, USERNAME, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME
5 FROM
6 (
7 SELECT /*+ ORDERED */ T.USER_ID, T.USERNAME, T1.NAME
8 FROM T1, T
9 WHERE T.USERNAME = T1.OWNER
10 )
11 WHERE ROWNUM <= 20
12 )
13 WHERE RN >= 11;

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=951 Card=20 Bytes=1200)
1 0 VIEW (Cost=951 Card=20 Bytes=1200)
2 1 COUNT (STOPKEY)
3 2 HASH JOIN (Cost=951 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=1842715)
5 3 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=12 Bytes=132)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8585 consistent gets
7310 physical reads
0 redo size
601 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

通過HINT提示，讓Oracle先掃描大表，這回結果就很明顯了。NESTED LOOP的效果要比HASH JOIN好得多。

下面，繼續(xù)比較一下兩個分頁操作的寫法，為了使結果更具有代表性，這里都采用了FIRST_ROWS提示，讓Oracle采用NESTED LOOP的方式來進行表連接：

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=97811 Card=96985 Bytes=5819100)
1 0 VIEW (Cost=97811 Card=96985 Bytes=5819100)
2 1 COUNT
3 2 NESTED LOOPS (Cost=97811 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=1842715)
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=11)
6 5 INDEX (UNIQUE SCAN) OF 'PK_T' (UNIQUE)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
105571 consistent gets
7299 physical reads
0 redo size
574 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

兩種寫法的效率差別極大。關鍵仍然是是否能將STOPKEY應用到最內層查詢中。

對于表連接來說，在寫分頁查詢的時候，可以考慮增加FIRST_ROWS提示，它有助于更快的將查詢結果返回。

其實，不光是表連接，對于所有的分頁查詢都可以加上FIRST_ROWS提示。不過需要注意的時，分頁查詢的目標是盡快的返回前N條記錄，因此，無論是ROWNUM還是FIRST_ROWS機制都是提高前幾頁的查詢速度，對于分頁查詢的最后幾頁，采用這些機制不但無法提高查詢速度，反而會明顯降低查詢效率，對于這一點使用者應該做到心中有數(shù)。

分享名稱：Oracle分頁查詢語句（三）
轉載源于：http://muchs.cn/article34/ihpdpe.html

成都網站建設公司_創(chuàng)新互聯(lián)，為您提供網站設計、ChatGPT、Google、微信小程序、App設計、手機網站建設

聲明：本網站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內容