oracle数据库如何写翻页_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 1VIEW

32COUNT (STOPKEY)

43VIEW

54INDEX (RANGE SCAN) OF 'I_AO_CREATED' (NON-UNIQUE)

61TABLE 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

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页