ORACLE数据库分页查询/翻页 最佳实践

 

ORACLE数据库分页查询/翻页

最佳实践

 

 

一、示例数据：

Select Count(*) From dba_objects ;

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

    COUNT(*)

       6254

Create table ao as select * f rom dba_objects ;

Create index i_ao_created on ao(created) ;

Create index i_ao_id on ao(object_id)  ;

Create table bo as select * from dba_objects where rownum<=100 ;

Analyze table ao compute statistics for table for all indexes ;

Analyze table bo compute statistics ;

 

二、求页数

无论以本文中哪种SQL写法，为了获得查询的总页数，不可避免的总要进行一次COUNT(*) 的查询过程，SQL如下：

单表：

Select count(*) into rowcount from ao where created > … and object_id between …

多表：

Select count(*) into rowcount from ao a,bo b where ao.object_id = bo.object_id and ao.object_name like …. And bo.ojbect_id > …

 

设单页行数为 pagesize (缺省为 20) ，则总页数 pagecount =  ceil(rowcount / pagesize)

CEIL函数： 返回大于或等于给出数字的最小整数
SQL> select ceil(21.000001),ceil(1),ceil(10.1234) from dual;

 

CEIL(21.000001)    CEIL(1)     CEIL(10.1234)

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

        22              1            11

 

提示：在具体项目中做分页实现时，设计上如果不要求算出总页数，则可以省去此COUNT(*) 的查询，则整个翻页性能将有所提升（因为少做了一个查询）

 

综上所述，对于每次的分页查询，需要输入的参数有2个：

页号：   pageindex 

页行数： PAGESIZE （一般为整个系统设一个常量，如前面定义的20）

 

则每个页面应该显示的记录为：

IF pageindex <= pagecount THEN

Firstrow = (pageindex – 1)*PAGESIZE + 1

Lastrow = pageindex * PAGESIZE

END IF;

 

三、分页查询实现SQL方案

 

方案一：（单表分页查询）

适用场景：

A、 WHERE部分条件仅仅通过索引查询就可满足所有查询过滤条件；

B、满足A条件，且有ORDER BY ，且WHERE 条件中所使用的索引，正好适合 ORDER BY 字段的要求。

SQL如下：

select /*+ ordered use_nl(v3 t) */
 t.*
 from (select v2.rd
          from (select v1.rd, rownum rn
                  from (select a.rowid rd
                          from ao a
                         where a.created >= sysdate - 100
                         order by created) v1
                 where rownum <= 40) v2   -- 40 ：lastrow
         where rn >= 21) v3,               -- 21 ：firstrow
       ao t
 where v3.rd = t.rowid

 

执行计划：

0      SELECT STATEMENT Optimizer=CHOOSE

 1    0   NESTED LOOPS

 2    1     VIEW

 3    2       COUNT (STOPKEY)

 4    3         VIEW

 5    4           INDEX (RANGE SCAN) OF 'I_AO_CREATED' (NON-UNIQUE)

 6    1     TABLE ACCESS (BY USER ROWID) OF 'AO'

 

评价：该种场景下的分页SQL写法执行效率最高效，但限制较多（见适用场景）。

 

不适用场景：

以上述SQL为例，如果WHERE 条件部分为 a.created >= sysdate – 100 and a.object_name like ‘ABC%’ and … 即不适应于场景A条件所述，或有ORDER BY ，如：ORDER BY OBJECT_NAME ,CREATED ，即不适应于场景B条件所述，则方案一SQL不适用。

 

方案二：（单表/多表分页查询）

适用场景：

所有场景都适用，包括方案一所指场景。

SQL如下（单表）：

 select v2.*
   from (select v1.*, rownum rn
           from (select object_name,
                        object_id,
                        owner,
                        data_object_id,
                        created
                   from ao
                  where created >= sysdate - 100
                    and owner = 'SYS'
                  order by object_name, created) v1
          where rownum <= 40) v2      -- 40 ：lastrow
 where rn >= 21                       -- 21 ：firstrow

 

执行计划：

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   VIEW

2    1     COUNT (STOPKEY)

3    2       VIEW

4    3         SORT (ORDER BY STOPKEY)

5    4           TABLE ACCESS (BY INDEX ROWID) OF 'AO'

6    5             INDEX (RANGE SCAN) OF 'I_AO_CREATED' (NON-UNIQUE)

 

SQL如下（多表）

 select v2.*
 from (select v1.*, rownum rn
          from (select ao.object_name,
                       ao.owner,
                       ao.created,
                       bo.object_type,
                       bo.last_ddl_time
                  from ao, bo
                 where ao.object_id = bo.object_id
                   and ao.created >= sysdate - 300
                   and ao.owner = 'SYS'
                   and bo.status = 'VALID'
                 order by ao.object_name, bo.created) v1
         where rownum <= 40) v2      -- 40：lastrow
 where rn >= 21                       -- 21: firstrow

 

执行计划：

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   VIEW

2    1     COUNT (STOPKEY)

3    2       VIEW

4    3         SORT (ORDER BY STOPKEY)

5    4           TABLE ACCESS (BY INDEX ROWID) OF 'AO'

6    5             NESTED LOOPS

7    6               TABLE ACCESS (FULL) OF 'BO'

8    6               INDEX (RANGE SCAN) OF 'I_AO_ID'

 

评价：以上两种（单表/多表）的分页查询写法很类似，从性能上来说都逊于方案一的做法，但这和场景有关，是没有办法的事。

 

 

综述：本文所述的两种分页方案，中心是阐述分页的实现思想，即翻页由数据库查询来完成，不占用额外的前后端会话内存，不在前端/中间层缓存数据，实现翻页时方法尽量简单、实用，需要强调的是，不论以哪种方案实现翻页，都要求查询的主体部分（SQL中粗体部分）的WHERE条件要求高效，这样整个翻页的执行速度才会快。方案一是取合理的利用了索引的特性；方案二则在主查询不变的基础上通过灵活运用ROWNUM伪列，实现查询行的定位，具体应用当中能够使用哪种方案，需具体评估场景，在之前请深刻理解本文两种方案中示例SQL的实现。

应当特别注意一个问题，在获得分页的查询SQL时，注意WHERE条件部分不应该是拼出来的，而应该是以绑定变量的方式设置条件字段值！！

SQL形如：

select v2.*
   from (select v1.*, rownum rn
           from (select object_name,
                        object_id,
                        owner,
                        data_object_id,
                        created
                   from ao
                  where created >= :created
                    and owner = :owner
                  order by object_name, created) v1
          where rownum <= :lastrow) v2     
 where rn >= :firstrow