Oracle分查询优化

1、Oracle 的分页查询语句基本上可以按照本文给出的格式来进行套用Oracle 分页查询语句（一）分页查询格式：SELECT*FROM（SELECTA.*,ROWNUMRNFROM（SELECT*FROMTABLE_NAME）AWHEREROWNUM=21其中最内层的查询 SELECT*FROMTABLE_NAME 表示不进行翻页的原始查询语句。ROWNUM=21 控制分页查询的每页的范围。上面给出的这个分页查询语句，在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小，将结果尽快的返回。在上面的分页查询语句中，这种考虑主要体现在 WHEREROWNUM=40 这句上。选择第 21 到

2、 40 条记录存在两种方法，一种是上面例子中展示的在查询的第二层通过 ROWNUM=40 来控制最大值，在查询的最外层控制最小值。而另一种方式是去掉查询第二层的 WHEREROWNUM=40语句，在查询的最外层控制分页的最小值和最大值。这是，查询语句如下：SELECT*FROM（SELECTA.*,ROWNUMRNFROM（SELECT*FROMTABLE_NAME）A）WHERERNBETWEEN21AND40对比这两种写法，绝大多数的情况下，第一个查询的效率比第二个高得多。这是由于 CBO 优化模式下，Oracle 可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。对于第一个查

3、询语句，第二层的查询条件 WHEREROWNUM=40 就可以被 Oracle 推入到内层查询中，这样 Oracle 查询的结果一旦超过了 ROWNUM 限制条件，就终止查询将结果返回了。而第二个查询语句，由于查询条件 BETWEEN21AND40 是存在于查询的第三层，而 Oracle 无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道 RN 代表什么）。因此，对于第二个查询语句，Oracle 最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。上面分析的查询不仅仅是针对单表的简

4、单查询， 对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。这里就不对包含排序的查询进行说明了，下一篇文章会通过例子来详细说明。下面简单讨论一下多表联合的情况。对于最常见的等彳！表连接查询，CBO 一般可能会采用两种连接方式 NESTEDLOOP 和 HASHJOIN（MERGEJOIN 效率比 HASHJOIN 效率低，一般 CBO 不会考虑）。在这里，由于使用了分页，因此指定了一个返回的最大记录数，NESTEDLOOP 在返回记录数超过最大值时可以马上停止并将结果返回给中间层，而 HASHJOIN 必须处理完所有结果集（MERGEJOIN 也是）。那么在大部分的情况下，

5、对于分页查询选择 NESTEDLOOP 作为查询的连接方法具有较高的效率（分页查询的时候绝大部分的情况是查询前几页的数据，越靠后面的页数访问几率越小）。因此，如果不介意在系统中使用 HINT 的话，可以将分页的查询语句改写为：SELECT/*+FIRST_ROWS*/*FROM(SELECTA.*,ROWNUMRNFROM(SELECT*FROMTABLE_NAME)AWHEREROWNUM=21Oracle 分页查询语句(二)这篇文章用几个例子来说明分页查询的效率。首先构造一个比较大的表作为测试表：SQLCREATETABLETASSELECT*FROMDBA_OBJECTS,DBA_SEQ

6、UENCES;表已创建。SQLSELECTCOUNT(*)FROMT;COUNT(*)457992首先比较两种分页方法的区别：SQLSETAUTOTONSQLCOLOBJECT_NAMEFORMATA30SQLEXECDBMS_STATS.GATHER_TABLE_STATS(USER,T)PL/SQL 过程已成功完成。SQLSELECTOBJECT_ID,OBJECT_NAME2 FROM3 (4 SELECTROWNUMRN,OBJECT_ID,OBJECT_NAME5 FROM6 (7 SELECTOBJECT_ID,OBJECT_NAMEFROMT8 )9 )10 WHERERNBET

7、WEEN11AND20;OBJECT_IDOBJECT_NAME5807ALL_APPLY_PROGRESS1769ALL_ARGUMENTS2085ALL_ASSOCIATIONS4997ALL_AUDIT_POLICIES4005ALL_BASE_TABLE_MVIEWS5753ALL_CAPTURE5757ALL_CAPTURE_PARAMETERS5761ALL_CAPTURE_PREPARED_DATABASE5765ALL_CAPTURE_PREPARED_SCHEMASExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=864Ca

8、rd=457992Bytes=42135264)10VIEW(Cost=864Card=457992Bytes=42135264)21COUNT32TABLEACCESS(FULL)OFT(Cost=864Card=457992Bytes=9617832)Statistics0recursivecalls0dbblockgets8979consistentgets7422physicalreads0redosize758bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/from

