oracle分页大于6查不出数据,Oracle分页查询语句（6）

当前位置:我的异常网» 数据库 » Oracle分页查询语句(6)

Oracle分页查询语句(6)

www.myexceptions.net  网友分享于：2015-08-01  浏览：0次

Oracle分页查询语句(六)

Oracle分页查询语句(六) 2006-06-03 00:00:00

分类： Linux

Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。

Oracle分页查询语句(一)：http://yangtingkun.itpub.net/post/468/100278

Oracle分页查询语句(二)：http://yangtingkun.itpub.net/post/468/101703

Oracle分页查询语句(三)：http://yangtingkun.itpub.net/post/468/104595

Oracle分页查询语句(四)：http://yangtingkun.itpub.net/post/468/104867

Oracle分页查询语句(五)：http://yangtingkun.itpub.net/post/468/107934

这篇文章通过例子说明分页查询使用的NESTED LOOP操作，在分页查询翻到最后几页时的性能问题：

SQL> CREATE TABLE T AS SELECT * FROM DBA_USERS;

表已创建。

SQL> CREATE TABLE T1 AS SELECT * FROM DBA_SOURCE;

表已创建。

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);

索引已创建。

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

PL/SQL 过程已成功完成。

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

PL/SQL 过程已成功完成。

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

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 )

12 WHERE RN BETWEEN 11 AND 20;

已选择10行。

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=830 Card=96985 Bytes=5819100)

1 0 VIEW (Cost=830 Card=96985 Bytes=5819100)

2 1 COUNT

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

8586 consistent gets

8052 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

在分页查询的前几页，NESTED LOOP操作比HASH JOIN操作效率高得多。

SQL> SET AUTOT OFF

SQL> SELECT COUNT(*) FROM T, T1 WHERE USERNAME = OWNER;

COUNT(*)

----------

96985

SQL> SET AUTOT TRACE

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 )

12 WHERE RN BETWEEN 96971 AND 96980;

已选择10行。

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=830 Card=96985 Bytes=5819100)

1 0 VIEW (Cost=830 Card=96985 Bytes=5819100)

2 1 COUNT

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

8586 consistent gets

8068 physical reads

0 redo size

571 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的方式，执行效率几乎没有任何改变，而采用NESTED LOOP方式，则效率严重下降，而且远远低于HASH JOIN的方式。

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 <= 96980

12 )

13 WHERE RN >= 96971;

已选择10行。

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=97811 Card=96980 Bytes=5818800)

1 0 VIEW (Cost=97811 Card=96980 Bytes=5818800)

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

105566 consistent gets

8068 physical reads

0 redo size

571 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

分页查询一般情况下，很少会翻到最后一篇，如果只是偶尔碰到这种情况，对系统性能不会有很大的影响，但是如果经常碰到这种情况，在设计分页查询时应该给予足够的考虑。

文章评论