@H_403_5@
--创建测试表：@H_403_5@
sql> create table t_test as select * from dba_objects;@H_403_5@
@H_403_5@
Table created.@H_403_5@
@H_403_5@
如，下面的sql （没有过滤条件，只有排序），要将查询结果分页显示，每页显示10条，如：@H_403_5@
select * from t_test order by object_id;@H_403_5@
@H_403_5@
例子：@H_403_5@
1、分页查询sql语句，如下（通常会采用下面的方法，但是这是错误的分页框架）@H_403_5@
语法：select * from (select t.*,rownum rn from (需要分页的sql)t ) where rn >=1 and rn <=10;@H_403_5@
@H_403_5@
sql>select*from(selectt.*,rownumrnfrom(select*fromt_testorderbyobject_id)t)wherern>=1andrn<=10; ExecutionPlan ---------------------------------------------------------- Planhashvalue:3603170480 ---------------------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|TempSpc|Cost(%cpu)|Time| ---------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||80700|16M||3918(1)|00:00:48| |*1|VIEW||80700|16M||3918(1)|00:00:48| |2|COUNT||||||| |3|VIEW||80700|15M||3918(1)|00:00:48| |4|SORTORDERBY||80700|15M|19M|3918(1)|00:00:48| |5|TABLEACCESSFULL|t_test|80700|15M||287(1)|00:00:04| ---------------------------------------------------------------------------------------- PredicateInformation(identifiedbyoperationid): --------------------------------------------------- 1-filter("RN"<=10AND"RN">=1) Note ----- -dynamicsamplingusedforthisstatement(level=2) Statistics ---------------------------------------------------------- 331recursivecalls 0dbblockgets 1211consistentgets 1024physicalreads 0redosize 2075bytessentviasql*Nettoclient 523bytesreceivedviasql*Netfromclient 2sql*Netroundtripsto/fromclient 1sorts(memory) 0sorts(disk) 10rowsprocessed
@H_403_5@
从上面的执行计划可以看出，该sql走了全表扫描，如果该表很大，则先要将该表排序，再取出其中10行数据，这样会出现严重的性能问题，所以该sql不能走全表扫描，必须走索引扫描。（因为索引已经排序了，可使用索引来消除排序）一般分页语句中都有排序。@H_403_5@
如下，在object_id列建立索引，并在索引中添加一个常量0，如下：（在索引中添加一个常量0，是因为object_id列允许位null，如果不添加常量（不一定是0，可以是1,2,3，也可以是英文字母），索引中就不能存储null值，；因为sql中并没有剔除null值，所以我们必须要添加一个常量，让索引存储null，这样才能使sql走索引。）@H_403_5@
sql> create index idx_test on t_test(object_id,0);@H_403_5@
@H_403_5@
sql> select * from (select t.*,rownum rn from (select /*+ index)t_test idx_test) */ * from t_test order by object_id) t) where rn >=1 and rn <=10;@H_403_5@
@H_403_5@
sql> alter session set statistics_level=all;@H_403_5@
sql>select*fromtable(dbms_xplan.display_cursor(null,null,'allstatslast')); PLAN_TABLE_OUTPUT ----------------------------------------- sql_IDgvq2rvmutr85w,childnumber0 ------------------------------------- select*from(selectt.*,rownumrnfrom(select/*+index)t_test idx_test)*/*fromt_testorderbyobject_id)t)wherern>=1andrn <=10 Planhashvalue:3119682446 ----------------------------------------------------------------------------------------------------- |Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers| ----------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||1||10|00:00:00.08|1245| |*1|VIEW||1|80700|10|00:00:00.08|1245| |2|COUNT||1||71903|00:00:00.07|1245| |3|VIEW||1|80700|71903|00:00:00.06|1245| |4|TABLEACCESSBYINDEXROWID|t_test|1|80700|71903|00:00:00.04|1245| |5|INDEXFULLSCAN|IDX_test|1|80700|71903|00:00:00.01|181| ----------------------------------------------------------------------------------------------------- PredicateInformation(identifiedbyoperationid): --------------------------------------------------- 1-filter(("RN"<=10AND"RN">=1)) Note ----- -dynamicsamplingusedforthisstatement(level=2) 28rowsselected.
(因为sql语句没有where过滤条件，强制走索引只能走index full scan，无法走索引范围扫描（index range scan）。E-Rows的显示 说明了扫描了索引中所有叶子块，一共消耗了1245个逻辑读；理想的执行计划是：index full scan只扫描一个最多几个索引叶子块，最根本的原因还在于这个分页框架错了！)@H_403_5@
@H_403_5@
语法：select * from (select * from (select a.*,rownum rn from (需要分页的sql) a ) where rownum <=10) where rn >=1;@H_403_5@
@H_403_5@
sql>select*from(select*from(selecta.*,rownumrnfrom(select/*+index(t_testidx_test)*/*fromt_testorderbyobject_id)a)whererownum<=10)wherern>=1; sql>altersessionsetstatistics_level=all; sql>select*fromtable(dbms_xplan.display_cursor(null,'allstatslast')); PLAN_TABLE_OUTPUT ----------------------------------------- sql_IDcgjp65zfj1yqa,childnumber0 ------------------------------------- select*from(select*from(selecta.*,rownumrnfrom(select/*+ index(t_testidx_test)*/*fromt_testorderbyobject_id)a)where rownum<=10)wherern>=1 Planhashvalue:1201925926 ------------------------------------------------------------------------------------------------------- |Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers| ------------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||1||10|00:00:00.01|5| |*1|VIEW||1|10|10|00:00:00.01|5| |*2|COUNTSTOPKEY||1||10|00:00:00.01|5| |3|VIEW||1|80700|10|00:00:00.01|5| |4|COUNT||1||10|00:00:00.01|5| |5|VIEW||1|80700|10|00:00:00.01|5| |6|TABLEACCESSBYINDEXROWID|t_test|1|80700|10|00:00:00.01|5| |7|INDEXFULLSCAN|IDX_test|1|80700|10|00:00:00.01|3| ------------------------------------------------------------------------------------------------------- PredicateInformation(identifiedbyoperationid): --------------------------------------------------- 1-filter("RN">=1) 2-filter(ROWNUM<=10) Note ----- -dynamicsamplingusedforthisstatement(level=2) 31rowsselected.
@H_403_5@
(从上面的执行计划可以看到，sql走 index full scan，只扫描了10条数据（id=7 A-Rows=10）就停止了（id=2 COUNT STOPKEY）一共消耗了5个逻辑读；该执行计划利用索引已经排序的特性只扫描索引获取了10条数据，然后再利用count stopkey特性，获取到分页需要的数据，sql立即停止运行，这才是最佳的执行计划。)@H_403_5@
@H_403_5@
注意：@H_403_5@
如果分页语句中有排序（order by）,要利用索引已经排序特性，将order by的列包含在索引中，同时也要利用rownum的count stopkey特性来优化分页sql。如果分页中没有排序，可以直接利用rownum的count stopkey特性来优化分页sql。@H_403_5@
@H_403_5@
例子：@H_403_5@
如下两条sq（注意，过滤条件是等值过滤，当然也有order by）l，改成分页语句，并查看执行计划，如下：@H_403_5@
select * from t_test where owner='SCOTT' order by object_id;@H_403_5@
select * from t_test where owner='SYS' order by object_id;@H_403_5@
(其中第一条sql语句的过滤条件是where owner='SCOTT'；该过滤条件能过滤掉表中大部分数据。第二条sql语句的过滤条件where owner='SYS'，能过滤表中一半数据)@H_403_5@
@H_403_5@
sql>select*from(select*from(selecta.*,rownumrnfrom(select/*+index(t_testidx_test)*/*fromt_testwhereowner='SCOTT'orderbyobject_id)a)whererownum<=10)wherern>=1; sql>altersessionsetstatistics_level=all; sql>select*fromtable(dbms_xplan.display_cursor(null,'allstatslast')); PLAN_TABLE_OUTPUT -------------------------------------------- sql_ID0w9tbrwkn9tn6,rownumrnfrom(select/*+ index(t_testidx_test)*/*fromt_testwhereowner='SCOTT'orderby object_id)a)whererownum<=10)wherern>=1 Planhashvalue:1201925926 ------------------------------------------------------------------------------------------------------- |Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers| ------------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||1||10|00:00:00.04|1245| |*1|VIEW||1|10|10|00:00:00.04|1245| |*2|COUNTSTOPKEY||1||10|00:00:00.04|1245| |3|VIEW||1|12|10|00:00:00.04|1245| |4|COUNT||1||10|00:00:00.04|1245| |5|VIEW||1|12|10|00:00:00.04|1245| |*6|TABLEACCESSBYINDEXROWID|t_test|1|12|10|00:00:00.04|1245| |7|INDEXFULLSCAN|IDX_test|1|80700|71901|00:00:00.01|181| ------------------------------------------------------------------------------------------------------- PredicateInformation(identifiedbyoperationid): --------------------------------------------------- 1-filter("RN">=1) 2-filter(ROWNUM<=10) 6-filter("OWNER"='SCOTT') Note ----- -dynamicsamplingusedforthisstatement(level=2) 32rowsselected.
---执行第2条语句，如下：@H_403_5@
sql>select*from(select*from(selecta.*,rownumrnfrom(select/*+index(t_testidx_test)*/*fromt_testwhereowner='SYS'orderbyobject_id)a)whererownum<=10)wherern>=1; sql>altersessionsetstatistics_level=all; sql>select*fromtable(dbms_xplan.display_cursor(null,'allstatslast')); PLAN_TABLE_OUTPUT ------------------------------------------------------- sql_IDdfwkpppbtc8h7,rownumrnfrom(select/*+ index(t_testidx_test)*/*fromt_testwhereowner='SYS'orderby object_id)a)whererownum<=10)wherern>=1 Planhashvalue:1201925926 ------------------------------------------------------------------------------------------------------- |Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers| ------------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||1||10|00:00:00.01|5| |*1|VIEW||1|10|10|00:00:00.01|5| |*2|COUNTSTOPKEY||1||10|00:00:00.01|5| |3|VIEW||1|28483|10|00:00:00.01|5| |4|COUNT||1||10|00:00:00.01|5| |5|VIEW||1|28483|10|00:00:00.01|5| |*6|TABLEACCESSBYINDEXROWID|t_test|1|28483|10|00:00:00.01|5| |7|INDEXFULLSCAN|IDX_test|1|80700|10|00:00:00.01|3| ------------------------------------------------------------------------------------------------------- PredicateInformation(identifiedbyoperationid): --------------------------------------------------- 1-filter("RN">=1) 2-filter(ROWNUM<=10) 6-filter("OWNER"='SYS') Note ----- -dynamicsamplingusedforthisstatement(level=2) 32rowsselected.
@H_403_5@
从上面的执行计划可以看出，两条sql都走了 index full scan，第1条sql从索引中扫描了71901条数据（id=7 A-Rows=71901），在回表的时候对数据进行了大量过滤（id=6），最后得到10条数据，耗费了1245个逻辑读。@H_403_5@
第2条sql从索引中扫描了10条数据，耗费了5个逻辑读。可以看出，第二条sql的执行计划是正确的，而第一条sql的执行计划是错误的，应该尽量在索引扫描的时候就取得10行数据。@H_403_5@
（为什么上面的两条sql只有过滤条件不一样，而第一条sql的执行计划就错了呢？这是因为第一条sql的过滤条件where owner='SCOTT'，在表中只有很少数据，通过扫描object_id列的索引，然后在回表去匹配owner='SCOTT'，因为owner='SCOTT'数据量很少，要搜索大量数据才能匹配上。而第二条sql的过滤条件owner='SYS'，因为数据量多，只需要搜索少量的数据就能匹配上。）@H_403_5@
@H_403_5@
---优化第一条sql：（就必须让过滤条件的列出现在索引中，如下：）@H_403_5@
@H_403_5@
sql>createindexidx_test_allont_test(owner,object_id);---创建一个组合索引，包含owner字段 sql>select*from(select*from(selecta.*,rownumrnfrom(select/*+index(t_testidx_test_all)*/*fromt_testwhereowner='SCOTT'orderbyobject_id)a)whererownum<=10)wherern>=1; sql>select*fromtable(dbms_xplan.display_cursor(null,'allstatslast')); PLAN_TABLE_OUTPUT ---------------------------------------------、 sql_ID9mm61b7j943sf,rownumrnfrom(select/*+ index(t_testidx_test_all)*/*fromt_testwhereowner='SCOTT'order byobject_id)a)whererownum<=10)wherern>=1 Planhashvalue:3696904346 ----------------------------------------------------------------------------------------------------------- |Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers| ----------------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||1||10|00:00:00.01|6| |*1|VIEW||1|10|10|00:00:00.01|6| |*2|COUNTSTOPKEY||1||10|00:00:00.01|6| |3|VIEW||1|12|10|00:00:00.01|6| |4|COUNT||1||10|00:00:00.01|6| |5|VIEW||1|12|10|00:00:00.01|6| |6|TABLEACCESSBYINDEXROWID|t_test|1|12|10|00:00:00.01|6| |*7|INDEXRANGESCAN|IDX_test_ALL|1|12|10|00:00:00.01|3| ----------------------------------------------------------------------------------------------------------- PredicateInformation(identifiedbyoperationid): --------------------------------------------------- 1-filter("RN">=1) 2-filter(ROWNUM<=10) 7-access("OWNER"='SCOTT') Note ----- -dynamicsamplingusedforthisstatement(level=2) 32rowsselected.
（这时候会发现，sql走了索引范围扫描，从索引中扫描了10条数据，一共耗费了6个逻辑读。这说明现在的执行计划是正确的。）
@H_403_5@
@H_403_5@
注意：@H_403_5@
在实际的生成环境中，过滤条件一般都是绑定变量，我们无法控制传参究竟传入哪个值，这就不能确定返回数据究竟是多还是少了，所以，建议最好将排序的列包含在索引中。@H_403_5@
@H_403_5@
但是要注意：如果排序列有多个列，创建索引的时候，我们要将所有的排序列包含在索引中，并且要注意排序列先后顺序，而且还要注意列时升序还是降序。如果分页语句中排序列只有一个列，但是是降序显示的，创建索引的时候就没必要降序创建索引了，我们可以使用hint：index_desc 让索引降序扫描就可以了。@H_403_5@
@H_403_5@
例子：@H_403_5@
(创建索引，只能是object_id在前，object_name在后，另外object_name是降序显示的，那么在创建索引的时候，还要指定object_name列降序排序。下面的sql也没有过滤条件，在创建索引的时候还要加个常量，如下所示：)@H_403_5@
sql>createindexidx_test_2ont_test(object_id,object_namedesc,0);---创建一个组合索引，包含owner字段 sql>select*from(select*from(selecta.*,rownumrnfrom(select/*+index(t_testidx_test_2)*/*fromt_testorderbyobject_id,object_namedesc)a)whererownum<=10)wherern>=1; sql>select*fromtable(dbms_xplan.display_cursor(null,'allstatslast')); PLAN_TABLE_OUTPUT ---------------------------------------- sql_IDg8zgbvt1u1qjq,rownumrnfrom(select/*+ index(t_testidx_test_2)*/*fromt_testorderby object_id,object_namedesc)a)whererownum<=10)wherern>=1 Planhashvalue:2251915778 ------------------------------------------------------------------------------------------------------------------ |Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|Reads| ------------------------------------------------------------------------------------------------------------------ |0|SELECTSTATEMENT||1||10|00:00:00.01|5|1| |*1|VIEW||1|10|10|00:00:00.01|5|1| |*2|COUNTSTOPKEY||1||10|00:00:00.01|5|1| |3|VIEW||1|80700|10|00:00:00.01|5|1| |4|COUNT||1||10|00:00:00.01|5|1| |5|VIEW||1|80700|10|00:00:00.01|5|1| |6|TABLEACCESSBYINDEXROWID|t_test|1|80700|10|00:00:00.01|5|1| |7|INDEXFULLSCAN|IDX_test_2|1|80700|10|00:00:00.01|3|1| ------------------------------------------------------------------------------------------------------------------ PredicateInformation(identifiedbyoperationid): --------------------------------------------------- 1-filter("RN">=1) 2-filter(ROWNUM<=10) Note ----- -dynamicsamplingusedforthisstatement(level=2) 31rowsselected.
@H_403_5@
如果在创建索引的时候没有指定 object_name列降序排序，那么执行计划中会出现sort order by。因为索引中排序和分页语句中排序不一致，如:@H_403_5@
@H_403_5@
sql>createindexidx_test_3ont_test(object_id,object_name,0); sql>select*from(select*from(selecta.*,rownumrnfrom(select/*+index(t_testidx_test_3)*/*fromt_testorderbyobject_id,'allstatslast')); PLAN_TABLE_OUTPUT --------------------------------------------- sql_IDbapu5jn5vn22r,rownumrnfrom(select/*+ index(t_testidx_test_3)*/*fromt_testorderby object_id,object_namedesc)a)whererownum<=10)wherern>=1 Planhashvalue:3547841113 ----------------------------------------- |Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|Reads|OMem|1Mem|Used-Mem| ---------------------------------------------------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||1||10|00:00:00.10|1490|426|||| |*1|VIEW||1|10|10|00:00:00.10|1490|426|||| |*2|COUNTSTOPKEY||1||10|00:00:00.10|1490|426|||| |3|VIEW||1|80700|10|00:00:00.10|1490|426|||| |4|COUNT||1||10|00:00:00.10|1490|426|||| |5|VIEW||1|80700|10|00:00:00.10|1490|426|||| |6|SORTORDERBY||1|80700|10|00:00:00.10|1490|426|10M|1272K|9811K(0)| |7|TABLEACCESSBYINDEXROWID|t_test|1|80700|71903|00:00:00.06|1490|426|||| |8|INDEXFULLSCAN|IDX_test_3|1|80700|71903|00:00:00.03|427|426|||| ---------------------------------------------------------------------------------------------------------------------------------------------- PredicateInformation(identifiedbyoperationid): --------------------------------------------------- 1-filter("RN">=1) 2-filter(ROWNUM<=10) Note ----- -dynamicsamplingusedforthisstatement(level=2) 32rowsselected.
@H_403_5@
(从上面可以看出，执行计划中出现了 sort order by，这就意味着分页语句没有利用到索引已经排序的特性，执行计划是错误的，这时候就需要创建正确的索引。)@H_403_5@
@H_403_5@
例子3：@H_403_5@
一条sql（，过滤条件有等值条件，也有非等值条件，当然也有order by），如下，将下面的sql分页查询：@H_403_5@
select * from t_test where owner='SYS' and object_id > 1000 order by object_name;@H_403_5@
@H_403_5@
--：创建索引（因为owner是等值过滤，object_Id是非等值过滤，创建索引的时候要优先将等值过滤列和排序列组合在一起，然后再将非等值过滤列放到后面）如下：@H_403_5@
createindexidx_test_4ont_test(owner,object_id); select*from(select*from(selecta.*,rownumrnfrom(select/*+index(t_testidx_test_4)*/*fromt_testwhereowner='SYS'andobject_id>1000orderbyobject_name)a)whererownum<=10)wherern>=1; sql>select*fromtable(dbms_xplan.display_cursor(null,'allstatslast')); PLAN_TABLE_OUTPUT ------------------------------------------: sql_ID4z6tjgrdjm5a1,rownumrnfrom(select/*+ index(t_testidx_test_4)*/*fromt_testwhereowner='SYS'and object_id>1000orderbyobject_name)a)whererownum<=10)wherern >=1 Planhashvalue:1432357471 ------------------------------------------------------------------------------------------------------------------ |Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|Reads| ------------------------------------------------------------------------------------------------------------------ |0|SELECTSTATEMENT||1||10|00:00:00.01|14|2| |*1|VIEW||1|10|10|00:00:00.01|14|2| |*2|COUNTSTOPKEY||1||10|00:00:00.01|14|2| |3|VIEW||1|25683|10|00:00:00.01|14|2| |4|COUNT||1||10|00:00:00.01|14|2| |5|VIEW||1|25683|10|00:00:00.01|14|2| |6|TABLEACCESSBYINDEXROWID|t_test|1|25683|10|00:00:00.01|14|2| |*7|INDEXRANGESCAN|IDX_test_4|1|256|10|00:00:00.01|4|2| ------------------------------------------------------------------------------------------------------------------ PredicateInformation(identifiedbyoperationid): --------------------------------------------------- 1-filter("RN">=1) 2-filter(ROWNUM<=10) 7-access("OWNER"='SYS'AND"OBJECT_ID">1000) filter("OBJECT_ID">1000) Note ----- -dynamicsamplingusedforthisstatement(level=2) 34rowsselected.
（可以看出，执行计划中没有出现 sort order by，逻辑读也只有14个，说明执行计划是正确的。）@H_403_5@
@H_403_5@
@H_403_5@
注意：
@H_403_5@
①：如果分页语句中有排序（order by），要利用索引已经排序的特性，将order by的列按照排序的先后顺序包含在索引中，同时要注意排序是升序还是降序。如果分页语句中有过滤条件，我们要注意过滤条件是否有等值过滤条件，如果有等值过滤条件，要将等值过滤条件优先组合在一起，然后将排序列放在等值过滤条件后面，最后将非等值过滤列放排序列后面。如果分页语句中没有等值过滤条件，我们应该先将排序列放在索引前面，将非等值过滤列放后面，最后利用rownum的count stopkey特性来优化分页sql。如果分页中没有排序，可以直接利用rownum的count stopkey特性来优化分页sql。@H_403_5@
@H_403_5@
②：要想一眼看出执行计划是否正确，就要先看分页语句有没有order by，再看执行计划有没有sort order by，如果执行计划中有sort order by，说明执行计划一般是错误的。
@H_403_5@
@H_403_5@
③：如果分页语句中排序的表是分区表，这时我们要看分页语句中是否有跨区分区扫描，如果有跨分区扫描，创建的索引一般都是global索引，如果不创建global索引，就无法保证分页的顺序与所有的顺序是一致的。如果就只扫描一个分区，这时可以创建local索引：
@H_403_5@
@H_403_5@
例子：创建分区表，并导入数据@H_403_5@
CREATE TABLE p_test@H_403_5@
( "OWNER" VARCHAR2(30),@H_403_5@
"OBJECT_NAME" VARCHAR2(128),@H_403_5@
"SUBOBJECT_NAME" VARCHAR2(30),@H_403_5@
"OBJECT_ID" NUMBER,@H_403_5@
"DATA_OBJECT_ID" NUMBER,@H_403_5@
"OBJECT_TYPE" VARCHAR2(19),@H_403_5@
"CREATED" DATE,@H_403_5@
"LAST_DDL_TIME" DATE,@H_403_5@
"TIMESTAMP" VARCHAR2(19),@H_403_5@
"STATUS" VARCHAR2(7),@H_403_5@
"TEMPORARY" VARCHAR2(1),@H_403_5@
"GENERATED" VARCHAR2(1),@H_403_5@
"SECONDARY" VARCHAR2(1),@H_403_5@
"NAMESPACE" NUMBER,@H_403_5@
"EDITION_NAME" VARCHAR2(30)@H_403_5@
) partition by range (object_id)@H_403_5@
(@H_403_5@
partition p1 values less than (10000),@H_403_5@
partition p2 values less than (20000),@H_403_5@
partition p3 values less than (30000),@H_403_5@
partition p4 values less than (40000),@H_403_5@
partition p5 values less than (50000),@H_403_5@
partition p6 values less than (60000),@H_403_5@
partition p7 values less than (70000),@H_403_5@
partition px values less than (maxvalue));@H_403_5@
@H_403_5@
sql> insert into p_test select * from dba_objects;@H_403_5@
@H_403_5@
71917 rows created.@H_403_5@
@H_403_5@
select * from (selct * from (select a.*,rownum rn from (select * from p_test order by object_id) a ) where rownum <=10) where rn >=1;@H_403_5@
@H_403_5@
（这个分页语句没有过滤条件，因此会扫描表中的所有分区。因为排序列恰好是范围分区列，范围分区每个分区的数据也是递增的，这时我们创建索引可以创建为local索引。但是如果将范围分区改为list分区或者hash分区，这时我们就必须创建global索引，因为list分区和hash分区是无序的。）@H_403_5@
@H_403_5@
sql> create index idx_test_id on p_test(object_id,0) local; ---创建local索引@H_403_5@
@H_403_5@
select*from(select*from(selecta.*,rownumrnfrom(select/*+index(p_testidx_test_id)*/*fromp_testorderbyobject_id)a)whererownum<=10)wherern>=1; sql>select*fromtable(dbms_xplan.display_cursor(null,'allstatslast')); sql_IDbxw1059jmgxvx,rownumrnfrom(select/*+ index(p_testidx_test_id)*/*fromp_testorderbyobject_id)a) whererownum<=10)wherern>=1 Planhashvalue:1291390031 -------------------------------------------------------------------------------------------------------------------------- |Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|Reads| -------------------------------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||1||10|00:00:00.01|5|1| |*1|VIEW||1|10|10|00:00:00.01|5|1| |*2|COUNTSTOPKEY||1||10|00:00:00.01|5|1| |3|VIEW||1|63696|10|00:00:00.01|5|1| |4|COUNT||1||10|00:00:00.01|5|1| |5|VIEW||1|63696|10|00:00:00.01|5|1| |6|PARTITIONRANGEALL||1|63696|10|00:00:00.01|5|1| |7|TABLEACCESSBYLOCALINDEXROWID|P_TEST|1|63696|10|00:00:00.01|5|1| |8|INDEXFULLSCAN|IDX_TEST_ID|1|63696|10|00:00:00.01|3|1| -------------------------------------------------------------------------------------------------------------------------- PredicateInformation(identifiedbyoperationid): --------------------------------------------------- 1-filter("RN">=1) 2-filter(ROWNUM<=10) Note ----- -dynamicsamplingusedforthisstatement(level=2) 32rowsselected.
@H_403_5@
例子2：分页语句（根据object_name排序）@H_403_5@
select * from (selct * from (select a.*,rownum rn from (select * from p_test order by object_name) a ) where rownum <=10) where rn >=1;@H_403_5@
@H_403_5@
这时候我们需要创建global索引，因为如果是本地索引就会产生 sort order by@H_403_5@
sql>dropindexidx_test_id; sql>createindexidx_test_idonp_test(object_name,0); select*from(select*from(selecta.*,rownumrnfrom(select/*+index(p_testidx_test_id)*/*fromp_testorderbyobject_name)a)whererownum<=10)wherern>=1; sql>select*fromtable(dbms_xplan.display_cursor(null,'allstatslast')); sql_ID25pm0f6b6m49x,rownumrnfrom(select/*+ index(p_testidx_test_id)*/*fromp_testorderbyobject_name)a) whererownum<=10)wherern>=1 Planhashvalue:246970912 -------------------------------------------------------------------------------------------------------------------------- |Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|Reads| -------------------------------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||1||10|00:00:00.01|10|2| |*1|VIEW||1|10|10|00:00:00.01|10|2| |*2|COUNTSTOPKEY||1||10|00:00:00.01|10|2| |3|VIEW||1|63696|10|00:00:00.01|10|2| |4|COUNT||1||10|00:00:00.01|10|2| |5|VIEW||1|63696|10|00:00:00.01|10|2| |6|TABLEACCESSBYGLOBALINDEXROWID|P_TEST|1|63696|10|00:00:00.01|10|2| |7|INDEXFULLSCAN|IDX_TEST_ID|1|63696|10|00:00:00.01|4|2| -------------------------------------------------------------------------------------------------------------------------- PredicateInformation(identifiedbyoperationid): --------------------------------------------------- 1-filter("RN">=1) 2-filter(ROWNUM<=10) Note ----- -dynamicsamplingusedforthisstatement(level=2) 31rowsselected.