9、client0sorts(memory)0sorts(disk)10rowsprocessedSQLSELECTOBJECT_ID,OBJECT_NAME2 FROM3 (4 SELECTROWNUMRN,OBJECT_ID,OBJECT_NAME5 FROM6 (7 SELECTOBJECT_ID,OBJECT_NAMEFROMT8 )9 WHEREROWNUM=11;OBJECT_IDOBJECT_NAME5807ALL_APPLY_PROGRESS1769ALL_ARGUMENTS2085ALL_ASSOCIATIONS4997ALL_AUDIT_POLICIES4005ALL_BASE

10、_TABLE_MVIEWS5753ALL_CAPTURE5757ALL_CAPTURE_PARAMETERS5761ALL_CAPTURE_PREPARED_DATABASE5765ALL_CAPTURE_PREPARED_SCHEMASExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=864Card=20Bytes=1840)0 0VIEW(Cost=864Card=20Bytes=1840)0 1COUNT(STOPKEY)32TABLEACCESS(FULL)OFT(Cost=864Card=457992Bytes=9617832)St

11、atistics0 recursivecalls0 dbblockgets0 consistentgets0 physicalreads0 redosize758bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient0 SQL*Netroundtripsto/fromclient0 sorts(memory)0 sorts(disk)0 0rowsprocessed二者执行效率相差很大，一个需要 8000 多逻辑读，而另一个只需要 5 个逻辑读。观察二者的执行计划可以发现，两个执行计划唯一的区别就是第二个查询在 COUNT

12、 这步使用了 STOPKEY,也就是说，Oracle将 ROWNUMSELECTOBJECT_ID,OBJECT_NAME2 FROM3 (4 SELECTROWNUMRN,OBJECT_ID,OBJECT_NAME5 FROM6 (7 SELECTOBJECT_ID,OBJECT_NAMEFROMT8 )9 WHEREROWNUM=457980;OBJECT_IDOBJECT_NAME7128XCF_I_HANDLE_STATUS7126XCF_P7127XCF_U17142XDF7145XDF_I_DF_KEY7146XDF_I_HANDLE_STATUS7143XDF_P7144XDF_

13、U1TEST.YANGTINGKUNTEST4.YANGTINGKUNYANGTK.YANGTINGKUN 已选择 11 行。ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=864Card=457990Bytes=42135080)10VIEW(Cost=864Card=457990Bytes=42135080)21COUNT(STOPKEY)32TABLEACCESS(FULL)OFT(Cost=864Card=457992Bytes=9617832)Statistics0recursivecalls0dbblockgets8979con

14、sistentgets7423physicalreads0redosize680bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)11rowsprocessedOracle 分页查询语句(三)继续看查询的第二种情况，包含表连接的情况：SQLCREATETABLETASSELECT*FROMDBA_USERS;表已创建。SQLCREATETABLET1ASSELECT*FROMDBA_SOURCE;表已创建。

15、SQLALTERTABLETADDCONSTRAINTPK_TPRIMARYKEY(USERNAME);表已更改。SQLALTERTABLET1ADDCONSTRAINTFK_T1_OWNERFOREIGNKEY(OWNER)2REFERENCEST(USERNAME);表已更改。SQLCREATEINDEXIND_T1_OWNERONT1(NAME);索引已创建。SQLEXECDBMS_STATS.GATHER_TABLE_STATS(USER,T)PL/SQL 过程已成功完成。SQLEXECDBMS_STATS.GATHER_TABLE_STATS(USER,T1)PL/SQL 过程已成功

16、完成。创建了 T 表和 T1 表，默认情况下，HASHJOIN 的效率要比 NESTEDLOOP 高很多:SQLSETAUTOTTRACESQLSELECT*FROMT,T1WHERET.USERNAME=T1.OWNER;已选择 96985 行。ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=844Card=96985Bytes=46164860)10HASHJOIN(Cost=844Card=96985Bytes=46164860)21TABLEACCESS(FULL)OFT(Cost=2Card=12Bytes=1044)31TABL

