使用索引进行高效分页pagination_PART1

 分页查询在ORACLE里可以使用rownum,row_number分析函数等实现。

rownum是oracle中的一个伪列(pseudo column)，其目的就是给查询出来的结果集标注行号。可以实现查询前n行(top-n)，中间几行(middle-n)，{BANNED}{BANNED}最佳佳后几行(bottom-n)的功能。但是rownum又是一个很特殊的列，使用过程中，首先要弄清楚oracle rownum的原理，然后加以分析。

注意点：

1.rownum不可以直接在前面加上表名或别名等。其他伪列如level,rowid等一样。

如 select t.rownum from table t;是错误的。

2.rownum和where在同一层查询中，where条件之后使用rownum比较，只能使用<=,<,!=,<>,不能使用>,>=(>=1是可以的,>=0,>0和不加效果一样),=(使用=，只能是where rownum=1才可以,rownum>1不可以)。否则不返回任何数据。如果使用!=或<>,那么只是返回前n-1行，其他按照rownum工作原理推算。

如 select rownum,name from emp where rownum>=5;没有结果

   Select rownum,name from emp where rownum=1;返回第1行

   Select rownum,name from emp where rownum !=10;//返回1-9行

Select rownum,name from emp where rownum >=1;//返回全部行,>0,>=0一样

3.当rownum和order by在一个语句级别中(同一层)使用的时候.看这个查询的数据是否从索引中获取(或者根据索引先得到rowid然后定位行)的，如果不是，那么就是先查询出来,每行标上rownum，然后order by将结果重新排序，那么rownum的顺序是乱的。如果排序的数据是从索引中查询的，这样结果有序。这取决于执行计划，执行计划又和oracle优化器相关。

Rownum原理：

1 Oracle executes your query.

1.执行查询操作

2 Oracle fetches the first row and calls it row number 1.

2.将{BANNED}中国{BANNED}中国第一行的row num置为1

3 Have we gotten past row number meets the criteria? If no, then Oracle discards the row, If yes, then Oracle return the row.

3.将得到的行的row num与条件相比较，如果不匹配，则抛弃行，如果匹配，则返回行

4 Oracle fetches the next row and advances the row number (to 2, and then to 3, and then to 4, and so forth).

4.oracle获取下一行，然后将rownum增1

5 Go to step 3.

5.返回第3步

4.rownum具有固定结果集的作用，这是rownum非常重要的一个特性。比如有的内联视图中没有使用rownum,那么优化器可以使用谓词推进推入内联视图中（控制条件的执行顺序），但是如果使用rownum,内联视图的结果集就固定了，外层谓词无法推入到内联视图中(使用no_merge,no_push_pred对这种情况经常无效)，下面看一个例子：

drop table t;

create table t

(

id varchar2(10),

 name varchar2(10),

status number

);

create index idx_t on t(name);

DINGJUN123>select * from t where rownum<10;

j                    NAME                     STATUS

-------------------- -------------------- ----------

1                    a                             1

2                    b                             1

3                    c                             1

4                    d                             1

5                    d                             1

6                    d                             1

aa                   e                             1

bb                   d                             0

0                    x                             1

----------下面基本都是0 x 1,100000条,10000条不走索引，例子重新设计

现在演示一个查询：

DINGJUN123>set autotrace traceonly

DINGJUN123>select *  from (

  2  select * from t where status =1 and name='d'

  3  ) where  id>1  ;

ERROR:

ORA-01722: 无效数字

未选定行

执行计划

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |     7 |     7   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T    |     1 |     7 |     7   (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("NAME"='d' AND TO_NUMBER("T"."ID")>1 AND "STATUS"=1)

报错了,我们看执行计划，对ID进行了自动类型转换，来看我们的内层查询：

DINGJUN123>select * from t where status =1 and name='d' ;

j                    NAME                     STATUS

-------------------- -------------------- ----------------------------------------

4                    d                             1

5                    d                             1

6                    d                             1

就算to_number(id)也应该没有问题啊，那为什么报无效的数字错误呢？？下面来解密：注意看上面的执行计划：

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("NAME"='d' AND TO_NUMBER("T"."ID")>1 AND "STATUS"=1)

       怎么将条件放一起了，而且是先name='d'然后立马类型转换，看select * from t where name='d';

DINGJUN123>select * from t where name='d';

j                    NAME                     STATUS

-------------------- -------------------- ----------

4                    d                             1

5                    d                             1

6                    d                             1

bb                   d                             0

       肯定转换到id='bb'那条失败了，本来我们内层查询是两个条件，外层一个，结果优化器将我们的外层条件where id>1推入到了内层，而且还放在了中间，先于status=1执行，导致类型转换失败。怎么办？？？在内层加rownum条件可以固化内层查询结果集，防止外层条件谓词推进，只要rownum条件不影响我们原来的结果集就可以，我们改下:

       DINGJUN123>select *  from (

  2  select * from t where status =1 and name='d' and rownum >= 1

  3  ) where  id>1  ;

j                    NAME                     STATUS

-------------------- -------------------- ----------

4                    d                             1

5                    d                             1

6                    d                             1

执行计划

----------------------------------------------------------

Plan hash value: 518606641

---------------------------------------------------------------------------

| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |      |     4 |   108 |     7   (0)| 00:00:01

|*  1 |  VIEW                |      |     4 |   108 |     7   (0)| 00:00:01

|   2 |   COUNT              |      |       |       |            |

|*  3 |    FILTER            |      |       |       |            |

|*  4 |     TABLE ACCESS FULL| T    |     4 |    28 |     7   (0)| 00:00:01

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(TO_NUMBER("ID")>1)

   3 - filter(ROWNUM>=1)

   4 - filter("NAME"='d' AND "STATUS"=1)

看现在，不影响结果集，而且运行正确，你看现在原来合并的条件现在被分为1和4了，外层的id>1未被推进到内层查询中，这就是用到了rownum固化结果集的功能。

接PART2:使用索引进行高效分页pagination_PART2