ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Rownum分页故障解决一例

Rownum分页故障解决一例

原创 Linux操作系统 作者:realkid4 时间:2013-07-03 22:49:35 0 删除 编辑

 

在目前很多系统中,界面数据分页(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”呢?

 

冷静下来想,rownumSTOPKEY都有一个共同的特点,就是不会将结果集合全部查看。那么,我们存在一个猜想: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取值之间。

 

简单的说,有10ISO_CODE=AU’的记录。你和Oracle说列出前5ISO_CODE=AU’的记录和列出后5ISO_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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7639758