17、EACCESS(FULL)OFT1(Cost=826Card=96985Bytes=37727165)Statistics39recursivecalls0dbblockgets14475consistentgets7279physicalreads0redosize37565579bytessentviaSQL*Nettoclient71618bytesreceivedviaSQL*Netfromclient6467SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)96985rowsprocessedSQLSELECT/*+FIR

18、ST_ROWS*/*FROMT,T1WHERET.USERNAME=T1.OWNER;已选择 96985 行。ExecutionPlan0SELECTSTATEMENTOptimizer=HINT:FIRST_ROWS(Cost=97811Card=96985Bytes=46164860)1 0NESTEDLOOPS(Cost=97811Card=96985Bytes=46164860)21TABLEACCESS(FULL)OFT1(Cost=826Card=96985Bytes=37727165)31TABLEACCESS(BYINDEXROWID)OFT(Cost=1Card=1Bytes

19、=87)43INDEX(UNIQUESCAN)OFPK_T(UNIQUE)Statistics0recursivecalls0dbblockgets117917consistentgets7268physicalreads0redosize37565579bytessentviaSQL*Nettoclient71618bytesreceivedviaSQL*Netfromclient6467SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)96985rowsprocessed但是如果分页查询的内层是这种连接查询的话，使用 NESTE

20、DLOOP 可以更快的得到前 N 条记录下面看一下这种情况下的分页查询情况：SQLSELECTUSER_ID,USERNAME,NAME2FROM3(4 SELECTROWNUMRN,USER_ID,USERNAME,NAME5 FROM6 (7 SELECTT.USER_ID,T.USERNAME,T1.NAME8 FROMT,T19 WHERET.USERNAME=T1.OWNER10 )11 WHEREROWNUM=11;已选择 10 行。ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=830Card=20Bytes=1200)1 0

21、VIEW(Cost=830Card=20Bytes=1200)2 1COUNT(STOPKEY)3 2HASHJOIN(Cost=830Card=96985Bytes=2909550)43TABLEACCESS(FULL)OFT(Cost=2Card=12Bytes=132)53TABLEACCESS(FULL)OFT1(Cost=826Card=96985Bytes=1842715)Statistics0recursivecalls0dbblockgets8consistentgets7physicalreads0redosize574bytessentviaSQL*Nettoclient5

22、03bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)10rowsprocessed2FROM3 (4 SELECTROWNUMRN,USER_ID,USERNAME,NAME5 FROM6 (7 SELECTT.USER_ID,T.USERNAME,T1.NAME8 FROMT,T19 WHERET.USERNAME=T1.OWNER10 )11 WHEREROWNUM=11;已选择 10 行。ExecutionPlan0SELECTSTATEMENTOptimi

23、zer=HINT:FIRST_ROWS(Cost=97811Card=20Bytes=1200)1 0VIEW(Cost=97811Card=20Bytes=1200)2 1COUNT(STOPKEY)3 2NESTEDLOOPS(Cost=97811Card=96985Bytes=2909550)43TABLEACCESS(FULL)OFT1(Cost=826Card=96985Bytes=1842715)53TABLEACCESS(BYINDEXROWID)OFT(Cost=1Card=1Bytes=11)65INDEX(UNIQUESCAN)OFPK_T(UNIQUE)Statistic

24、s0recursivecalls0dbblockgets28consistentgets0 physicalreads0 redosize574bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)10rowsprocessed看上去似乎 HASHJOIN 效率更高，难道上面说错了。其实这个现象是由于这个例子的特殊性造成的。T 表是根据 DBA_USERS 创建，这张表很小。HASHJOIN中第一步也就是第一张

25、表的全表扫描是无法应用 STOPKEY 的，这就是上面提到的 NESTEDLOOP 比 HASHJOIN 优势的地方。但是，这个例子中，恰好第一张表很小，对这张表的全扫描的代价极低，因此，显得 HASHJOIN效率更高。但是，这不具备共性，如果两张表的大小相近，或者 Oracle 错误的选择了先扫描大表，则使用 HASHJOIN 的效率就会低得多。SQLSELECTUSER_ID,USERNAME,NAME2 FROM3 (4 SELECTROWNUMRN,USER_ID,USERNAME,NAME5 FROM6 (7 SELECT/*+ORDERED*/T.USER_ID,T.USERNAM

26、E,T1.NAME8 FROMT1,T9 WHERET.USERNAME=T1.OWNER10 )11 WHEREROWNUM=11;已选择 10 行。ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=951Card=20Bytes=1200)1 0VIEW(Cost=951Card=20Bytes=1200)2 1COUNT(STOPKEY)3 2HASHJOIN(Cost=951Card=96985Bytes=2909550)43TABLEACCESS(FULL)OFT1(Cost=826Card=96985Bytes=1842715)5

