关于Oracle数据库分页查询的小技巧
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了关于Oracle数据库分页查询的小技巧，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5464字，纯文字阅读大概需要8分钟。
内容图文
对于Oracle中的相关优化，有许多常用的方法，像大家都知道的如：select *不提倡使用，效率极差，建议显式获取列，即使是所有字段也应罗列。而取总数时使用count(*),为提高cache的命中率，尽量做到SQL重用。另外，提高性能的好选择可能就是把所有的字符数据都
对于Oracle中的相关优化，有许多常用的方法，像大家都知道的如：select *不提倡使用，效率极差，建议显式获取列，即使是所有字段也应罗列。而取总数时使用count(*),为提高cache的命中率，尽量做到SQL重用。另外，提高性能的好选择可能就是把所有的字符数据都保存为Unicode，Java以Unicode形式处理所有数据，因此，数据库驱动程序不必再执行转换过程。
而这篇文章我们主要来了解下关于用SQL来实现分页的很多种实现方式，有些语句可能并不是很通用，只能用在一些特殊场景之中，而一些虽然功能强大、使用方便，带来的缺陷就是效率会慢得掉渣，接下来是IT男经过亲身的开发经验，分别讲解下这些常用的sql查询语句的作用，但愿对你有帮助！
以下介绍三种比较通用的实现方案；在以下各种实现中，ROWNUM是一个最核心的关键词，在查询时他是一个虚拟的列，取值为从1到记录总数的序号。
1、首先来介绍我们工作中最常使用的一种实现方式：
SELECT *
FROM (SELECT IT_NAN_.*, ROWNUM R_
FROM (SELECT *
FROM IT_NAN T
ORDER BY T.IT_CAT) IT_NAN_
WHERE ROWNUM <= 50000)
WHERE R_ >= 49991;
其中最内层的查询SELECT为不进行翻页的原始查询语句，可以用自己的任意Select SQL替换；ROWNUM <= 50000 和ROWNUM >= 49991控制分页查询的每页的范围。分页的目的就是控制输出结果集大小，将结果尽快的返回；上面的SQL语句在大多数情况拥有较高的效率，主要体现在WHERE ROWNUM <= 50000这句上，这样就控制了查询过程中的最大记录数。（经过本IT男测试百万级别的数据，以上sql测试的时间大约徘徊在2.5s上下，并随着50000这个数值的变大，时间略微变长。）
2、上面例子中展示的在查询的第二层通过ROWNUM <= 50000来控制最大值，在查询的最外层控制最小值。而另一种方式是去掉查询第二层的WHERE ROWNUM <= 50000语句，在查询的最外层控制分页的最小值和最大值。此时SQL语句如下，也就是要介绍的第二种实现方式：
SELECT *
FROM (SELECT IT_NAN_.*, ROWNUM R_
FROM (SELECT *
FROM IT_NAN T
ORDER BY T.IT_CAT) IT_NAN_ )
WHERE R_ BETWEEN 49991 AND 50000;
由于Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率，但不能跨越多层。
对于第一个查询语句，第二层的查询条件WHERE ROWNUM <= 50000就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。
而第二个查询语句，由于查询条件BETWEEN 49991 AND 50000 是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道ROWNUM代表什么）。因此，对于第二个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。（这种查询语句经过本IT男测试和以上相同的数据时，测试的时间大约为第一种查询时间的两倍，并也随着50000这个数值的变大，时间略微增长。）
3、以上两种方案完全是通过ROWNUM来完成，下面一种则采用ROWID和ROWNUM相结合的方式，SQL语句如下：
SELECT *
FROM (SELECT RID
FROM (SELECT IT_NAN_1.RID, ROWNUM R_
FROM (SELECT T.ROWID RID
FROM IT_NAN T
ORDER BY T.IT_CAT) IT_NAN_
WHERE ROWNUM <= 50000)
WHERE R_ >= 49991) IT_NAN_1_ , IT_NAN IT_NAN_2_
WHERE IT_NAN_1_.RID = IT_NAN IT_NAN_2_.ROWID ;
从语句上看，共有嵌套查询了四层Select，最内层为可替换的不分页原始SQL语句，但是他查询的字段只有ROWID，而没有任何待查询的实际表字段，具体查询实际字段值是在最外层实现的。
这种方式的原理大致为：首先通过ROWNUM查询到分页之后的10条实际返回记录的ROWID，最后通过ROWID将最终返回字段值查询出来并返回；
和前面两种实现方式相比，该SQL的实现方式更加繁琐，通用性也不是非常好，因为要将原始的查询语句分成两部分（查询字段在最外层，表及其查询条件在最内层）；
但这种实现在特定场景下还是有优势的：比如我们经常要翻页到很后面，比如100000条记录中我们经常需要查90000-91000及其以后的数据；此时该方案效率可能要比前面的高；
因为前面的方案中是通过ROWNUM <= 91000来控制的，这样就需要查询出91000条数据，然后取最后90000-91000之间的数据，而这个方案直接通过ROWID取需要的那100条数据；
从不断向后翻页这个角度来看，第一种实现方案的成本会越来越高，基本上是线性增长，而第三种方案的成本则不会像前者那样快速，他的增长只体现在通过查询条件读取ROWID的部分；（这种查询语句IT男同样进行了测试，测试的时间大约为第一种查询时间的一半，并也随着50000这个数值的变大，体现出的效果会更可观。而后我又测试了查看页数为1-10的查询速度，结果发现这个查询方法，仍旧比第一种要快将近一半…不知道具体原因，不过事实胜于雄辩啊…）
这个查询方法真的很给力，如果你们的项目中应用了hibernate的二级缓存对实体对象进行了缓存的话，那样的话，效率将会是更加的可观。
4、当然，除了以上提了这些方案，我们还可以用以下的SQL来实现：
SELECT * FROM IT_NAN WHERE ROWID NOT IN (SELECT ROWID FROM IT_NAN WHERE ROWNUM <= 49990) AND ROWNUM <= 10; SELECT * FROM IT_NAN WHERE ROWNUM <= 50000 MINUS SELECT * FROM IT_NAN WHERE ROWNUM <= 49990
以上两种查询语句IT男也测试了下，大概是8s左右。所以说如果项目数据量不大的话，可以尝试使用，如果百万或以上的数据，还是考虑上面的比较好些。
………………
注意：当ROWNUM作为查询条件时，他是在order by之前执行，所以要特别小心；
比如我们想查询IT_NAN中按IT_CAT倒序排列的前10条记录不能用如下的SQL来完成：
SELECT * FROM IT_NAN T WHERE ROWNUM <= 10 ORDER BY T.IT_CAT DESC;
当然对于这个需求，可以参考上面的例子那样的子查询来实现，也可以采用Oracle的排序函数：ROW_NUMBER()函数根据OrderID这列来生成一个新的数据列。当然对ROW_NUMBER感兴趣的可以请教下度娘或谷哥，这里就不再细说，效果是差不多。
关于数据的优化还有很多事情要做，要考虑，要研究，这也只是一点儿小技巧，有时间我会继续在博客中分享相关的经验，当然也希望有大虾们尽情指正、放荡的拍砖、一起探讨分享你的优化经验，共同成长！
原文地址：关于Oracle数据库分页查询的小技巧, 感谢原作者分享。
内容总结
以上是互联网集市为您收集整理的关于Oracle数据库分页查询的小技巧全部内容，希望文章能够帮你解决关于Oracle数据库分页查询的小技巧所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。