原始未分页查询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最内层返回给中间层的是所有满足条件的数据,而中间层返回给最外层的也是所有数据。数据的过滤在最外层完 成,显然这个效率要比第二个查询低得多。
这种分页对于单表查询、多表查询一样有效。