27、3TABLEACCESS(FULL)OFT(Cost=2Card=12Bytes=132)Statistics0recursivecalls0dbblockgets8585consistentgets7310physicalreads0redosize601bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)10rowsprocessed通过 HINT 提示，让 Oracle 先扫描大表，这回结果就很明显了。

28、NESTEDLOOP 的效果要比 HASHJOIN好得多。下面，继续比较一下两个分页操作的写法，为了使结果更具有代表性，这里都采用了 FIRST_ROWS 提示，让 Oracle 采用 NESTEDLOOP 的方式来进行表连接：SQLSELECT/*+FIRST_ROWS*/USER_ID,USERNAME,NAME2 FROM3 (4 SELECTROWNUMRN,USER_ID,USERNAME,NAME6 (7 SELECTT.USER_ID,T.USERNAME,T1.NAME8 FROMT,T19 WHERET.USERNAME=T1.OWNER10 )11 WHEREROWNUM=

29、11;已选择 10 行。ExecutionPlan0SELECTSTATEMENTOptimizer=HINT:FIRST_ROWS(Cost=97811Card=20Bytes=1200)1 0VIEW(Cost=97811Card=20Bytes=1200)2 1COUNT(STOPKEY)3 2NESTEDLOOPS(Cost=97811Card=96985Bytes=2909550)43TABLEACCESS(FULL)OFT1(Cost=826Card=96985Bytes=1842715)53TABLEACCESS(BYINDEXROWID)OFT(Cost=1Card=1Byte

30、s=11)65INDEX(UNIQUESCAN)OFPK_T(UNIQUE)Statistics0recursivecalls0dbblockgets28consistentgets0physicalreads0redosize574bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)10rowsprocessedSQLSELECT/*+FIRST_ROWS*/USER_ID,USERNAME,NAME2 F

31、ROM3 (4 SELECTROWNUMRN,USER_ID,USERNAME,NAME5 FROM6 (7 SELECTT.USER_ID,T.USERNAME,T1.NAME8 FROMT,T19 WHERET.USERNAME=T1.OWNER10 )12WHERERNBETWEEN11AND20;已选择 10 行。ExecutionPlan0SELECTSTATEMENTOptimizer=HINT:FIRST_ROWS(Cost=97811Card=96985Bytes=5819100)1 0VIEW(Cost=97811Card=96985Bytes=5819100)2 1COUN

32、T3 2NESTEDLOOPS(Cost=97811Card=96985Bytes=2909550)43TABLEACCESS(FULL)OFT1(Cost=826Card=96985Bytes=1842715)53TABLEACCESS(BYINDEXROWID)OFT(Cost=1Card=1Bytes=11)65INDEX(UNIQUESCAN)OFPK_T(UNIQUE)Statistics0recursivecalls0dbblockgets105571consistentgets7299physicalreads0redosize574bytessentviaSQL*Nettocl

33、ient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)10rowsprocessed两种写法的效率差别极大。关键仍然是是否能将 STOPKEY 应用到最内层查询中。对于表连接来说，在写分页查询的时候，可以考虑增加 FIRST_ROWS 提示，它有助于更快的将查询结果返回。其实，不光是表连接，对于所有的分页查询都可以加上 FIRST_ROWS 提示。不过需要注意的时，分页查询的目标是尽快的返回前 N 条记录，因此，无论是 ROWNUM 还是 FIRST_RO

34、WS 机制都是提高前几页的查询速度，对于分页查询的最后几页，采用这些机制不但无法提高查询速度，反而会明显降低查询效率，对于这一点使用者应该做到心中有数。Oracle 分页查询语句(四)最后的例子说明内部循环包含排序的情况：SQLCREATETABLETASSELECT*FROMDBA_OBJECTS;表已创建。SQLCREATEINDEXIND_T_OBJECT_NAMEONT(OBJECT_NAME);索引已创建。SQLALTERTABLETMODIFYOBJECT_NAMENOTNULL;表已更改。SQLEXECDBMS_STATS.GATHER_TABLE_STATS(USER,T)PL

