分页语句最能考察一个人究竟会不会 SQL 优化，因为分页语句优化几乎囊括了 SQL 优化必须具备的知识。
我们先创建一个测试表 T_PAGE。
create table t_page as select * from dba_objects;
Table created.
现有如下 SQL（没有过滤条件，只有排序），要将查询结果分页显示，每页显示 10 条。
select * from t_page order by object_id;
大家可能会采用以下这种分页框架（错误的分页框架）。
select *from (select t.*, rownum rn from (需要分页的 SQL) t)where rn >= 1and rn <= 10;
采用这种分页框架会产生严重的性能问题。现在将 SQL 语句代入错误的分页框架中。
select *from (select t.*, rownum rnfrom (select * from t_page order by object_id) t)where rn >= 1and rn <= 10;
10 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 3603170480-----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|-----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 61800 | 12M| | 3020 (1)||* 1 | VIEW | | 61800 | 12M| | 3020 (1)|| 2 | COUNT | | | | | || 3 | VIEW | | 61800 | 12M| | 3020 (1)|| 4 | SORT ORDER BY | | 61800 | 12M| 14M| 3020 (1)|| 5 | TABLE ACCESS FULL| T_PAGE | 61800 | 12M| | 236 (1)|-----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("RN"<=10 AND "RN">=1)
从执行计划中我们可以看到该 SQL 走了全表扫描，假如 T_PAGE 有上亿条数据，先要将该表（上亿条的表）进行排序（SORT ORDER BY），再取出其中 10 行数据，这时该 SQL 会产生严重的性能问题。所以该 SQL 不能走全表扫描，必须走索引扫描。
该 SQL 没有过滤条件，只有排序，我们可以利用索引已经排序这个特性来优化分页语句，也就是说要将分页语句中的 SORT ORDER BY 消除。一般分页语句中都有排序。
现在我们对排序列 object_id 建立索引，在索引中添加一个常量 0，注意 0 不能放前面。
create index idx_page on t_page(object_id,0);
Index created.
为什么要在索引中添加一个常量 0 呢？这是因为 object_id 列允许为 null，如果不添加常量（不一定是 0，可以是 1、2、3，也可以是英文字母），索引中就不能存储 null 值，然而 SQL 并没有写成以下写法。这个时候就不会走单个object_id索引！
select * from t_page where object_id is not null order by object_id;
因为 SQL 中并没有剔除 null 值，所以我们必须要添加一个常量，让索引存储 null 值，这样才能使 SQL 走索引。
create index idx_page on t_page(object_id);create index IDX_PAGE_ID on t_page(object_id,0);explain plan FOR select * from (select t.*, rownum rn from (select/*+ index(t_page idx_page) */ *from t_page order by object_id) t) where rn >= 1and rn <= 10;SELECT * FROM table(DBMS_XPLAN.display);explain plan FOR select /*+ index(t_page idx_page) */ * from t_page;SELECT * FROM table(DBMS_XPLAN.display);
现在我们来看一下强制走索引的 A-Rows 执行计划（因为涉及到排版和美观，执行计划中删掉了 A-Time 等数据）。
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------SQL_ID fw6ym4n8njxqf, child number 0-------------------------------------
select * from (select t.*, rownum rn from (select/*+ index(t_page idx_page) */ *from t_page order by object_id) t) where rn >= 1and rn <= 10
Plan hash value: 3119682446-------------------------------------------------------------------------------------| Id |Operation | Name | Starts | E-Rows | A-Rows | Buffers |-------------------------------------------------------------------------------------| 0 |SELECT STATEMENT | | 1 | | 10 | 1287 ||* 1 | VIEW | | 1 | 61800 | 10 | 1287 || 2 | COUNT | | 1 | | 72608 | 1287 || 3 | VIEW | | 1 | 61800 | 72608 | 1287 || 4 | TABLE ACCESS BY INDEX ROWID| T_PAGE | 1 | 61800 | 72608 | 1287 || 5 | INDEX FULL SCAN | IDX_PAGE | 1 | 61800 | 72608 | 183 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter(("RN"<=10 AND "RN">=1))
因为 SQL 语句中没有 where 过滤条件，强制走索引只能走 INDEX FULL SCAN，无法走索引范围扫描（INDEX RANGE SCAN）。我们注意看执行计划中 A-Rows 这列，INDEX FULL SCAN 扫描了索引中所有叶子块，因为 INDEX FULL SCAN 返回了 72 608 行数据（表的总行数），一共耗费了 1 287 个逻辑读（Buffers=1287）。理想的执行计划是：INDEX FULL SCAN 只扫描 1 个（最多几个）索引叶子块，扫描 10 行数据（A-Rows=10）就停止了。为什么没有走最理想的执行计划呢？这是因为分页框架错了！
下面才是正确的分页框架。(就是将and分页条件拆开)
select *from (select *from (select a.*, rownum rnfrom (需要分页的 SQL) a)where rownum <= 10)where rn >= 1;
现在将 SQL 代入正确的分页框架中，强制走索引，查看 A-Rows 的执行计划（因为涉及到排版和美观，执行计划中删掉了 A-Time 等数据）。
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
select * from (select * from (select a.*, rownum rn from (select /*+ index(t_page idx_page) */* from t_page order by object_id) a)where rownum <= 10)where rn >= 1
Plan hash value: 1201925926-------------------------------------------------------------------------------------| Id |Operation | Name | Starts | E-Rows | A-Rows |Buffers|-------------------------------------------------------------------------------------| 0 |SELECT STATEMENT | | 1 | | 10 | 5||* 1 | VIEW | | 1 | 10 | 10 | 5||* 2 | COUNT STOPKEY | | 1 | | 10 | 5|| 3 | VIEW | | 1 | 61800 | 10 | 5|| 4 | COUNT | | 1 | | 10 | 5|| 5 | VIEW | | 1 | 61800 | 10 | 5|| 6 | TABLE ACCESS BY INDEX ROWID| T_PAGE | 1 | 61800 | 10 | 5|| 7 | INDEX FULL SCAN | IDX_PAGE | 1 | 61800 | 10 | 3|-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("RN">=1)2 - filter(ROWNUM<=10)
从执行计划中我们可以看到，SQL 走了 INDEX FULL SCAN，只扫描了 10 条数据（Id=7 A-Rows=10）就停止了（Id=2 COUNT STOPKEY），一共只耗费了 5 个逻辑读（Buffers=5）。该执行计划利用索引已经排序特性（执行计划中没有 SORT ORDER BY），扫描索引获取了 10 条数据；然后再利用了 COUNT STOPKEY 特性，获取到分页语句需要的数据，SQL 立即停止运行，这才是最佳执行计划。
为什么错误的分页框架会导致性能很差呢？因为错误的分页框架这种写法没有 COUNT STOPKEY（where rownum<=…）功能，COUNT STOPKEY 就是当扫描到指定行数的数据之后，SQL 就停止运行。
现在我们得到分页语句的优化思路：如果分页语句中有排序（order by），要利用索引已经排序特性，将 order by 的列包含在索引中，同时也要利用 rownum 的 COUNT STOPKEY 特性来优化分页 SQL。如果分页中没有排序，可以直接利用 rownum 的 COUNT STOPKEY 特性来优化分页 SQL。
现有如下 SQL（注意，过滤条件是等值过滤，当然也有 order by），现在要将查询结果分页显示，每页显示 10 条。
select * from t_page where owner = 'SCOTT' order by object_id;select * from t_page where owner = 'SYS' order by object_id;
第一条 SQL 语句的过滤条件是where owner='SCOTT'，该过滤条件能过滤掉表中绝大部分数据。第二条 SQL 语句的过滤条件是where owner='SYS'，该过滤条件能过滤表中一半数据。
我们将上述 SQL 代入正确的分页框架中强制走索引（object_id 列的索引，因为到目前为止 t_page 只有该列建立了索引），查看 A-Rows 的执行计划（因为涉及到排版和美观，执行计划中删掉了 A-Time 等数据）。
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------SQL_ID 7s4mhq8sz19da, child number 0-------------------------------------
select * from (select * from (select a.*, rownum rn from (select /*+ index(t_page idx_page) */ * from t_page where owner = 'SCOTT' order by object_id) a)where rownum <= 10)where rn >= 1
Plan hash value: 1201925926-------------------------------------------------------------------------------------| Id | Operation |Name | Starts | E-Rows | A-Rows |Buffers|-------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 10 | 1273||* 1 | VIEW | | 1 | 10 | 10 | 1273||* 2 | COUNT STOPKEY | | 1 | | 10 | 1273|| 3 | VIEW | | 1 | 57 | 10 | 1273|| 4 | COUNT | | 1 | | 10 | 1273|| 5 | VIEW | | 1 | 57 | 10 | 1273||* 6 | TABLE ACCESS BY INDEX ROWID|T_PAGE | 1 | 57 | 10 | 1273|| 7 | INDEX FULL SCAN |IDX_PAGE| 1 | 61800 | 72427 | 183|-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("RN">=1)2 - filter(ROWNUM<=10)6 - filter("OWNER"='SCOTT')
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------SQL_ID bn5k602hpdcq1, child number 0-------------------------------------
select * from (select * from (select a.*, rownum rnfrom (select /*+ index(t_page idx_page) */* from t_pagewhere owner = 'SYS' order by object_id) a)where rownum <= 10) where rn >= 1
Plan hash value: 1201925926-------------------------------------------------------------------------------------| Id |Operation | Name | Starts | E-Rows | A-Rows |Buffers|-------------------------------------------------------------------------------------| 0 |SELECT STATEMENT | | 1 | | 10 | 5||* 1 | VIEW | | 1 | 10 | 10 | 5||* 2 | COUNT STOPKEY | | 1 | | 10 | 5|| 3 | VIEW | | 1 | 28199 | 10 | 5|| 4 | COUNT | | 1 | | 10 | 5|| 5 | VIEW | | 1 | 28199 | 10 | 5||* 6 | TABLE ACCESS BY INDEX ROWID| T_PAGE | 1 | 28199 | 10 | 5|| 7 | INDEX FULL SCAN | IDX_PAGE | 1 | 61800 | 10 | 3|-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("RN">=1)2 - filter(ROWNUM<=10)6 - filter("OWNER"='SYS')
从执行计划中我们可以看到，两条 SQL 都走了index full scan，第一条 SQL 从索引中扫描了 72 427 条数据（Id=7 A-Rows=72427），在回表的时候对数据进行了大量过滤（Id=6），最后得到 10 条数据，耗费了 1 273 个逻辑读（Buffers=1273）。第二条 SQL 从索引中扫描了 10 条数据，耗费了 5 个逻辑读（Buffers=5）。显而易见，第二条 SQL 的执行计划是正确的，而第一条 SQL 的执行计划是错误的，应该尽量在索引扫描的时候就取得 10 行数据。
为什么仅仅是过滤条件不一样，两条 SQL 在效率上有这么大区别呢？这是因为第一条 SQL 过滤条件是owner='SCOTT'，owner='SCOTT'在表中只有很少数据，通过扫描 object_id 列的索引，然后回表再去匹配owner='SCOTT'，因为owner='SCOTT'数据量少，要搜索大量数据才能匹配上。而第二条 SQL 的过滤条件是**owner='SYS'**，因为**owner='SYS'**数据量多，只需要搜索少量数据就能匹配上。
想要优化第一条 SQL，就需要让其在索引扫描的时候读取少量数据块就取得 10 行数据，这就需要将过滤列（owner）包含在索引中，排序列是 object_id，那么现在我们创建组合索引。
create index idx_page_ownerid on t_page(owner,object_id);
Index created.
我们查看强制走索引（idx_page_ownerid）带有 A-Rows 的执行计划（省略了部分数据）。
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------SQL_ID a1g16uafr05qf, child number 0-------------------------------------
select * from (select * from (select a.*, rownum rnfrom (select /*+ index(t_page idx_page_ownerid) */* from t_pagewhere owner = 'SCOTT' order byobject_id) a) where rownum <= 10) where rn >= 1
Plan hash value: 4175643597-------------------------------------------------------------------------------------| Id |Operation |Name |Starts|E-Rows|A-Rows|Buffers|-------------------------------------------------------------------------------------| 0 |SELECT STATEMENT | | 1| | 10| 6||* 1 |VIEW | | 1| 10| 10| 6||* 2 | COUNT STOPKEY | | 1| | 10| 6|| 3 | VIEW | | 1| 57| 10| 6|| 4 | COUNT | | 1| | 10| 6|| 5 | VIEW | | 1| 57| 10| 6|| 6 | TABLE ACCESS BY INDEX ROWID|T_PAGE | 1| 57| 10| 6||* 7 | INDEX RANGE SCAN |IDX_PAGE_OWNERID| 1| 57| 10| 3|-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("RN">=1)2 - filter(ROWNUM<=10)7 - access("OWNER"='SCOTT')
从执行计划中我们可以看到，SQL 走了索引范围扫描，从索引中扫描了 10 条数据，一共耗费了 6 个逻辑读。这说明该执行计划是正确的。大家可能会问：可不可以在创建索引的时候将 object_id 放在前面、owner 放在后面？现在我们来创建另外一个索引，将 object_id 列放在前面，owner 放在后面。
create index idx_page_idowner on t_page(object_id,owner);
Index created.
我们查看强制走索引（idx_page_idowner）带有 A-Rows 的执行计划（省略了部分数据）。
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------SQL_ID djdnfyyznp3tf, child number 0-------------------------------------
select * from (select * from (select a.*, rownum rnfrom (select /*+ index(t_page idx_page_idowner) */ *from t_page where owner ='SCOTT' order by object_id) a) whererownum <= 10) where rn >= 1
Plan hash value: 2811585238-------------------------------------------------------------------------------------| Id |Operation |Name |Starts|E-Rows|A-Rows|Buffers|-------------------------------------------------------------------------------------| 0 |SELECT STATEMENT | | 1| | 10| 224||* 1 | VIEW | | 1| 10| 10| 224||* 2 | COUNT STOPKEY | | 1| | 10| 224|| 3 | VIEW | | 1| 57| 10| 224|| 4 | COUNT | | 1| | 10| 224|| 5 | VIEW | | 1| 57| 10| 224|| 6 | TABLE ACCESS BY INDEX ROWID|T_PAGE | 1| 57| 10| 224||* 7 | INDEX FULL SCAN |IDX_PAGE_IDOWNER| 1| 247| 10| 221|-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("RN">=1)2 - filter(ROWNUM<=10)7 - access("OWNER"='SCOTT')filter("OWNER"='SCOTT')
从执行计划中我们看到，SQL 走了索引全扫描，从索引中扫描了 10 条数据，但是索引全扫描耗费了 221 个逻辑读，因为要边扫描索引边过滤数据（owner=’SCOTT’），SQL 一共耗费了 224 个逻辑读，与走 object_id 列的执行计划（耗费了 1 273 个逻辑读）相比，虽然也提升了性能，但是性能最好的是走 idx_page_ownerid 这个索引的执行计划（逻辑读为 6）。
大家可能还会问，可不可以只在 owner 列创建索引呢？也就是说不将排序列包含在索引中。如果过滤条件能过滤掉大部分数据（**owner='SCOTT'**），那么这时不将排序列包含在索引中也是可以的，因为这时只需要对少量数据进行排序，少量数据排序几乎对性能没有什么影响。但是如果过滤条件只能过滤掉一部分数据，也就是说返回数据量很多（**owner='SYS'**），这时我们必须将排序列包含在索引中，如果不将排序列包含在索引中，就需要对大量数据进行排序。在实际生产环境中，过滤条件一般都是绑定变量，我们无法控制传参究竟传入哪个值，这就不能确定返回数据究竟是多还是少，所以为了保险起见，建议最好将排序列包含在索引中！
另外要注意，如果排序列有多个列，创建索引的时候，我们要将所有的排序列包含在索引中，并且要注意排序列先后顺序（语句中是怎么排序的，创建索引的时候就对应排序），而且还要注意列是升序还是降序。如果分页语句中排序列只有一个列，但是是降序显示的，创建索引的时候就没必要降序创建了，我们可以使用 HINT: index_desc 让索引降序扫描就行。
现有如下分页语句。
select *from (select *from (select a.*, rownum rnfrom (select *from t_pageorder by object_id, object_name desc) a)where rownum <= 10)where rn >= 1;
创建索引的时候，只能是 object_id 列在前，object_name 列在后面，另外 object_name 是降序显示的，那么在创建索引的时候，我们还要指定 object_name 列降序排序。此外该 SQL 没有过滤条件，在创建索引的时候，我们还要加个常量。现在我们创建如下索引。
create index idx_page_idname on t_page(object_id,object_name desc,0);
Index created.
我们查看强制走索引（idx_page_idname）带有 A-Rows 的执行计划（省略了部分数据）。
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------SQL_ID 20yk62bptjrs9, child number 0-------------------------------------select * from (select * from (select a.*, rownum rnfrom (select /*+ index(t_page idx_page_idname)*/* from t_pageorder by object_id, object_name desc) a) whererownum <= 10) where rn >= 1Plan hash value: 445348578-------------------------------------------------------------------------------------| Id |Operation |Name |Starts|E-Rows| A-Rows |Buffers|-------------------------------------------------------------------------------------| 0 |SELECT STATEMENT | | 1| | 10 | 5||* 1 | VIEW | | 1| 10| 10 | 5||* 2 | COUNT STOPKEY | | 1| | 10 | 5|| 3 | VIEW | | 1| 61800| 10 | 5|| 4 | COUNT | | 1| | 10 | 5|| 5 | VIEW | | 1| 61800| 10 | 5|| 6 | TABLE ACCESS BY INDEX ROWID|T_PAGE | 1| 61800| 10 | 5|| 7 | INDEX FULL SCAN |IDX_PAGE_IDNAME| 1| 61800| 10 | 3|-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("RN">=1)2 - filter(ROWNUM<=10)
如果创建索引的时候将 object_name 放在前面，object_id 放在后面，这个时候，索引中列先后顺序与分页语句中排序列先后顺序不一致，强制走索引的时候，执行计划中会出现 SORT ORDER BY 关键字。因为索引的顺序与排序的顺序不一致，所以需要从索引中获取数据之后再排序，有排序就会出现 SORT ORDER BY。现在我们创建如下索引。
create index idx_page_nameid on t_page(object_name,object_id,0);
Index created.
现在查看强制走索引（idx_page_nameid）带有 A-Rows 的执行计划（省略了部分数据）。
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------SQL_ID 8b8nwayah0z68, child number 0-------------------------------------select * from (select * from (select a.*, rownum rnfrom (select /*+ index(t_page idx_page_nameid)*/* from t_pageorder by object_id, object_name desc) a) where rownum <=10) where rn >= 1Plan hash value: 2869317785-------------------------------------------------------------------------------------| Id |Operation |Name |Starts|E-Rows|A-Rows|Buffers|-------------------------------------------------------------------------------------| 0 |SELECT STATEMENT | | 1| | 10| 37397||* 1 | VIEW | | 1| 10| 10| 37397||* 2 | COUNT STOPKEY | | 1| | 10| 37397|| 3 | VIEW | | 1| 61800| 10| 37397|| 4 | COUNT | | 1| | 10| 37397|| 5 | VIEW | | 1| 61800| 10| 37397|| 6 | SORT ORDER BY | | 1| 61800| 10| 37397|| 7 | TABLE ACCESS BY INDEX ROWID|T_PAGE | 1| 61800| 72608| 37397|| 8 | INDEX FULL SCAN |IDX_PAGE_NAMEID| 1| 61800| 72608| 431|-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("RN">=1)2 - filter(ROWNUM<=10)
如果创建索引的时候没有指定 object_name 列降序排序，那么执行计划中也会出现 SORT ORDER BY。因为索引中排序和分页语句中排序不一致。现在我们创建如下索引。
create index idx_page_idname1 on t_page(object_id,object_name,0);
Index created.
我们查看强制走索引（idx_page_idname1）带有 A-Rows 的执行计划（省略了部分数据）。
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------SQL_ID 2dsmtc9b65a7v, child number 0-------------------------------------select * from (select * from (select a.*, rownum rnfrom (select /*+ index(t_page idx_page_idname1)*/* from t_pageorder by object_id, object_name desc) a) where rownum <=10) where rn >= 1Plan hash value: 170538223-------------------------------------------------------------------------------------| Id |Operation | Name |Starts|E-Rows|A-Rows|Buffers|-------------------------------------------------------------------------------------| 0 |SELECT STATEMENT | | 1| | 10| 1533||* 1 | VIEW | | 1| 10| 10| 1533||* 2 | COUNT STOPKEY | | 1| | 10| 1533|| 3 | VIEW | | 1| 61800| 10| 1533|| 4 | COUNT | | 1| | 10| 1533|| 5 | VIEW | | 1| 61800| 10| 1533|| 6 | SORT ORDER BY | | 1| 61800| 10| 1533|| 7 | TABLE ACCESS BY INDEX ROWID|T_PAGE | 1| 61800| 72608| 1533|| 8 | INDEX FULL SCAN |IDX_PAGE_IDNAME1| 1| 61800| 72608| 430|-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("RN">=1)2 - filter(ROWNUM<=10)
分页语句中如果出现了 SORT ORDER BY，这就意味着分页语句没有利用到索引已经排序的特性，执行计划一般是错误的，这时需要创建正确的索引。
现有如下 SQL（注意，过滤条件有等值条件，也有非等值条件，当然也有 order by），现在要将查询结果分页显示，每页显示 10 条。
select * from t_page where owner = 'SYS' and object_id > 1000 order by object_name;
大家请思考，应该怎么创建索引，从而优化上面的分页语句呢？上文提到，如果分页语句中有排序列，创建索引的时候，要将排序列包含在索引中。所以现在我们只需要将过滤列 owner、object_id 以及排序列 object_name 组合起来创建索引中即可。
因为 owner 是等值过滤，object_id 是非等值过滤，创建索引的时候，我们要优先将等值过滤列和排序列组合在一起，然后再将非等值过滤列放到后面。
create index idx_ownernameid on t_page(owner,object_name,object_id);
Index created.
让我们查看强制走索引（idx_ownernameid）带有 A-Rows 的执行计划（省略了部分数据）。
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------SQL_ID 07z0dkm4a9qdz, child number 0-------------------------------------select * from (select * from (select a.*, rownum rnfrom (select /*+ index(t_page idx_ownernameid) */* from t_pagewhere owner = 'SYS' and object_id >1000 order by object_name) a) whererownum <= 10) where rn >= 1Plan hash value: 2090516350-------------------------------------------------------------------------------------| Id |Operation |Name |Starts|E-Rows| A-Rows |Buffers|-------------------------------------------------------------------------------------| 0 |SELECT STATEMENT | | 1| | 10 | 14||* 1 | VIEW | | 1| 10| 10 | 14||* 2 | COUNT STOPKEY | | 1| | 10 | 14|| 3 | VIEW | | 1| 26937| 10 | 14|| 4 | COUNT | | 1| | 10 | 14|| 5 | VIEW | | 1| 26937| 10 | 14|| 6 | TABLE ACCESS BY INDEX ROWID|T_PAGE | 1| 26937| 10 | 14||* 7 | INDEX RANGE SCAN |IDX_OWNERNAMEID| 1| 254| 10 | 4|-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("RN">=1)2 - filter(ROWNUM<=10)7 - access("OWNER"='SYS' AND "OBJECT_ID">1000)filter("OBJECT_ID">1000)
执行计划中没有 SORT ORDER BY，逻辑读也才 14 个，说明执行计划非常理想。也许大家会问，为何不创建如下这样索引呢？
create index idx_owneridname on t_page(owner,object_id,object_name);
Index created.
我们查看强制走索引（idx_owneridname）带有 A-Rows 的执行计划（省略了部分数据）。
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------SQL_ID 7bm9sf2u94uxa, child number 0-------------------------------------select * from (select * from (select a.*, rownum rnfrom (select /*+ index(t_page idx_owneridname) */* from t_pagewhere owner = 'SYS' and object_id >1000 order by object_name) a) whererownum <= 10) where rn >= 1Plan hash value: 2498002320-------------------------------------------------------------------------------------| Id |Operation |Name |Starts|E-Rows|A-Rows|Buffers|-------------------------------------------------------------------------------------| 0 |SELECT STATEMENT | | 1| | 10| 1002||* 1 | VIEW | | 1| 10| 10| 1002||* 2 | COUNT STOPKEY | | 1| | 10| 1002|| 3 | VIEW | | 1| 26937| 10| 1002|| 4 | COUNT | | 1| | 10| 1002|| 5 | VIEW | | 1| 26937| 10| 1002|| 6 | SORT ORDER BY | | 1| 26937| 10| 1002|| 7 | TABLE ACCESS BY INDEX ROWID|T_PAGE | 1| 26937| 29919| 1002||* 8 | INDEX RANGE SCAN |IDX_OWNERIDNAME| 1| 26937| 29919| 189|-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("RN">=1)2 - filter(ROWNUM<=10)8 - access("OWNER"='SYS' AND "OBJECT_ID">1000 AND "OBJECT_ID" IS NOT NULL)
该执行计划中有 SORT ORDER BY，说明没有用到索引已经排序特性，而且逻辑读为 1 002 个，这说明该执行计划是错误的。为什么该执行计划是错误的呢？这是因为该分页语句是根据 object_name 进行排序的，但是创建索引的时候是按照 owner、object_id、object_name 顺序创建索引的，索引中前 5 条数据如下。
select *from (select rownum rn, owner, object_id, object_namefrom t_pagewhere owner = 'SYS'and object_id > 1000order by owner, object_id, object_name)where rownum <= 5;
RN OWNER OBJECT_ID OBJECT_NAME---------- ----- ---------- ------------1 SYS 1001 NOEXP$2 SYS 1002 EXPPKGOBJ$3 SYS 1003 I_OBJTYPE4 SYS 1004 EXPPKGACT$5 SYS 1005 I_ACTPACKAGE
在这前 5 条数据中，我们按照分页语句排序条件 object_name 进行排序，应该是第 4 行数据显示为第一行数据，但是它在索引中排到了第 4 行，所以索引中数据的顺序并不能满足分页语句中的排序要求，这就产生了 SORT ORDER BY，进而导致执行计划错误。为什么按照 owner、object_name、object_id 顺序创建索引，执行计划是对的呢？现在我们取索引中前 5 条数据。
select *from (select rownum rn, owner, object_id, object_namefrom t_pagewhere owner = 'SYS'and object_id > 1000order by owner,object_name,object_id)where rownum <= 5;
RN OWNER OBJECT_ID OBJECT_NAME---------- ----- ---------- --------------------------------1 SYS 34042 /1000323d_DelegateInvocationHa2 SYS 44844 /1000e8d1_LinkedHashMapValueIt3 SYS 23397 /1005bd30_LnkdConstant4 SYS 19737 /10076b23_OraCustomDatumClosur5 SYS 45460 /100c1606_StandardMidiFileRead
索引中的数据顺序完全符合分页语句中的排序要求，这就不需要我们进行 SORT ORDER BY 了，所以该执行计划是对的。
现在我们继续完善分页语句的优化思路：如果分页语句中有排序（order by），要利用索引已经排序特性，将 order by 的列按照排序的先后顺序包含在索引中，同时要注意排序是升序还是降序。如果分页语句中有过滤条件，我们要注意过滤条件是否有等值过滤条件，如果有等值过滤条件，要将等值过滤条件优先组合在一起，然后将排序列放在等值过滤条件后面，最后将非等值过滤列放排序列后面。如果分页语句中没有等值过滤条件，我们应该先将排序列放在索引前面，将非等值过滤列放后面，最后利用 rownum 的 COUNT STOPKEY 特性来优化分页 SQL。如果分页中没有排序，可以直接利用 rownum 的 COUNT STOPKEY 特性来优化分页 SQL。
如果我们想一眼看出分页语句执行计划是正确还是错误的，先看分页语句有没有 ORDER BY，再看执行计划有没有 SORT ORDER BY，如果执行计划中有 SORT ORDER BY，执行计划一般都是错误的。
请大家思考，如下分页语句应该如何建立索引（提示：该 SQL 没有等值过滤）？
select *from (select *from (select a.*, rownum rnfrom (select *from t_pagewhere owner like 'SYS%'and object_id > 1000order by object_name) a)where rownum <= 10)where rn >= 1;
如果分页语句中排序的表是分区表，这时我们要看分页语句中是否有跨分区扫描，如果有跨分区扫描，创建索引一般都创建为 global 索引，如果不创建 global 索引，就无法保证分页的顺序与索引的顺序一致。如果就只扫描一个分区，这时可以创建 local 索引。
现在我们创建一个根据 object_id 范围分区的分区表 p_test 并且插入测试数据。
create table p_test(OWNER VARCHAR2(30),OBJECT_NAME VARCHAR2(128),SUBOBJECT_NAME VARCHAR2(30),OBJECT_ID NUMBER,DATA_OBJECT_ID NUMBER,OBJECT_TYPE VARCHAR2(19),CREATED DATE,LAST_DDL_TIME DATE,TIMESTAMP VARCHAR2(19),STATUS VARCHAR2(7),TEMPORARY VARCHAR2(1),GENERATED VARCHAR2(1),SECONDARY VARCHAR2(1),NAMESPACE NUMBER,EDITION_NAME VARCHAR2(30)) partition by range (object_id)(partition p1 values less than (10000),partition p2 values less than (20000),partition p3 values less than (30000),partition p4 values less than (40000),partition p5 values less than (50000),partition p6 values less than (60000),partition p7 values less than (70000),partition p8 values less than (80000),partition pmax values less than(maxvalue));
Table created.
insert into p_test select * from dba_objects;
72662 rows created.
commit;
现有如下分页语句（根据范围分区列排序）。
select *from (select *from (select a.*, rownum rnfrom (select * from p_test order by object_id) a)where rownum <= 10)where rn >= 1;
该分页语句没有过滤条件，因此会扫描表中所有分区。因为排序列恰好是范围分区列，范围分区每个分区的数据也是递增的，这时我们创建索引可以创建为 local 索引。但是如果将范围分区改成 LIST 分区或者 HASH 分区，这时我们就必须创建 global 索引，因为 LIST 分区和 HASH 分区是无序的。
现在我们创建 local 索引。
create index idx_ptest_id on p_test(object_id,0) local;
Index created.
我们查看强制走索引（idx_ptest_id）带有 A-Rows 的执行计划（省略了部分数据）。
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------SQL_ID 3rp1uz98fgggq, child number 0-------------------------------------select * from (select * from (select a.*, rownum rnfrom (select /*+ index(p_test idx_ptest_id) */* from p_testorder by object_id) a) where rownum <= 10) where rn >= 1Plan hash value: 1636704844-------------------------------------------------------------------------------------| Id |Operation |Name |Starts|E-Rows|A-Rows|Buffers|-------------------------------------------------------------------------------------| 0 |SELECT STATEMENT | | 1| | 10| 5||* 1 | VIEW | | 1| 10| 10| 5||* 2 | COUNT STOPKEY | | 1| | 10| 5|| 3 | VIEW | | 1| 51888| 10| 5|| 4 | COUNT | | 1| | 10| 5|| 5 | VIEW | | 1| 51888| 10| 5|| 6 | PARTITION RANGE ALL | | 1| 51888| 10| 5|| 7 | TABLE ACCESS BY LOCAL INDEX ROWID|P_TEST | 1| 51888| 10| 5|| 8 | INDEX FULL SCAN |IDX_PTEST_ID| 1| 51888| 10| 3|-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("RN">=1)2 - filter(ROWNUM<=10)
现有如下分页语句（根据 object_name 排序）。
select *from (select *from (select a.*, rownum rnfrom (select * from p_test order by object_name) a)where rownum <= 10)where rn >= 1;
这时我们就需要创建 global 索引，如果创建 local 索引会导致产生 SORT ORDER BY。
create index idx_ptest_name on p_test(object_name,0) local;
Index created.
现在查看强制走索引（idx_ptest_name）带有 A-Rows 的执行计划（省略了部分数据）。
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------SQL_ID 50hgw72gnvs83, child number 0-------------------------------------select * from (select * from (select a.*, rownum rnfrom (select /*+ index(p_test idx_ptest_name) */* from p_testorder by object_name) a) where rownum <= 10) where rn >=1Plan hash value: 2548872510-------------------------------------------------------------------------------------| Id |Operation |Name |Starts|E-Rows|A-Rows|Buffers |-------------------------------------------------------------------------------------| 0 |SELECT STATEMENT | | 1| | 10| 35530 ||* 1 | VIEW | | 1| 10| 10| 35530 ||* 2 | COUNT STOPKEY | | 1| | 10| 35530 || 3 | VIEW | | 1| 51888| 10| 35530 || 4 | COUNT | | 1| | 10| 35530 || 5 | VIEW | | 1| 51888| 10| 35530 || 6 | SORT ORDER BY | | 1| 51888| 10| 35530 || 7 | PARTITION RANGE ALL | | 1| 51888| 72662| 35530 || 8 | TABLE ACCESS BY LOCAL INDEX ROWID|P_TEST | 9| 51888| 72662| 35530 || 9 | INDEX FULL SCAN |IDX_PTEST_NAME| 9| 51888| 72662| 392 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("RN">=1)2 - filter(ROWNUM<=10)
现在我们将索引 idx_ptest_name 重建为 global 索引。
drop index idx_ptest_name;
Index dropped.
create index idx_ptest_name on p_test(object_name,0);
Index created.
查看强制走索引（idx_ptest_name）带有 A-Rows 的执行计划（省略了部分数据）。
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------SQL_ID 50hgw72gnvs83, child number 0-------------------------------------select * from (select * from (select a.*, rownum rnfrom (select /*+ index(p_test idx_ptest_name) */* from p_testorder by object_name) a) where rownum <= 10) where rn >=1Plan hash value: 4135902528-------------------------------------------------------------------------------------| Id |Operation |Name |Starts|E-Rows|A-Rows|Buffers|------------------------------------------------------------------------------------------| 0 |SELECT STATEMENT | | 1| | 10| 10||* 1 | VIEW | | 1| 10| 10| 10||* 2 | COUNT STOPKEY | | 1| | 10| 10|| 3 | VIEW | | 1| 51888| 10| 10|| 4 | COUNT | | 1| | 10| 10|| 5 | VIEW | | 1| 51888| 10| 10|| 6 | TABLE ACCESS BY GLOBAL INDEX ROWID|P_TEST | 1| 51888| 10| 10|| 7 | INDEX FULL SCAN |IDX_PTEST_NAME| 1| 51888| 10| 4|-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("RN">=1)2 - filter(ROWNUM<=10)
若有收获，就点个赞吧
0 人点赞
