Rownum分页故障解决一例

最新推荐文章于 2022-11-17 14:17:56 发布
最新推荐文章于 2022-11-17 14:17:56 发布
阅读量182 收藏
点赞数
文章标签： 数据库 java 前端 ViewUI

 

在目前很多系统中，界面数据分页（Page）已经成为一项通用功能。基本上，每种框架、结构和对应的数据库，都有一些成熟的现成解决方案。在Oracle中，rownum伪列是初学者非常容易用错的功能。本篇就介绍一个实际的分页和rownum结合使用时候的故障案例。

 

1、问题简述

 

系统后台数据库采用Oracle 11gR2版本。

 

 

SQL> select * from v$version;

 

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE     11.2.0.3.0       Production

TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

 

 

数据表inputfile_log记录数据文件日志信息。在界面上提供数据查询、操作和排序。界面提供所有字段的查询排序功能。数据表主键为inputfile_log_seq，一个字段为ISO_CODE。

 

在测试阶段，测试人员不时报出某些特定字段在排序过程中有一些问题，排序之后，跳转到下一页数据取值不变。但是，在架构层面，没有明显的故障和Bug存在，前端故障也大都是偶发性的。

 

一个偶然的机会，测试和开发人员定位到了问题的所在：对其他字段进行排序处理之后，分页功能一般不会有故障。只有在ISO_CODE进行排序，之后进行跳转分页的时候，才会有问题，而且问题也是偶发性，不具有必然性。

 

经过定位，发现页面前端输出的SQL语句有一些问题。页面前端采用Hibernate实体映射类。分页采用数据库“真分页”技术——数据库只把要显示的数据传递到前端。Hibernate负责SQL语句的生成执行。

 

定位的SQL语句为：

 

 

select *

  from (select a1.inputfile_log_seq, a1.ISO_CODE,rownum rwn

          from (select * from inputfile_log t order by t.ISO_CODE) a1

         where rownum <= 50)

 where rwn > 45;

 

 

Oracle中分页的语句方案很多，这种利用rownum取段的方法也是普遍接受的方法。问题在哪儿呢？下面我们执行语句看结果。

 

 

 

SQL> select *

  2    from (select a1.inputfile_log_seq, a1.ISO_CODE,rownum rwn

  3            from (select * from inputfile_log t order by t.ISO_CODE) a1

  4           where rownum <= 50)

  5   where rwn > 45;

 

INPUTFILE_LOG_SEQ ISO_CODE        RWN

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

             1901 AU               46

             1903 AU               47

             1906 AU               48

             1142 AU               49

             1157 AU               50

 

 

SQL> select *

  2    from (select a1.inputfile_log_seq, a1.ISO_CODE, rownum rwn

  3            from (select * from inputfile_log t order by t.ISO_CODE) a1

  4           where rownum <= 55)

  5   where rwn > 50;

 

INPUTFILE_LOG_SEQ ISO_CODE        RWN

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

             1901 AU               51

             1903 AU               52

             1906 AU               53

             1142 AU               54

             1157 AU               55

 

 

看出问题了，在SQL语句中，我们希望看到连续10条记录。但是虽然替换了数据段范围，但是结果集却是相同的。

 

反映到页面上，就是按照ISO_CODE进行排序之后，分页功能失效，点击跳到下一页，但是显示的数据却没有变化。

 

2、分析和实验

 

看似很诡异的问题，难道是Hibernate语句方案有问题？替换为其他的排序字段，问题似乎不存在。

 

 

SQL> select *

  2    from (select a1.inputfile_log_seq, a1.ISO_CODE,rownum rwn

  3            from (select * from inputfile_log t order by t.inputfile_log_seq) a1

  4           where rownum <= 55)

  5   where rwn > 50;

 

INPUTFILE_LOG_SEQ ISO_CODE        RWN

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

              997 AU               51

              998 AU               52

              999 AU               53

             1000 AU               54

             1001 AU               55

 

SQL>

SQL> select *

  2    from (select a1.inputfile_log_seq, a1.ISO_CODE,rownum rwn

  3            from (select * from inputfile_log t order by t.inputfile_log_seq) a1

  4           where rownum <= 50)

  5   where rwn > 45;

 

INPUTFILE_LOG_SEQ ISO_CODE        RWN

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

              992 AU               46

              993 AU               47

              994 AU               48

              995 AU               49

              996 AU               50

 

 