35、/SQL 过程已成功完成。下面进行测试包含排序操作的分页查询。可以简单的将查询分为两种不同情况，第一种排序列就是索引列,这种可以利用索引读取，第二种排序列没有索引。第一种情况又可以细分为：完全索引扫描和通过索引扫描定位到表记录两种情况。无论是那种情况，都可以通过索引的全扫描来避免排序的产生。看下面的例子：SQLSETAUTOTTRACESQLSELECTOBJECT_NAME2 FROM3 (4 SELECTROWNUMRN,OBJECT_NAME5 FROM6 (7 SELECTOBJECT_NAMEFROMTORDERBYOBJECT_NAME8 )9 WHEREROWNUM=11;已选择

36、 10 行。ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=26Card=20Bytes=1580)10VIEW(Cost=26Card=20Bytes=1580)21COUNT(STOPKEY)32VIEW(Cost=26Card=6361Bytes=419826)43INDEX(FULLSCAN)OFIND_T_OBJECT_NAME(NON-UNIQUE)(Cost=26Card=6361Bytes=108137)Statistics0recursivecalls0dbblockgets3consistentgets0physical

37、reads0redosize576bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)10rowsprocessed这种情况下，通过索引可以完全得到查询的结果，因此可以避免表扫描的产生，而且，由于索引已经是排序过的，因此通过索引的全扫描，连排序操作都省略了。SQLSELECTOBJECT_ID,OBJECT_NAME3 (4 SELECTROWNUMRN,OBJECT_ID,OBJECT_NAME5 FROM

38、6 (7 SELECTOBJECT_ID,OBJECT_NAMEFROMTORDERBYOBJECT_NAME8 )9 WHEREROWNUM=11;已选择 10 行。ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=43Card=20Bytes=1840)10VIEW(Cost=43Card=20Bytes=1840)21COUNT(STOPKEY)32VIEW(Cost=43Card=6361Bytes=502519)43SORT(ORDERBYSTOPKEY)(Cost=43Card=6361Bytes=133581)54TABLEAC

39、CESS(FULL)OFT(Cost=9Card=6361Bytes=133581)Statistics0recursivecalls0dbblockgets81consistentgets0physicalreads0redosize673bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient1sorts(memory)0sorts(disk)10rowsprocessed由于不能仅仅通过索引扫描得到查询结果，这里 Oracle 选择了表扫描。这是由于初始化参

40、数设置决定的因此，建议在分页的时候使用 FIRST_ROWS 提示。SQLSELECT/*+FIRST_ROWS*/OBJECT_ID,OBJECT_NAME2 FROM3 (4 SELECTROWNUMRN,OBJECT_ID,OBJECT_NAME5 FROM6 (7 SELECTOBJECT_ID,OBJECT_NAMEFROMTORDERBYOBJECT_NAME8 )9WHEREROWNUM=11;已选择 10 行。ExecutionPlan0SELECTSTATEMENTOptimizer=HINT:FIRST_ROWS(Cost=826Card=20Bytes=1840)1 0V

41、IEW(Cost=826Card=20Bytes=1840)2 1COUNT(STOPKEY)3 2VIEW(Cost=826Card=6361Bytes=502519)43TABLEACCESS(BYINDEXROWID)OFT(Cost=826Card=6361Bytes=133581)54INDEX(FULLSCAN)OFIND_T_OBJECT_NAME(NON-UNIQUE)(Cost=26Card=6361)Statistics0recursivecalls0dbblockgets22consistentgets0physicalreads0redosize673bytessent

42、viaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)10rowsprocessed使用了 FIRST_ROWS 提示后，Oracle 不需要扫描全表，而且避免了排序操作。下面讨论最后一种情况，排序列不是索引列。这个时候排序不可避免，但是利用给出分页格式,会对所有数据进行排序，而是只排序前 N 条记录。SQLSELECTOBJECT_ID,OBJECT_NAME2 FROM3 (4 SELECTROWNUMRN,OBJECT_I

43、D,OBJECT_NAME5 FROM6 (7 SELECTOBJECT_ID,OBJECT_NAMEFROMTORDERBYTIMESTAMP8 )9 )10 WHERERNBETWEEN11AND20;已选择 10 行。ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=64Card=6361Bytes=585212)10VIEW(Cost=64Card=6361Bytes=585212)21COUNT32VIEW(Cost=64Card=6361Bytes=502519)Oracle 不43SORT(ORDERBY)(Cost=64Car

44、d=6361Bytes=260801)54TABLEACCESS(FULL)OFT(Cost=9Card=6361Bytes=260801)Statistics0recursivecalls0dbblockgets81consistentgets0physicalreads0redosize690bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient1sorts(memory)0sorts(disk)10rowsprocessedSQLSELECTOBJECT_

