错误的分页框架

select *
  from (select t.*, rownum rn from (需要分页的SQL) t)
 where rn >= 1
   and rn <= 10;

正确的分页框架 ( 可以利用 ROW COUNT STOPKEY 特性 )

select *
  from (select *
          from (select a.*, rownum rn
                  from (需要分页的SQL) a)
         where rownum <= 10)
 where rn >= 1;

我们先创建一个测试表 T_PAGE 。

SQL> create table t_page as select * from dba_objects;

案例一：分页语句中没有 where 条件。

现有如下 SQL （没有过滤条件，只有排序），要将查询结果分页显示，每页显示 10 条。

select * from t_page order by object_id;

采用错误的分页框架：

select *
  from (select t.*, rownum rn from (select * from t_page order by object_id) t)
 where rn >= 1
   and rn <= 10;

从执行计划中我们可以看到该 SQL 走了全表扫描，假如 T_PAGE 有上亿条数据，先要将该表（上亿条的表）进行排序（ SORT ORDER BY ），再取出其中 10 行数据，这时该 SQL 会产生严重的性能问题。所以该 SQL 不能走全表扫描，必须走索引扫描。可以在 object_id 列上建立索引，让其不走全表扫描。（ 意思就是说索引自带排序功能，可以消去 SORT ORDER BY 这一步 ）

由于 object_id 列允许为 null ，如果不添加常量（不一定是 0 ，可以是 1 、 2 、 3 ，也可以是英文字母），索引中就不能存储 null 值，然而 SQL 并没有写成以下写法：

select * from t_page where object_id is not null order by object_id;

因为 SQL 中并没有剔除 null 值，所以我们必须要添加一个常量，让索引存储 null 值，这样才能使 SQL 走索引。现在我们来看一下强制走索引的 A-Rows 执行计划

create index idx_page on t_page(object_id,0);

select *
  from (select t.*, rownum rn from (select /*+index(t_page idx_page)*/ * from t_page order by object_id) t)
 where rn >= 1
   and rn <= 10;

因为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）就停止了。为什么没有走最理想的执行计划呢？这是因为分页框架错了!（ 总的来说就是，索引不需要扫描全部叶子快去返回全部数据，而是返回 10 行数据就可以停下来了，这样最多就扫几个叶子快）

采用正确的分页架构 ( 用到了 rownum 的 COUNT STOPKEY 特性， COUNT STOPKEY 就是当扫描到指定行数的数据之后， SQL 就停止运行 ) ：

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;

看 ~ ，采用正确的分页结构和走了索引后就 消除了 SORT ORDER BY 这一步的执行计划 ( 索引的的功劳 )( 排序列 (order by 列 ) 一定要在索引中才能消除 SORT ORDER BY) ，而且用上了 rownum 的 COUNT STOPKEY 特性 ( 正确的分页架构的功劳 ) 。

如果分页语句中有排序（ order by ）， 要利用索引已经排序特性，将 order by 的列包含在索引中 ，同时也要利用 rownum 的 COUNT STOPKEY 特性来优化分页 SQL 。如果分页中没有排序，可以直接利用 rownum 的 COUNT STOPKEY 特性来优化分页 SQL 。

案例二：分页语句中出现了 where 条件。

select * from t_page where owner = 'SCOTT' order by object_id
select * from t_page where owner = 'SYS' order by object_id

为什么一样的语句， SCOTT 和 SYS 两个变量会导致这么大的效率差异，入口的 A-ROWS 差异这么大。

我认为不是分页语句的原因，是因为 owner 上没索引，在通过 id 列排序后的结果集进行回表去找符合 owner 的条件的话，由于符合 scott 的用户很少，因此要把 owner 列也放在索引里。

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;

select *
  from (select *
          from (select a.*, rownum rn
                  from (select /*+index(t_page idx_page)*/ * from t_page where owner = 'SYS' order by object_id) a)
         where rownum <= 10)
 where rn >= 1;

由于索引排序的特性，我们必须把 order by 列放在索引里面，同时也要考虑到 owner='SCOTT' 的少数据量，因此 owner 列也得在索引里面，要去搞个这俩列的组合索引。 而且 owner 列是过滤列， object_id 列是排序列，如果 object_id 列在前，那么非引导列 owner 由于有产生过滤，会发生 INDEX SKIP SCAN ；而如果 owner 列在前面的话，非引导列 object_id 只会去排序，而不会去过滤，因此路径是 INDEX RANGE SCAN 。 所以肯定是推荐让 owner 列作为引导列 。实验如下所示：

create index idx_owner_id on t_page(owner,object_id);

select *
  from (select *
          from (select a.*, rownum rn
                  from (select /*+index(t_page idx_owner_id)*/ * from t_page where owner = 'SCOTT' order by object_id) a)
         where rownum <= 10)
 where rn >= 1;

create index idx_id_owner on t_page(object_id,owner);

select *
  from (select *
          from (select a.*, rownum rn
                  from (select /*+index(t_page idx_id_owner)*/ * from t_page where owner = 'SCOTT' order by object_id) a)
         where rownum <= 10)
 where rn >= 1;