排序结果正常。看来问题还是出在ISO_CODE上。

 

条条大路通罗马，换一种方法，试试结果如何呢？Oracle中还有很多其他的分页方案。

 

 

SQL> select *

  2    from (select a1.inputfile_log_seq, a1.ISO_CODE,rownum rwn

  3            from (select * from inputfile_log t order by t.ISO_CODE) a1)

  4   where rwn > 45

  5     and rwn <= 50;

 

INPUTFILE_LOG_SEQ ISO_CODE        RWN

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

             1316 AU               46

             1317 AU               47

             1318 AU               48

             1319 AU               49

             1323 AU               50

 

SQL>

SQL> select *

  2    from (select a1.inputfile_log_seq, a1.ISO_CODE, rownum rwn

  3            from (select * from inputfile_log t order by t.ISO_CODE) a1)

  4   where rwn > 50

  5     and rwn <= 55;

 

INPUTFILE_LOG_SEQ ISO_CODE        RWN

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

             1324 AU               51

             1865 AU               52

             1624 AU               53

             1163 AU               54

             1173 AU               55

 

 

不采用问题SQL的两次截取方法，而是将所有的rownum都取出来实体化，再利用范围进行截取。这样做的结果也是正确的。

 

问题出在哪里呢？SQL本质上是一种描述性语句，只要我们正确描述，绝大多数情况是可以将正确的结果返回的。但是这个案例下，描述本身没有什么问题，而且错误出现是偶发性的，令人疑惑。

 

3、执行计划找线索

 

在所有的DBMS中，SQL语句并不能执行，都需要转化为实际运行的“程序”。这种自动编程的程序可视化结果，就是执行计划。我们猜想执行计划过程中可能有一些问题。

 

首先，我们检查一下能返回正确结果的SQL结构。以下采用autotrace进行分析。

 

 

 

SQL> select *

  2    from (select a1.inputfile_log_seq, a1.ISO_CODE,rownum rwn

  3            from (select * from inputfile_log t order by t.ISO_CODE) a1)

  4   where rwn > 45

  5     and rwn <= 50;

 

已用时间:  00: 00: 00.01

 

执行计划

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

Plan hash value: 3700799345

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

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

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

|   0 | SELECT STATEMENT    |      |  5598 |   158K|       |   365   (1)| 00:00:05 |

|*  1 |  VIEW                 |     |  5598 |   158K|       |   365   (1)| 00:00:05 |

|   2 |   COUNT             |    |       |       |       |       |          |

|   3 |    VIEW               |      |  5598 | 89568 |       |   365   (1)| 00:00:05 |

|   4 |     SORT ORDER BY     |    |  5598 |  1322K|  1800K|   365   (1)| 00:00:05 |

|   5 |      TABLE ACCESS FULL| INPUTFILE_LOG |  5598 |  1322K|       |    70   (2)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("RWN"<=50 AND "RWN">45)

 

 

 

上面的执行计划，表示Oracle首先（Step 4）将所有数据记录根据ISO_CODE进行全排序，最后（Step 1）根据rwn设定的范围进行数据筛选。这样的结构意味着Oracle的确是需要将所有数据进行全排序动作，最后返回结果。

 

那么，Hibernate生成的SQL语句，执行计划如何呢？

 

 

SQL>  select *

  2    from (select a1.inputfile_log_seq, a1.ISO_CODE, rownum rwn

  3            from (select * from inputfile_log t order by t.ISO_CODE) a1

  4           where rownum <= 55)

  5   where rwn > 50;

 

已用时间:  00: 00: 00.06

 

执行计划

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

Plan hash value: 4198067120

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

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

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

|   0 | SELECT STATEMENT   |       |    55 |  1595 |       | 365   (1)| 00:00:05 |

|*  1 |  VIEW               |       |    55 |  1595 |       | 365   (1)| 00:00:05 |

|*  2 |   COUNT STOPKEY  |      |       |       |       |          |          |

|   3 |    VIEW                  |     |  5598 | 89568 |       | 365   (1)| 00:00:05 |

|*  4 |     SORT ORDER BY STOPKEY| |  5598 |  1322K|  1800K| 365   (1)| 00:00:05 |

