Oracle分页查询语句(四)


Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。Oracle分页查询语句(一):http://yangtingkun.itpub.net/post/468/100278Oracle分页查询语句(二):http://yangtingkun.itpub.net/post/468/101703Oracle分页查询语句(三):http://yangtingkun.itpub.net/post/468/104595 最后的例子说明内部循环包含排序的情况:SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;表已创建。SQL> CREATE INDEX IND_T_OBJECT_NAME ON T (OBJECT_NAME);索引已创建。SQL> ALTER TABLE T MODIFY OBJECT_NAME NOT NULL;表已更改。SQL&g开发云主机域名t; EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, ‘T’)PL/SQL 过程已成功完成。下面进行测试包含排序操作的分页查询。可以简单的将查询分为两种不同情况,第一种排序列就是索引列,这种可以利用索引读取,第二种排序列没有索引。第一种情况又可以细分为:完全索引扫描和通过索引扫描定位到表记录两种情况。无论是那种情况,都可以通过索引的全扫描来避免排序的产生。看下面的例子:SQL> SET AUTOT TRACE
SQL> SELECT OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_NAME FROM T ORDER BY OBJECT_NAME
8 )
9 WHERE ROWNUM 10 )
11 WHERE RN >= 11;
已选择10行。
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=20 Bytes=1580)
1 0 VIEW (Cost=26 Card=20 Bytes=1580)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=26 Card=6361 Bytes=419826)
4 3 INDEX (FULL SCAN) OF ‘IND_T_OBJECT_NAME’ (NON-UNIQUE) (Cost=26 Card=6361 Bytes=108137)

Statistics
———————————————————-
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
576 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 ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY OBJECT_NAME
8 )
9 WHERE ROWNUM 10 )
11 WHERE RN >= 11;
已选择10行。
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=43 Card=20 Bytes=1840)
1 0 VIEW (Cost=43 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=43 Card=6361 Bytes=502519)
4 3 SORT (ORDER BY STOPKEY) (Cost=43 Card=6361 Bytes=133581)
5 4 TABLE ACCESS (FULL) OF ‘T’ (Cost=9 Card=6361 Bytes=133581)

Statistics
———————————————————-
0 recursive calls
0 db block gets
81 consistent gets
0 physical reads
0 redo size
673 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
由于不能仅仅通过索引扫描得到查询结果,这里Oracle选择了表扫描。这是由于初始化参数设置决定的。因此,建议在分页的时候使用FIRST_ROWS提示。SQL> SELECT /*+ FIRST_ROWS */ 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 ORDER BY OBJECT_NAME
8 )
9 WHERE ROWNUM 10 )
11 WHERE RN >= 11;
已选择10行。
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (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=6361 Bytes=502519)
4 3 TABLE ACCESS (BY INDEX ROWID) OF ‘T’ (Cost=826 Card=6361 Bytes=133581)
5 4 INDEX (FULL SCAN) OF ‘IND_T_OBJECT_NAME’ (NON-UNIQUE) (Cost=26 Card=6361)

Statistics
———————————————————-
0 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
673 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
使用了FIRST_ROWS提示后,Oracle不需要扫描全表,而且避免了排序操作。下面讨论最后一种情况,排序列不是索引列。这个时候排序不可避免,但是利用给出分页格式,Oracle不会对所有数据进行排序,而是只排序前N条记录。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 ORDER BY TIMESTAMP
8 )
9 )
10 WHERE RN BETWEEN 11 AND 20;
已选择10行。
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=6361 Bytes=585212)
1 0 VIEW (Cost=64 Card=6361 Bytes=585212)
2 1 COUNT
3 2 VIEW (Cost=64 Card=6361 Bytes=502519)
4 3 SORT (ORDER BY) (Cost=64 Card=6361 Bytes=260801)
5 4 TABLE ACCESS (FULL) OF ‘T’ (Cost=9 Card=6361 Bytes=260801)

Statistics
———————————————————-
0 recursive calls
0 db block gets
81 consistent gets
0 physical reads
0 redo size
690 bytes sent via SQL*Net to client
503 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 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 ORDER BY TIMESTAMP
8 )
9 WHERE ROWNUM 10 )
11 WHERE RN >= 11;
已选择10行。
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=20 Bytes=1840)
1 0 VIEW (Cost=64 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=64 Card=6361 Bytes=502519)
4 3 SORT (ORDER BY STOPKEY) (Cost=64 Card=6361 Bytes=260801)
5 4 TABLE ACCESS (FULL) OF ‘T’ (Cost=9 Card=6361 Bytes=260801)

Statistics
———————————————————-
0 recursive calls
0 db block gets
81 consistent gets
0 physical reads
0 redo size
690 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
观察两种不同写法的ORDER BY步骤,一个是带STOPKEY的ORDER BY,另一个不带。在大数据量需要排序的情况下,带STOPKEY的效率要比不带STOPKEY排序的效率高得多。SQL> INSERT INTO T SELECT T.* FROM T, USER_OBJECTS;已创建407104行。SQL> COMMIT;提交完成。SQL> SELECT OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME FROM
5 (
6 SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP
7 )
8 WHERE ROWNUM 9 )
10 WHERE RN >= 11;
已选择10行。已用时间: 00: 00: 03.78
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=20 Bytes=1840)
1 0 VIEW (Cost=64 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=64 Card=6361 Bytes=502519)
4 3 SORT (ORDER BY STOPKEY) (Cost=64 Card=6361 Bytes=260801)
5 4 TABLE ACCESS (FULL) OF ‘T’ (Cost=9 Card=6361 Bytes=260801)

Statistics
———————————————————-
268 recursive calls
0 db block gets
6215 consistent gets
6013 physical reads
0 redo size
740 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> SELECT OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME FROM
5 (
6 SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP
7 )
8 )
9 WHERE RN BETWEEN 11 AND 20;
已选择10行。已用时间: 00: 00: 11.86
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=6361 Bytes=585212)
1 0 VIEW (Cost=64 Card=6361 Bytes=585212)
2 1 COUNT
3 2 VIEW (Cost=64 Card=6361 Bytes=502519)
4 3 SORT (ORDER BY) (Cost=64 Card=6361 Bytes=260801)
5 4 TABLE ACCESS (FULL) OF ‘T’ (Cost=9 Card=6361 Bytes=260801)

Statistics
———————————————————-
26 recursive calls
12 db block gets
6175 consistent gets
9219 physical reads
0 redo size
737 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
10 rows processed
观察两个查询语句的执行时间,以及统计信息中的排序信息。对于第一个查询语句,Oracle利用了ORDER BY STOPKEY方式进行排序,排序操作只排序需要的TOP N的数据,因此排序操作放到了内存中,而对于第二个查询语句来说,进行的数据的全排序,排序数据量大,排序操作不得不在磁盘上完成,因此耗时比较多。通过上面的例子可以看出给出的标准分页查询格式,对于包含排序的操作仍然可以在很大程度上提高分页查询性能。

相关推荐: nginx proxy_pass 和 proxy_redirect

proxy_pass:充当代理服务器,转发请求proxy_redirect:修改301或者302转发过程中的Location。默认值为proxy_redirect default。例:location / {proxy_pass http://192.168.…

免责声明:本站发布的图片视频文字,以转载和分享为主,文章观点不代表本站立场,本站不承担相关法律责任;如果涉及侵权请联系邮箱:360163164@qq.com举报,并提供相关证据,经查实将立刻删除涉嫌侵权内容。

(0)
打赏 微信扫一扫 微信扫一扫
上一篇 03/31 12:25
下一篇 03/31 12:25