45、ID,OBJECT_NAME2 FROM3 (4 SELECTROWNUMRN,OBJECT_ID,OBJECT_NAME5 FROM6 (7 SELECTOBJECT_ID,OBJECT_NAMEFROMTORDERBYTIMESTAMP8 )9 WHEREROWNUM=11;已选择 10 行。ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=64Card=20Bytes=1840)10VIEW(Cost=64Card=20Bytes=1840)21COUNT(STOPKEY)32VIEW(Cost=64Card=6361Bytes=502

46、519)43SORT(ORDERBYSTOPKEY)(Cost=64Card=6361Bytes=260801)54TABLEACCESS(FULL)OFT(Cost=9Card=6361Bytes=260801)Statistics0recursivecalls0dbblockgets81consistentgets0physicalreads0redosize690bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient1sorts(memory)0sorts

47、(disk)10rowsprocessed观察两种不同写法的 ORDERBY 步骤， 一个是带 STOPKEY 的 ORDERBY,另一个不带。 在大数据量需要排序的情况下，带 STOPKEY 的效率要比不带 STOPKEY 排序的效率高得多。SQLINSERTINTOTSELECTT.*FROMT,USER_OBJECTS;已创建 407104 行。SQLCOMMIT;提交完成。SQLSELECTOBJECT_ID,OBJECT_NAME2 FROM3 (4 SELECTROWNUMRN,OBJECT_ID,OBJECT_NAMEFROM5 (6 SELECTOBJECT_ID,OBJECT

48、_NAMEFROMTORDERBYTIMESTAMP7 )8 WHEREROWNUM=11;已选择 10 行。已用时间：00:00:03.78ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=64Card=20Bytes=1840)10VIEW(Cost=64Card=20Bytes=1840)21COUNT(STOPKEY)32VIEW(Cost=64Card=6361Bytes=502519)43SORT(ORDERBYSTOPKEY)(Cost=64Card=6361Bytes=260801)54TABLEACCESS(FULL)OFT

49、(Cost=9Card=6361Bytes=260801)Statistics268recursivecalls0dbblockgets6215consistentgets6013physicalreads0redosize740bytessentviaSQL*Nettoclient385bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient6sorts(memory)0sorts(disk)10rowsprocessedSQLSELECTOBJECT_ID,OBJECT_NAME2 FROM3 (4 SELECTROW

50、NUMRN,OBJECT_ID,OBJECT_NAMEFROM5 (6 SELECTOBJECT_ID,OBJECT_NAMEFROMTORDERBYTIMESTAMP7 )8 )9 WHERERNBETWEEN11AND20;已选择 10 行。已用时间：00:00:11.86ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=64Card=6361Bytes=585212)10VIEW(Cost=64Card=6361Bytes=585212)21COUNT32VIEW(Cost=64Card=6361Bytes=502519)43SORT(

51、ORDERBY)(Cost=64Card=6361Bytes=260801)54TABLEACCESS(FULL)OFT(Cost=9Card=6361Bytes=260801)Statistics26recursivecalls12dbblockgets6175consistentgets9219physicalreads0redosize737bytessentviaSQL*Nettoclient385bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)1sorts(disk)10row

52、sprocessed观察两个查询语句的执行时间， 以及统计信息中的排序信息。 对于第一个查询语句， Oracle 利用了 ORDERBYSTOPKEY方式进行排序，排序操作只排序需要的 TOPN 的数据，因此排序操作放到了内存中，而对于第二个查询语句来说，进行的数据的全排序，排序数据量大，排序操作不得不在磁盘上完成，因此耗时比较多。通过上面的例子可以看出给出的标准分页查询格式，对于包含排序的操作仍然可以在很大程度上提高分页查询性能。Oracle 分页查询语句(五)前面的各种例子已经说明了分页查询语句的标准写法所带来的性能提升。这里简单总结一下，并简单的说明分页查询语句在何时无法带来性能提升。分

