Oracle数据库中分页查询中排序及效率问题

原始未分页查询Sql代码如下：

select
 ROWNUM
 rn,
 t
.
id ID,
 o.
name
 YYB,
u.
name
 XM,
 t
.
MC from
 tZDYSX  

t
,
tuser u,
lborganization o where
 t
.
cjr=
u.
id and
 u.
orgid=
o.
orgcode and
 t
.
gx =
 1  

order
 by
 ID

结果如下：

RN	ID	YYB	XM	MC
3	49	某证券总部	管理员	测试
4	96	某证券总部	管理员	持有上港10000股以上
5	102	某证券总部	管理员	十年规划
14	105	某证券总部	管理员	开发渠道为上海
11	106	某证券总部	管理员	万科A
12	107	某证券总部	管理员	11
13	108	某证券总部	管理员	今天过生日的客户
2	109	某证券总部	管理员	客户状态正常
6	110	某证券总部	管理员	无交易
7	111	某证券总部	管理员	OA
8	112	某证券总部	管理员	幸运客户
9	113	某证券总部	管理员	风险型
10	114	某证券总部	管理员	tst
22	115	白沙网上交易	安昌彪	安客户正常
1	118	某证券总部	管理员	213
18	119	某证券总部	管理员	客户号包含1008
17	120	某证券总部	管理员	aaa
19	123	某证券总部	管理员	ssssssss
20	124	某证券总部	管理员	www
21	126	某证券总部	管理员	123123
15	127	某证券总部	管理员	1212
16	128	某证券总部	管理员	aaaaaa

22 rows selected 最初我使用如下Sql代码查询：

select
 *
 from
 (
select
 ROWNUM
 rn,
 t
.
id ID,
 o.
name
 YYB,
u.
name
 XM,
 t
.
MC    from
 tZDYSX t
,
tuser u,
lborganization o where
 t
.
cjr=
u.
id and
 u.
orgid=
o.
orgcode and
    t
.
gx =
 1 order
 by
 t
.
ID )
Where
 rn>
10 and
 rn<
=
20;

这种方法能成功分页，结果如下：

RN	ID	YYB	XM	MC
14	105	某证券总部	管理员	开发渠道为上海
11	106	某证券总部	管理员	万科A
12	107	某证券总部	管理员	11
13	108	某证券总部	管理员	今天过生日的客户
18	119	某证券总部	管理员	客户号包含1008
17	120	某证券总部	管理员	aaa
19	123	某证券总部	管理员	ssssssss
20	124	某证券总部	管理员	www
15	127	某证券总部	管理员	1212
16	128	某证券总部	管理员	aaaaaa

10 rows selected

从结果看来，有个问题：此语句Sql代码
order by CJSJ DESC  被执行，但是是在分后的第11到20条记录的结果集中再进行排序，而不是先排序后分页。（本来希望显示ID为112到126，结果变为105到128）

后来变为以下Sql代码查询：

SQL code 复制代码

SELECT
 *
   

FROM
(
   

SELECT
 ROWNUM
 RN,
TA.
*
   

FROM
(
  

select
 t
.
id ID,
 o.
name
 YYB,
u.
name
 XM,
 t
.
MC  

from
 tZDYSX t
,
tuser u,
lborganization o   

where
 t
.
cjr=
u.
id and
 u.
orgid=
o.
orgcode and
 t
.
gx =
 1 order
 by
 t
.
ID  

)
TA WHERE
 ROWNUM
 <
=
 20  

)
WHERE
 RN >
 10 

SELECT
 *


FROM
(


SELECT
 ROWNUM
 RN,
TA.
*


FROM
(


select
 t
.
id ID,
 o.
name
 YYB,
u.
name
 XM,
 t
.
MC

from
 tZDYSX t
,
tuser u,
lborganization o

where
 t
.
cjr=
u.
id and
 u.
orgid=
o.
orgcode and
 t
.
gx =
 1 order
 by
 t
.
ID

)
TA WHERE
 ROWNUM
 <
=
 20

)
WHERE
 RN >
 10

结果如下：

RN	ID	YYB	XM	MC
11	112	某证券总部	管理员	幸运客户
12	113	某证券总部	管理员	风险型
13	114	某证券总部	管理员	tst
14	115	白沙网上交易	安昌彪	安客户正常
15	118	某证券总部	管理员	213
16	119	某证券总部	管理员	客户号包含1008
17	120	某证券总部	管理员	aaa
18	123	某证券总部	管理员	ssssssss
19	124	某证券总部	管理员	www
20	126	某证券总部	管理员	123123

10 rows selected

看来结果是正确的。

总结：第二种方法其中最内层的查询Sql代码

select
 t
.
id ID,
 o.
name
 YYB,
u.
name
 XM,
 t
.
MC  

from
 tZDYSX t
,
tuser u,
lborganization o   

where
 t
.
cjr=
u.
id and
 u.
orgid=
o.
orgcode and
 t
.
gx =
 1 order
 by
 t
.
ID

表示不进行翻页的原始查询语句。ROWNUM <= 20和RN > 10控制分页查询的每页的范围。
第二种方法在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小，在上面的分页查询语句中，这种考虑主要体现在WHERE ROWNUM <= 20这句上。

选择第11到20条记录存在两种方法，第二种方法正是在查询的第二层通过ROWNUM <= 20来控制最大值，在查询的最外层控制最小值。而第一种方法是去掉查询第二层的WHERE ROWNUM <= 20语句，在查询的最外层控制分页的最小值和最大值。

一般来说，第二个查询的效率比第一个高得多，这是由于CBO 优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。对于第二个查询语句，第2层的查询条件WHERE ROWNUM <= 20就可以被Oracle推入到内层查询中，这Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。

而第一个查询语句，由于查询条件Where rn>10 and rn<=20是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什 么）。因此，对于第一个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完 成，显然这个效率要比第二个查询低得多。

这种分页对于单表查询、多表查询一样有效。