|   5 |      TABLE ACCESS FULL   | INPUTFILE_LOG |  5598 |  1322K|       |  70   (2)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("RWN">50)

   2 - filter(ROWNUM<=55)

   4 - filter(ROWNUM<=55)

 

 

注意，我们在此处看到了一个操作名称为“SORT ORDER BY STOPKEY”，我们知道STOPKEY是标准rownum伪列行为。STOPKEY的效果是“点到即指”，也就是数出符合条数的结果集合之后，立即结束操作。

 

第二个限制条件，体现在了结果集合的“COUNT STOPKEY”上。那么，我们遇到的问题是不是在于这个“SORT ORDER BY STOPKEY”呢？

 

冷静下来想，rownum与STOPKEY都有一个共同的特点，就是不会将结果集合全部查看。那么，我们存在一个猜想：Oracle是不是有信心可以确定出，已经找到了合适的结果呢？

 

问题出在ISO_CODE上，由于业务特点要求，ISO_CODE取值离散程度很低。

 

 

SQL>  select ISO_CODE, count(*) from inputfile_log group by ISO_CODE;

 

ISO_CODE   COUNT(*)

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

                293

SE              198

（篇幅原因，有省略……）

AU             2396

 

16 rows selected

 

 

如果按照ISO_CODE进行排列，会有连续很多页数都是取值AU。如果按照ISO_CODE单条件进行排列，那么顺序的差异体现在不同的ISO_CODE之间，而不是相同ISO_CODE取值之间。

 

简单的说，有10条ISO_CODE=‘AU’的记录。你和Oracle说列出前5条ISO_CODE=‘AU’的记录和列出后5条ISO_CODE=‘AU’的记录，Oracle是不承认这十条记录之间存在顺序差异关系。也就是说：我给你一个相对位置数量，因为没有差异。

 

当排序字段离散程度大，顺序差异大的时候，这种情况就不会出现。并且，如果每页的页数超过相同值范围，也不会出现问题。这就是为什么测试人员反映问题是偶发性出现的原因了。

 

退一步说，“SORT ORDER BY STOPKEY”操作是Oracle对于排序过程的一种优化。虽然笔者不能看到真实的源代码，也没有很多相关的资料。可以猜想到这种方式的排序数目应该是小于单纯排列的。

 

4、解决方案

 

那么，怎么办呢？

 

笔者感觉，问题应该从多个方面思考，从多个方面解决。首先是需求，用户真的存在按照这个离散程度如此之低的字段进行排序吗？而且，从业务上看，是不是每次集中在一个ISO_CODE上进行处理，也就是使用筛选功能的几率更高呢？所以，首先应该争取取消对这个字段的排序要求。最强大的优化领域就是业务方面的优化，最好的数据库调优就是不让数据库做这个事情。这里还要考虑对现有架构解决方案的冲击。

 

如果不能从业务上免于使用这个，建议在架构层面为每次排序的时候，加上数据表主键列，提高差异离散度。

 

 

 

SQL> select *

  2    from (select a1.inputfile_log_seq, a1.ISO_CODE,rownum rwn

  3            from (select * from inputfile_log t order by t.ISO_CODE, t.inputfile_log_seq) a1

  4           where rownum <= 50)

  5   where rwn > 45;

 

INPUTFILE_LOG_SEQ ISO_CODE        RWN

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

              992 AU               46

              993 AU               47

              994 AU               48

              995 AU               49

              996 AU               50

 

SQL>

SQL> select *

  2    from (select a1.inputfile_log_seq, a1.ISO_CODE, rownum rwn

  3            from (select * from inputfile_log t order by t.ISO_CODE, t.inputfile_log_seq) a1

  4           where rownum <= 55)

  5   where rwn > 50;

 

INPUTFILE_LOG_SEQ ISO_CODE        RWN

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

              997 AU               51

              998 AU               52

              999 AU               53

             1000 AU               54

             1001 AU               55

 

 

问题解决。这个方法的缺点在于需要修改架构代码，加入一些判断逻辑和处理逻辑。但是毕竟这个Bug是有解的。

 

5、结论

 

在Oracle中，rownum是一种使用方便的工具函数列、伪列，很多业务都可以通过这个方法快速解决。但是，一定要注意这个列并不是真实存在的数据列，它的使用是有很强的特点的。

 

在使用rownum的过程中，一定要谨慎谨慎再谨慎，多测试、多实验。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-765436/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17203031/viewspace-765436/

