Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。
Oracle分页查询语句(一):http://wu423.iteye.com/blog/510222
Oracle分页查询语句(二):http://wu423.iteye.com/blog/510223
继续看查询的第二种情况,包含表连接的情况:
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 过程已成功完成。
创建了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效率更高,难道上面说错了。
其实这个现象是由于这个例子的特殊性造成的。T表是根据DBA_USERS创建,这张表很小。HASH JOIN中第一步也就是第一张表的全表扫描是无法应用STOPKEY的,这就是上面提到的NESTED LOOP比HASH JOIN优势的地方。但是,这个例子中,恰好第一张表很小,对这张表的全扫描的代价极低,因此,显得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好得多。
下面,继续比较一下两个分页操作的写法,为了使结果更具有代表性,这里都采用了FIRST_ROWS提示,让Oracle采用NESTED LOOP的方式来进行表连接:
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 /*+ 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 )
12 WHERE RN BETWEEN 11 AND 20;
已选择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机制都是提高前几页的查询速度,对于分页查询的最后几页,采用这些机制不但无法提高查询速度,反而会明显降低查询效率,对于这一点使用者应该做到心中有数。
发表评论
-
Oracle存储过程学习
2009-12-14 10:53 1979存储过程创建语法: ... -
Oracle分页查询语句(六)
2009-11-04 22:58 732Oracle的分页查询语句基本上可以按照本文给出的格式来进行套 ... -
Oracle分页查询语句(五)
2009-11-04 22:58 937Oracle的分页查询语句基 ... -
Oracle分页查询语句(四)
2009-11-04 22:57 898Oracle的分页查询语句基本上可以按照本文给出的格式来进行套 ... -
Oracle分页查询语句(二)
2009-11-04 22:55 962Oracle的分页查询语句基本上可以按照本文给出的格式来进行套 ... -
Oracle分页查询语句(一)
2009-11-04 22:51 1030Oracle的分页查询语句基本上可以按照本文给出的格式来进行套 ...
相关推荐
msql和oracle分页查询语句笔记
Oracle的分页查询语句以及Oracle分页的存储过程
Oracle分页查询语句的学习和示例演练
oracle分页查询语句sql
Oracle的分页查询语句
一些常用到的在oracle中的sql语句,也是我在网上找到的,分享给大家
网上搜集的oracle分页查询语句和性能分析
select * from (select a.*,rownum rn from (select * from tablename) a where rownum) where rn>2
用ORACLE的SQL语句实现多栏分页输出.RTF
oracle认证详解Oracle的几种分页查询语句! 值得下载看看!资源免费,大家分享!!
Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用
NULL 博文链接:https://yan578351314.iteye.com/blog/911307
Oracle中实现分页查询的SQL命令 //curPage是当前页面,pageCount是每页显示行数 //rownum是伪列,相当于表中每一列的标识列(可以理解为行号),需要显式的提取出来并取一个别名
几条常见的数据库分页SQL 语句,针对oracle,sqlserver,mysql三种常见数据库的分页显示。
高效多条件 Oracle 分页存储过程 ,快速分页
环境: Oracle 9i 本例子使用简单的SQL语句实现Oracle数据库的分页动作。它没有使用PL/SQL语句(比如什么程序包、游标等),只是使用通用的、简单的SQL实现了...分页公式注释非常详细,是非常实用的Oracle分页语句!!
对于数据库中表的数据的 Web 显示,如果没有展示顺序的需要,而且因为满足条件的记录如此之多,就不得不对数据进行分页处理。常常用户并不是对所有数据都感兴趣的,或者大部分情况下,他们...看似相似的分页语句,在响
写好oracle的连接字符串和查询语句,调用程序中的方法可以很方便的实现分页功能。该方法中,将参数连接字符串,查询的sql语句,指定每页显示多少行,调用成功后,会返回页数,行数,还有查询的结果数据集。 使用...
mysql和oracle的分页语句
3大数据库(Sql-Server,MySql和Oracle)的分页SQL语句实现