53、页查询语句之所以可以很快的返回结果，是因为它的目标是最快的返回第一条结果。如果每页有 20 条记录，目前翻到第 5 页，那么只需要返回前 100 条记录都可以满足查询的要求了，也许还有几万条记录也符合查询的条件，但是由于分页的限制，在当前的查询中可以忽略这些数据，而只需尽快的返回前 100 条数据。这也是为什么在标准分页查询语句中经常会使用 FIRST_ROWS 提示的原因。对于行操作，可以在得到结果的同时将结果直接返回给上一层调用。但是对于结果集操作，Oracle 必须得到结果集中所有的数据，因此分页查询中所带的 ROWNUM 信息不起左右。如果最内层的子查询中包含了下面这些操作中的一个以上

54、， 则分页查询语句无法体现出任何的性能优势： UNIONUNIONALL、 MINUS、 INTERSECT、GROUPBY、DISTINCT、UNIQUE 以及聚集函数如 MAX、MIN 和分析函数等。除了这些操作以外，分页查询还有一个很明显的特点，就是处理的页数越小，效率就越高，越到后面，查询速度越慢。分页查询用来提高返回速度的方法都是针对数据量较小的前 N 条记录而言。无论是索引扫描，NESTEDLOOP 连接，还是 ORDERBYSTOPKEY,这些方法带来性能提升的前提都是数据量比较小，一旦分页到了最后几页，会发现这些方法不但没有办法带来性能的提升，而且性能比普通查询还要低得多。这一

55、点，在使用分页查询的时候，一定要心里有数。最后看几个例子：首先看看 UNIONALL、GROUPBY 以及分析函数使外层的 ROWNUM 限制对内层查询无效。SQLSETAUTOTTRACESQLSELECT/*+FIRST_ROWS*/OBJECT_ID,OBJECT_NAME2FROM3 (4 SELECTROWNUMRN,OBJECT_ID,OBJECT_NAME5FROM6 (7 SELECTOBJECT_ID,OBJECT_NAMEFROMTORDERBYOBJECT_NAME8)9 WHEREROWNUM=11;已选择 10 行。ExecutionPlan0SELECTSTATEM

56、ENTOptimizer=HINT:FIRST_ROWS(Cost=826Card=20Bytes=1840)10VIEW(Cost=826Card=20Bytes=1840)21COUNT(STOPKEY)32VIEW(Cost=826Card=6361Bytes=502519)43TABLEACCESS(BYINDEXROWID)OFT(Cost=826Card=6361Bytes=133581)54INDEX(FULLSCAN)OFIND_T_OBJECT_NAME(NON-UNIQUE)(Cost=26Card=6361)Statistics0recursivecalls0dbbloc

57、kgets23consistentgets0physicalreads0redosize597bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)10rowsprocessed这是分页查询 ROWNUM 起作用的情况，下面看看如果内层查询包括了集操作时的情况:SQLSELECT/*+FIRST_ROWS*/OBJECT_ID,OBJECT_NAME2 FROM3 (4 SELECTROWNUMRN,OBJEC

58、T_ID,OBJECT_NAME5 FROM6 (7 SELECTOBJECT_ID,OBJECT_NAMEFROMT8 UNIONALL9 SELECTOBJECT_ID,OBJECT_NAMEFROMT10 ORDERBYOBJECT_NAME11 )12 WHEREROWNUM=11;已选择 10 行。ExecutionPlan0SELECTSTATEMENTOptimizer=HINT:FIRST_ROWS(Cost=85Card=20Bytes=1840)10VIEW(Cost=85Card=20Bytes=1840)21COUNT(STOPKEY)32VIEW(Cost=85Car

59、d=12722Bytes=1005038)43SORT(ORDERBYSTOPKEY)(Cost=18Card=12722Bytes=267162)54UNION-ALL65TABLEACCESS(FULL)OFT(Cost=9Card=6361Bytes=133581)75TABLEACCESS(FULL)OFT(Cost=9Card=6361Bytes=133581)Statistics0recursivecalls0dbblockgets322consistentgets0physicalreads0redosize546bytessentviaSQL*Nettoclient503byt

60、esreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient1sorts(memory)0sorts(disk)10rowsprocessedSQLSELECT/*+FIRST_ROWS*/OBJECT_ID,OBJECT_NAME2 FROM3 (4 SELECTROWNUMRN,OBJECT_ID,OBJECT_NAME5 FROM6 (7 SELECT/*+INDEX(T)*/OBJECT_ID,OBJECT_NAMEFROMT8 UNIONALL9 SELECT/*+INDEX(T)*/OBJECT_ID,OBJECT_NA