ciqu9915
关注
Oracle学习总结（8）—— 面向程序员的数据库访问性能优化法则
科技D人生
05-25 1万+
特别说明： 1、  本文只是面对数据库应用开发的程序员，不适合专业DBA，DBA在数据库性能优化方面需要了解更多的知识； 2、  本文许多示例及概念是基于Oracle数据库描述，对于其它关系型数据库也可以参考，但许多观点不适合于KV数据库或内存数据库或者是基于SSD技术的数据库； 3、  本文未深入数据库优化中最核心的执行计划分析技术。   读者对像： 开发人员：如果你是做数据库开发，
一到两年工作经验的看完这些面试轻松拿offer
NNCC1022的博客
12-28 819
java经常遇到的基础面试题
Oracle rownum rowid 分页等问题
从基础到源码解析的学习记录
11-03 603
1、rownum和rowid的区别 rowid物理编号，插入库时候顺序，不迁库，编号不会变 SELECT e.JOB ,e.DEPTNO ,rowid FROM EMP e rownum逻辑编号，先有结果集，再给结果集编号 2、rownum问题 1）rownum = 1获取第一条，rownum = 2为啥等于空？ 因为rownum从1开始编号，获取一条，编号为1没问题。rownum =2本意想获取第二条，同样也是一条数据，rownum先获取结果集再编号，则第二条数据rownum又变成了1，以此
Rownum 解决排名问题，分页问题
weixin_30535167的博客
08-07 622
Rownum用法2013-09-09 一、rownum 　　伪列。它和数据不是一一对应的。但是又可以通过它进行排序的。或者获取某个wornum对应的数据。 　　在同一个单独的查询语句中是固定的，如子查询是一个查询，父查询又是一个查询。 　　如果只是一个单独的查询，非嵌套查询，rownum是根据插入的顺序编号的。从1开始。 　　如果根据rownum过滤数据，rownum必须从1开始，...
oracle11c分页,oracle11gr2用rownum在分页时执行计划出现异常,无结果集返回？
weixin_28753647的博客
04-12 215
是rownum的存在影响了结果集,如果没有rownum执行计划就没有null is not null;没有rownum的情况下执行计划是:Plan hash value: 3022366153------------------------------------------------------------------------------------------------| Id | ...
oracle11g分页优化,rowid和rownum的性能比较，解决越往后数据查询越慢的问题
热门推荐
u012572955的专栏
11-02 1万+
先看看数据量，100万+的数据: 再看看使用传统rownum分页的情况，没有使用排序，因为排序在大数据量下会造成全表扫描的情况! oracle11g使用模糊查询%%,查询优化器一般情况下会选择使用索引，极端情况没有测试！这里在查询第一页的数据的时候耗时0.172秒！各列索引都已使用! 使用rowid查询第一页的数据: 查询第一页使用了0.031秒，其实差不多，如果求
ORACLE 分页效率比较
ASD1415926的专栏
10-16 732
举例：查询表mdl_indicator中20000-20100之间的数据 一、rownum 二、row_number 三、row_number 四、rowid 总结：第二种方式性能最高
java基础面试题-看我这一篇就够了
dreamJavaer的博客
05-14 300
基础题 一、String,StringBuffer, StringBuilder 的区别是什么？String为什么是不可变的？ String是字符串常量，StringBuffer和StringBuilder是字符串变量。 StringBuffer是线程安全的， StringBuilder是非线程安全的。 具体来说String是一个不可变的对象(String类是由final修饰的类，俗称阉割类)，...
java架构师常见的基础面试题及答案（一）
Java架构师面试网
09-03 1684
一、String,StringBuffer, StringBuilder 的区别是什么？String为什么是不可变的？ 1. String是字符串常量，StringBuffer和StringBuilder是字符串变量。StringBuffer是线程安全的，StringBuilder是非线程安全的。具体来说String是一个不可变的对象，每次修改String对象实际上是创新新对象，并将引用指向新对象。效率很低。StringBuffer 是可变的，即每次修改只是针对其本身，大部分情况下比String效率高，St
数据库（面试向）
Olivia_BETTER的博客
06-11 905
目录一、四大特性二、隔离级别1、数据库的并发问题2、四个隔离级别3、悲观锁和乐观锁三、查询语句中的执行顺序四、视图&临时表&子查询1、视图2、临时表3、子查询五、主从复制1、原理2、策略3、搭建4、优势六、mysql、DB2和Oracle的区别1、平台和性能2、database、user、schema3、分页查询4、处理临时表5、主键6、字符串的引号7、提交事务8、并发性9、日期转换10、空串七、存储过程八、索引1、优缺点2、需要建索引的情况3、不建议建索引的情况4、索引有哪几类5、索引的数据结构有哪些九、
Oracle数据库中调用ROWNUM分页失败
lxsz0214的博客
08-05 935
在Oracle数据库中进行分页操作，发现一些情况取不到数据的情况，例如： -- 已知t_test表中有70余条数据，现在对表中数据进行查询操作。 -- 下面情况查询结果为空 SELECT * FROM T_TEST WHERE ROWNUM > 5 AND ROWNUM <= 10 -- 下面情况查询结果为空 SELECT * FROM T_TEST WHERE ROWNUM ...
oracle ROWNUM分页出现重复数据
最新发布
小码蚁
11-17 2009
Oracle分页数据重复；Oracle分页踩坑
oracle 使用rownum进行分页的陷阱
大鹏_James的专栏
05-14 4893
同事遇到一个奇怪的问题，在使用下面分页导记录时发现分页后的记录跟总的记录数匹配（总的记录数大概25000），但是有些记录不存在,有些重复：   SELECT *   FROM (SELECT row_.*, rownum rn           FROM (select --省略几百行                  order by sdate, mname, blevel asc
oracle使用rownum分页问题（rownum使用>,>=,!=出现查不到结果或者奇怪的结果）
qq279788的博客
05-14 2771
oracle使用伪列rownum分页SELECT * FROM( SELECT * FROM emp ORDER BY sal DESC) WHERE Rownum=5结果查不到结果，同样ROWNUM&gt;=5查询5名之后的成绩也失败，因为ROWNUM是一个每一次都要从1开始排列的伪列，例如取第一条数据到缓冲区 rownum=1不符合条件，于是便扔掉这条数据，再取第二条数据的rownum还是为1...
Oracle ROWNUM用法和分页查询
love398146779的专栏
05-07 3308
最后简单总结一下ROWNUM，很多人都知道ROWNUM只适用于小于或小于等于，如果进行等于判断，那么只能等于1，不能进行大于的比较。但是却并不了解造成这种限制条件的机制是什么。 对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，且rownum不能以任何表的名称作为前缀。 其实ROWNUM...
Oracle的rownum和分页
李昆鹏的博客
06-06 1万+
--------------------------------------------Oracle的rownum和分页--------------------------------------------------RownumROWNUM:表示行号，实际上此是一个列,但是这个列是一个伪列,此列可以在每张表中出现。范例：查询emp表带有rownum列selectrownum, t.* from...
SQL分页排序的实现与分页数据重复问题——以Oracle rownum为例
huzgd的专栏
02-21 8553
<br />对于关系数据库来说，直接写SQL拉数据在列表中显示是很常用的做法。但如此便带来一个问题：当数据量大到一定程度时，系统内存迟早会耗光。另外，网络传输也是问题。如果有1000万条数据，用户想看最后一条，这时即便有足够的内存，在网络上传输这么多数据也得一两小时吧，恐怕没几个用户有这么耐心等。因此分页是必须的。<br /> <br />现在网上的论坛、博客什么的，基本上都会有分页功能，有些是SQL分页的，有些可能是NOSQL用其它方法分页，都有很成熟的东西了。本文根据我自己的经验，以ORACLE为例，讲
Oracle中rownum的分页问题
hyly_zhard的博客
11-21 373
问题描述 在Oracle数据库中，可以使用rownum进行分页。相信有朋友这样使用过rownum。 例如： 写法一： select * from emp where rownum&amp;amp;amp;lt;10；--我这个emp表没有rownum并没有这个物理字段，为什么可以使用rownum 写法二： select * t.rownum from emp t; --这里直接报错 写法三： select ...
Oracle ROWNUM 使用技巧与分页查询
在Oracle数据库中，ROWNUM是一个非常有用的伪列，它可以生成一个序列化的数字，用于标识查询结果中的每一行。这篇文章由资深DBA黄玮撰写，他在Oracle开发、设计和维护方面拥有丰富的经验。ROWNUM的特性使得它在实现...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值