Oracle的分页Word文档下载推荐.docx
- 文档编号:4682493
- 上传时间:2023-05-03
- 格式:DOCX
- 页数:17
- 大小:18.09KB
Oracle的分页Word文档下载推荐.docx
《Oracle的分页Word文档下载推荐.docx》由会员分享,可在线阅读,更多相关《Oracle的分页Word文档下载推荐.docx(17页珍藏版)》请在冰点文库上搜索。
这是,查询语句如下:
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;
表已创建。
selectcount(*)fromT;
COUNT(*)
----------
457992
首先比较两种分页方法的区别:
setautoton;
colobject_nameformatA30
execdbms_stats.gather_table_stats(user,'
T'
)
PL/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'
(Cost=864Card=457992Bytes=9617832)
Statistics
0recursivecalls
0dbblockgets
8979consistentgets
7422physicalreads
0redosize
758bytessentviaSQL*Nettoclient
503bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
10rowsprocessed
SELECTOBJECT_ID,OBJECT_NAME
2FROM
3(
4SELECTROWNUMRN,OBJECT_ID,OBJECT_NAME
5FROM
6(
7SELECTOBJECT_ID,OBJECT_NAMEFROMT
8)
9WHEREROWNUM<
=20
10)
11WHERERN>
=11;
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=864Card=20Bytes=1840)
10VIEW(Cost=864Card=20Bytes=1840)
21COUNT(STOPKEY)
5consistentgets
0physicalreads
二者执行效率相差很大,一个需要8000多逻辑读,而另一个只需要5个逻辑读。
观察二者的执行计划可以发现,两个执行计划唯一的区别就是第二个查询在COUNT这步使用了STOPKEY,也就是说,Oracle将ROWNUM<
=20推入到查询内层,当符合查询的条件的记录达到STOPKEY的值,则Oracle结束查询。
因此,可以预见,采用第二种方式,在翻页的开始部分查询速度很快,越到后面,效率越低,当翻到最后一页,效率应该和第一种方式接近。
=457990
=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行。
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=864Card=457990Bytes=42135080)
10VIEW(Cost=864Card=457990Bytes=42135080)
7423physicalreads
680bytessentviaSQL*Nettoclient
11rowsprocessed
(三)
继续看查询的第二种情况,包含表连接的情况:
CREATETABLETASSELECT*FROMDBA_USERS;
CREATETABLET1ASSELECT*FROMDBA_SOURCE;
ALTERTABLETADDCONSTRAINTPK_TPRIMARYKEY(USERNAME);
表已更改。
ALTERTABLET1ADDCONSTRAINTFK_T1_OWNERFOREIGNKEY(OWNER)
2REFERENCEST(USERNAME);
CREATEINDEXIND_T1_OWNERONT1(NAME);
索引已创建。
EXECDBMS_STATS.GATHER_TABLE_STATS(USER,'
T1'
创建了T表和T1表,默认情况下,HASHJOIN的效率要比NESTEDLOOP高很多:
SETAUTOTTRACE
SELECT*FROMT,T1WHERET.USERNAME=T1.OWNER;
已选择96985行。
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=844Card=96985Bytes=46164860)
10HASHJOIN(Cost=844Card=96985Bytes=46164860)
21TABLEACCESS(FULL)OF'
(Cost=2Card=12Bytes=1044)
31TABLEACCESS(FULL)OF'
(Cost=826Card=96985Bytes=37727165)
39recursivecalls
14475consistentgets
7279physicalreads
37565579bytessentviaSQL*Nettoclient
71618bytesreceivedviaSQL*Netfromclient
6467SQL*Netroundtripsto/fromclient
96985rowsprocessed
SELECT/*+FIRST_ROWS*/*FROMT,T1WHERET.USERNAME=T1.OWNER;
0SELECTSTATEMENTOptimizer=HINT:
FIRST_ROWS(Cost=97811Card=96985Bytes=46164860)
10NESTEDLOOPS(Cost=97811Card=96985Bytes=46164860)
31TABLEACCESS(BYINDEXROWID)OF'
(Cost=1Card=1Bytes=87)
43INDEX(UNIQUESCAN)OF'
PK_T'
(UNIQUE)
117917consistentgets
7268physicalreads
但是如果分页查询的内层是这种连接查询的话,使用NESTEDLOOP可以更快的得到前N条记录。
下面看一下这种情况下的分页查询情况:
SELECTUSER_ID,USERNAME,NAME
4SELECTROWNUMRN,USER_ID,USERNAME,NAME
7SELECTT.USER_ID,T.USERNAME,T1.NAME
8FROMT,T1
9WHERET.USERNAME=T1.OWNER
11WHEREROWNUM<
12)
13WHERERN>
已选择10行。
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=830Card=20Bytes=1200)
10VIEW(Cost=830Card=20Bytes=1200)
32HASHJOIN(Cost=830Card=96985Bytes=2909550)
43TABLEACCESS(FULL)OF'
(Cost=2Card=12Bytes=132)
53TABLEACCESS(FULL)OF'
(Cost=826Card=96985Bytes=1842715)
8consistentgets
7physicalreads
574bytessentviaSQL*Nettoclient
SELECT/*+FIRST_ROWS*/USER_ID,USERNAME,NAME
FIRST_ROWS(Cost=97811Card=20Bytes=1200)
10VIEW(Cost=97811Card=20Bytes=1200)
32NESTEDLOOPS(Cost=97811Card=96985Bytes=2909550)
53TABLEACCESS(BYINDEXROWID)OF'
(Cost=1Card=1Bytes=11)
65INDEX(UNIQUESCAN)OF'
28consistentgets
看上去似乎HASHJOIN效率更高,难道上面说错了。
其实这个现象是由于这个例子的特殊性造成的。
T表是根据DBA_USERS创建,这张表很小。
HASHJOIN中第一步也就是第一张表的全表扫描是无法应用STOPKEY的,这就是上面提到的NESTEDLOOP比HASHJOIN优势的地方。
但是,这个例子中,恰好第一张表很小,对这张表的全扫描的代价极低,因此,显得HASHJOIN效率更高。
但是,这不具备共性,如果两张表的大小相近,或者Oracle错误的选择了先扫描大表,则使用HASHJOIN的效率就会低得多。
7SELECT/*+ORDERED*/T.USER_ID,T.USERNAME,T1.NAME
8FROMT1,T
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=951Card=20Bytes=1200)
10VIEW(Cost=951Card=20Bytes=1200)
32HASHJOIN(Cost=951Card=96985Bytes=2909550)
8585consistentgets
7310physicalreads
601bytessentviaSQL*Nettoclient
通过HINT提示,让Oracle先扫描大表,这回结果就很明显了。
NESTEDLOOP的效果要比HASHJOIN好得多。
下面,继续比较一下两个分页操作的写法,为了使结果更具有代表性,这里都采用了FIRST_ROWS提示,让Oracle采用NESTEDLOOP的方式来进行表连接:
7SELECTT.USER_ID
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 分页