Oracle的分页.docx
- 文档编号:11966838
- 上传时间:2023-04-16
- 格式:DOCX
- 页数:17
- 大小:18.09KB
Oracle的分页.docx
《Oracle的分页.docx》由会员分享,可在线阅读,更多相关《Oracle的分页.docx(17页珍藏版)》请在冰豆网上搜索。
Oracle的分页
Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。
(一)
分页查询格式:
select*from(selectA.*,rownumrwnfrom(select*fromemp)Awhererownum<=10)whererwn>=6;
其中最内层的查询select*fromemp表示不进行翻页的原始查询语句。
rownum<=40和rwn>=21控制分页查询的每页的范围。
上面给出的这个分页查询语句,在大多数情况拥有较高的效率。
分页的目的就是控制输出结果集大小,将结果尽快的返回。
在上面的分页查询语句中,这种考虑主要体现在whererownum<=10这句上。
选择第6到10条记录存在两种方法,一种是上面例子中展示的在查询的第二层通过rownum<=40来控制最大值,在查询的最外层控制最小值。
而另一种方式是去掉查询第二层的whererownum<=10语句,在查询的最外层控制分页的最小值和最大值。
这是,查询语句如下:
select*from(selectA.*,rownumrwnfrom(select*fromemp)A)whererwnbetween6and10;
对比这两种写法,绝大多数的情况下,第一个查询的效率比第二个高得多。
这是由于CBO优化模式下,Oracle可以将外层的查询条件推到内层查询中,以提高内层查询的执行效率。
对于第一个查询语句,第二层的查询条件whererownum<=10就可以被Oracle推入到内层查询中,这样Oracle查询的结果一旦超过了rownum限制条件,就终止查询将结果返回了。
而第二个查询语句,由于查询条件between6and10是存在于查询的第三层,而Oracle无法将第三层的查询条件推到最内层(即使推到最内层也没有意义,因为最内层查询不知道RN代表什么)。
因此,对于第二个查询语句,Oracle最内层返回给中间层的是所有满足条件的数据,而中间层返回给最外层的也是所有数据。
数据的过滤在最外层完成,显然这个效率要比第一个查询低得多。
上面分析的查询不仅仅是针对单表的简单查询,对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。
(二)
首先构造一个比较大的表作为测试表:
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_namefrom(selectrownumrwn,object_id,object_namefrom(selectobject_id,object_namefromT))whererwnbetween6and10;
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;
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错误的选择了先扫描大表,则使用HASHJOIN的效率就会低得多。
SQL>SELECTUSER_ID,USERNAME,NAME
2FROM
3(
4SELECTROWNUMRN,USER_ID,USERNAME,NAME
5FROM
6(
7SELECT/*+ORDERED*/T.USER_ID,T.USERNAME,T1.NAME
8FROMT1,T
9WHERET.USERNAME=T1.OWNER
10)
11WHEREROWNUM<=20
12)
13WHERERN>=11;
已选择10行。
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=951Card=20Bytes=1200)
10VIEW(Cost=951Card=20Bytes=1200)
21COUNT(STOPKEY)
32HASHJOIN(Cost=951Card=96985Bytes=2909550)
43TABLEACCESS(FULL)OF'T1'(Cost=826Card=96985Bytes=1842715)
53TABLEACCESS(FULL)OF'T'(Cost=2Card=12Bytes=132)
Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
8585consistentgets
7310physicalreads
0redosize
601bytessentviaSQL*Nettoclient
503bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
10rowsprocessed
通过HINT提示,让Oracle先扫描大表,这回结果就很明显了。
NESTEDLOOP的效果要比HASHJOIN好得多。
下面,继续比较一下两个分页操作的写法,为了使结果更具有代表性,这里都采用了FIRST_ROWS提示,让Oracle采用NESTEDLOOP的方式来进行表连接:
SQL>SELECT/*+FIRST_ROWS*/USER_ID,USERNAME,NAME
2FROM
3(
4SELECTROWNUMRN,USER_ID,USERNAME,NAME
5FROM
6(
7SELECTT.USER_ID
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 分页