Oracle的分页查询语句.docx
- 文档编号:12411822
- 上传时间:2023-04-18
- 格式:DOCX
- 页数:20
- 大小:18.60KB
Oracle的分页查询语句.docx
《Oracle的分页查询语句.docx》由会员分享,可在线阅读,更多相关《Oracle的分页查询语句.docx(20页珍藏版)》请在冰豆网上搜索。
Oracle的分页查询语句
Oracle的分页查询语句
(一)分页查询格式:
SELECT*FROM
(SELECTA.*,ROWNUMRN
FROM(SELECT*FROMTABLE_NAME)A
WHEREROWNUM<=40)
WHERERN>=21
其中最内层的查询SELECT*FROMTABLE_NAME表示不进行翻页的原始查询语句。
ROWNUM<=40和RN>=21控制分页查询的每页的范围。
上面给出的这个分页查询语句,在大多数情况拥有较高的效率。
分页的目的就是控制输出结果集大小,将结果尽快的返回。
在上面的分页查询语句中,这种考虑主要体现在WHEREROWNUM<=40这句上。
选择第21到40条记录存在两种方法,一种是上面例子中展示的在查询的第二层通过ROWNUM<=40来控制最大值,在查询的最外层控制最小值。
而另一种方式是去掉查询第二层的WHEREROWNUM<=40语句,在查询的最外层控制分页的最小值和最大值。
这是,查询语句如下:
SELECT*FROM
(SELECTA.*,ROWNUMRN
FROM(SELECT*FROMTABLE_NAME)A)WHERERNBETWEEN21AND40
对比这两种写法,绝大多数的情况下,第一个查询的效率比第二个高得多。
这是由于CBO优化模式下,Oracle可以将外层的查询条件推到内层查询中,以提高内层查询的执行效率。
对于第一个查询语句,第二层的查询条件WHEREROWNUM<=40就可以被Oracle推入到内层查询中,这样Oracle查询的结果一旦超过了ROWNUM限制条件,就终止查询将结果返回了。
而第二个查询语句,由于查询条件BETWEEN21AND40是存在于查询的第三层,而Oracle无法将第三层的查询条件推到最内层(即使推到最内层也没有意义,因为最内层查询不知道RN代表什么)。
因此,对于第二个查询语句,Oracle最内层返回给中间层的是所有满足条件的数据,而中间层返回给最外层的也是所有数据。
数据的过滤在最外层完成,显然这个效率要比第一个查询低得多。
上面分析的查询不仅仅是针对单表的简单查询,对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。
这里就不对包含排序的查询进行说明了,下一篇文章会通过例子来详细说明。
下面简单讨论一下多表联合的情况。
对于最常见的等值表连接查询,CBO一般可能会采用两种连接方式NESTEDLOOP和HASHJOIN(MERGEJOIN效率比HASHJOIN效率低,一般CBO不会考虑)。
在这里,由于使用了分页,因此指定了一个返回的最大记录数,NESTEDLOOP在返回记录数超过最大值时可以马上停止并将结果返回给中间层,而HASHJOIN必须处理完所有结果集(MERGEJOIN也是)。
那么在大部分的情况下,对于分页查询选择NESTEDLOOP作为查询的连接方法具有较高的效率(分页查询的时候绝大部分的情况是查询前几页的数据,越靠后面的页数访问几率越小)。
因此,如果不介意在系统中使用HINT的话,可以将分页的查询语句改写为:
SELECT/*+FIRST_ROWS*/*FROM
(SELECTA.*,ROWNUMRN
FROM(SELECT*FROMTABLE_NAME)A
WHEREROWNUM<=40)
WHERERN>=21
(二)用几个例子来说明分页查询的效率。
首先构造一个比较大的表作为测试表:
SQL>CREATETABLETASSELECT*FROMDBA_OBJECTS,DBA_SEQUENCES;
表已创建。
SQL>SELECTCOUNT(*)FROMT;
COUNT(*)
----------
457992
首先比较两种分页方法的区别:
SQL>SETAUTOTON
SQL>COLOBJECT_NAMEFORMATA30
SQL>EXECDBMS_STATS.GATHER_TABLE_STATS(USER,'T')
PL/SQL过程已成功完成。
SQL>SELECTOBJECT_ID,OBJECT_NAME
2FROM
3(
4SELECTROWNUMRN,OBJECT_ID,OBJECT_NAME
5FROM
6(
7SELECTOBJECT_ID,OBJECT_NAMEFROMT
8)
9)
10WHERERNBETWEEN11AND20;
OBJECT_IDOBJECT_NAME
----------------------------------------
5807ALL_APPLY_PROGRESS
1769ALL_ARGUMENTS
2085ALL_ASSOCIATIONS
4997ALL_AUDIT_POLICIES
4005ALL_BASE_TABLE_MVIEWS
5753ALL_CAPTURE
5757ALL_CAPTURE_PARAMETERS
5761ALL_CAPTURE_PREPARED_DATABASE
5765ALL_CAPTURE_PREPARED_SCHEMAS
5769ALL_CAPTURE_PREPARED_TABLES
已选择10行。
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=864Card=457992Bytes=42135264)
10VIEW(Cost=864Card=457992Bytes=42135264)
21COUNT
32TABLEACCESS(FULL)OF'T'(Cost=864Card=457992Bytes=9617832)
Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
8979consistentgets
7422physicalreads
0redosize
758bytessentviaSQL*Nettoclient
503bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
10rowsprocessed
SQL>SELECTOBJECT_ID,OBJECT_NAME
2FROM
3(
4SELECTROWNUMRN,OBJECT_ID,OBJECT_NAME
5FROM
6(
7SELECTOBJECT_ID,OBJECT_NAMEFROMT
8)
9WHEREROWNUM<=20
10)
11WHERERN>=11;
OBJECT_IDOBJECT_NAME
----------------------------------------
5807ALL_APPLY_PROGRESS
1769ALL_ARGUMENTS
2085ALL_ASSOCIATIONS
4997ALL_AUDIT_POLICIES
4005ALL_BASE_TABLE_MVIEWS
5753ALL_CAPTURE
5757ALL_CAPTURE_PARAMETERS
5761ALL_CAPTURE_PREPARED_DATABASE
5765ALL_CAPTURE_PREPARED_SCHEMAS
5769ALL_CAPTURE_PREPARED_TABLES
已选择10行。
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=864Card=20Bytes=1840)
10VIEW(Cost=864Card=20Bytes=1840)
21COUNT(STOPKEY)
32TABLEACCESS(FULL)OF'T'(Cost=864Card=457992Bytes=9617832)
Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
5consistentgets
0physicalreads
0redosize
758bytessentviaSQL*Nettoclient
503bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
10rowsprocessed
二者执行效率相差很大,一个需要8000多逻辑读,而另一个只需要5个逻辑读。
观察二者的执行计划可以发现,两个执行计划唯一的区别就是第二个查询在COUNT这步使用了STOPKEY,也就是说,Oracle将ROWNUM<=20推入到查询内层,当符合查询的条件的记录达到STOPKEY的值,则Oracle结束查询。
因此,可以预见,采用第二种方式,在翻页的开始部分查询速度很快,越到后面,效率越低,当翻到最后一页,效率应该和第一种方式接近。
SQL>SELECTOBJECT_ID,OBJECT_NAME
2FROM
3(
4SELECTROWNUMRN,OBJECT_ID,OBJECT_NAME
5FROM
6(
7SELECTOBJECT_ID,OBJECT_NAMEFROMT
8)
9WHEREROWNUM<=457990
10)
11WHERERN>=457980;
OBJECT_IDOBJECT_NAME
----------------------------------------
7128XCF_I_HANDLE_STATUS
7126XCF_P
7127XCF_U1
7142XDF
7145XDF_I_DF_KEY
7146XDF_I_HANDLE_STATUS
7143XDF_P
7144XDF_U1
TEST.YANGTINGKUN
TEST4.YANGTINGKUN
YANGTK.YANGTINGKUN
已选择11行。
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=864Card=457990Bytes=42135080)
10VIEW(Cost=864Card=457990Bytes=42135080)
21COUNT(STOPKEY)
32TABLEACCESS(FULL)OF'T'(Cost=864Card=457992Bytes=9617832)
Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
8979consistentgets
7423physicalreads
0redosize
680bytessentviaSQL*Nettoclient
503bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
11rowsprocessed
(三)
继续看查询的第二种情况,包含表连接的情况:
SQL>CREATETABLETASSELECT*FROMDBA_USERS;
表已创建。
SQL>CREATETABLET1ASSELECT*FROMDBA_SOURCE;
表已创建。
SQL>ALTERTABLETADDCONSTRAINTPK_TPRIMARYKEY(USERNAME);
表已更改。
SQL>ALTERTABLET1ADDCONSTRAINTFK_T1_OWNERFOREIGNKEY(OWNER)
2REFERENCEST(USERNAME);
表已更改。
SQL>CREATEINDEXIND_T1_OWNERONT1(NAME);
索引已创建。
SQL>EXECDBMS_STATS.GATHER_TABLE_STATS(USER,'T')
PL/SQL过程已成功完成。
SQL>EXECDBMS_STATS.GATHER_TABLE_STATS(USER,'T1')
PL/SQL过程已成功完成。
创建了T表和T1表,默认情况下,HASHJOIN的效率要比NESTEDLOOP高很多:
SQL>SETAUTOTTRACE
SQL>SELECT*FROMT,T1WHERET.USERNAME=T1.OWNER;
已选择96985行。
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=844Card=96985Bytes=46164860)
10HASHJOIN(Cost=844Card=96985Bytes=46164860)
21TABLEACCESS(FULL)OF'T'(Cost=2Card=12Bytes=1044)
31TABLEACCESS(FULL)OF'T1'(Cost=826Card=96985Bytes=37727165)
Statistics
----------------------------------------------------------
39recursivecalls
0dbblockgets
14475consistentgets
7279physicalreads
0redosize
37565579bytessentviaSQL*Nettoclient
71618bytesreceivedviaSQL*Netfromclient
6467SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
96985rowsprocessed
SQL>SELECT/*+FIRST_ROWS*/*FROMT,T1WHERET.USERNAME=T1.OWNER;
已选择96985行。
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=HINT:
FIRST_ROWS(Cost=97811Card=96985Bytes=46164860)
10NESTEDLOOPS(Cost=97811Card=96985Bytes=46164860)
21TABLEACCESS(FULL)OF'T1'(Cost=826Card=96985Bytes=37727165)
31TABLEACCESS(BYINDEXROWID)OF'T'(Cost=1Card=1Bytes=87)
43INDEX(UNIQUESCAN)OF'PK_T'(UNIQUE)
Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
117917consistentgets
7268physicalreads
0redosize
37565579bytessentviaSQL*Nettoclient
71618bytesreceivedviaSQL*Netfromclient
6467SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
96985rowsprocessed
但是如果分页查询的内层是这种连接查询的话,使用NESTEDLOOP可以更快的得到前N条记录。
下面看一下这种情况下的分页查询情况:
SQL>SELECTUSER_ID,USERNAME,NAME
2FROM
3(
4SELECTROWNUMRN,USER_ID,USERNAME,NAME
5FROM
6(
7SELECTT.USER_ID,T.USERNAME,T1.NAME
8FROMT,T1
9WHERET.USERNAME=T1.OWNER
10)
11WHEREROWNUM<=20
12)
13WHERERN>=11;
已选择10行。
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=830Card=20Bytes=1200)
10VIEW(Cost=830Card=20Bytes=1200)
21COUNT(STOPKEY)
32HASHJOIN(Cost=830Card=96985Bytes=2909550)
43TABLEACCESS(FULL)OF'T'(Cost=2Card=12Bytes=132)
53TABLEACCESS(FULL)OF'T1'(Cost=826Card=96985Bytes=1842715)
Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
8consistentgets
7physicalreads
0redosize
574bytessentviaSQL*Nettoclient
503bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
10rowsprocessed
SQL>SELECT/*+FIRST_ROWS*/USER_ID,USERNAME,NAME
2FROM
3(
4SELECTROWNUMRN,USER_ID,USERNAME,NAME
5FROM
6(
7SELECTT.USER_ID,T.USERNAME,T1.NAME
8FROMT,T1
9WHERET.USERNAME=T1.OWNER
10)
11WHEREROWNUM<=20
12)
13WHERERN>=11;
已选择10行。
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=HINT:
FIRST_ROWS(Cost=97811Card=20Bytes=1200)
10VIEW(Cost=97811Card=20Bytes=1200)
21COUNT(STOPKEY)
32NESTEDLOOPS(Cost=97811Card=96985Bytes=2909550)
43TABLEACCESS(FULL)OF'T1'(Cost=826Card=96985Bytes=1842715)
53TABLEACCESS(BYINDEXROWID)OF'T'(Cost=1Card=1Bytes=11)
65INDEX(UNIQUESCAN)OF'PK_T'(UNIQUE)
Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
28consistentgets
0physicalreads
0redosize
574bytessentviaSQL*Nettoclient
503bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
10rowsprocessed
看上去似乎HASHJOIN效率更高,难道上面说错了。
其实这个现象是由于这个例子的特殊性造成的。
T表是根据DBA_USERS创建,这张表很小。
HASHJOIN中第一步也就是第一张表的全表扫描是无法应用STOPKEY的,这就是上面提到的NESTEDLOOP比HASHJOIN优势的地方。
但是,这个例子中,恰好第一张表很小,对这张表的全扫描的代价极低,因此,显得HASHJOIN效率更高。
但是,这不具备共性,如果两张表的大小相近,或者Oracle错误的选择了先扫描大表,则使用HASH
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 分页 查询 语句