oracle分页查询优化,解决Oracle分页查询中排序与效率问题

oracle分页查询优化,解决Oracle分页查询中排序与效率问题详细介绍

本文目录一览： oracle 高效分页查询SQL

?????因为一个功能需要读取一个大表的所有数据做业务处理，那这样肯定不能一次性查出所有数据，需要程序分页查询处理，模拟测试一个200万数据量的表发现耗时很久，并不是业务处理耗时，而且分页查询耗时了。oracle的分页查询可能大家都知道利用rownum,而且大部分公司这种分页都是底层封装好的了，所有平时大家使用的时候也没注意（这次之后特意留意了一下，我们公司就是用了错误的）

这两条查询语句看着区别不大，但是性能却差很多。经过测试第一种性能最好，而且随着数量的增大，几乎不受影响。第二种随着数据量的增大，查询速度也越来越慢。表200W条数据的情况下，第一种查询耗时基本是0.3s,第二种基本在1.3s以上。一个查询足足差了一秒。别小看这1秒。200W条数据每次查询1000条，查询完也差了2000s=33分钟.
分页的目的就是控制输出结果集大小，将结果尽快的返回。在上面的分页查询语句中，这种考虑主要体现在WHERE ROWNUM <= 20这句上。

这是由于CBO优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。
对于正确有order by语句，第二层的查询条件WHERE ROWNUM <= 20就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了
对于错误有order by 语句，由于查询条件where b.rowno >= 11 and b.rowno <= 20是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道b.rowno代表什么）。因此对于这个语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。

上面分析的查询不仅仅是针对单表的简单查询，对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。

Oracle 数据量非常大（上亿）时，使用存储过程中的游标返回分页查询的10条记录非常耗时，请问如何优化？

select * /*+ FIRST_ROWS */　from XXX where XXX
提高SQL语句的响应时间，快速的先返回 n 行。
SELECT /*+ FIRST_ROWS */ * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21

解决Oracle分页查询中排序与效率问题

　　原始未分页查询Sql代码如下
　　select ROWNUM rn t id ID o name YYB u name XM t MC from tZDYSX t tuser u lbanization o where t cjr=u id and id=code and t gx = order by ID

　　结果如下 RN ID YYB XM MC 某证券总部 管理员 测试 某证券总部 管理员 持有上港 股以上 某证券总部 管理员 十年规划 某证券总部 管理员 开发渠道为上海 某证券总部 管理员 万科A 某证券总部 管理员 某证券总部 管理员 今天过生日的客户 某证券总部 管理员 客户状态正常 某证券总部 管理员 无交易 某证券总部 管理员 OA 某证券总部 管理员 幸运客户 某证券总部 管理员 风险型 某证券总部 管理员 tst 白沙网上交易 安昌彪 安客户正常 某证券总部 管理员 某证券总部 管理员 客户号包含 某证券总部 管理员 aaa 某证券总部 管理员 ssssssss 某证券总部 管理员 某证券总部 管理员 某证券总部 管理员 某证券总部 管理员 aaaaaa
　　 rows selected

　　最初我使用如下Sql代码查询
　　select * from (select ROWNUM rn t id ID o name YYB u name XM t MC from tZDYSX t tuser u lbanization o where t cjr=u id and id=code and t gx = order by t ID )Where rn> and rn<= ;

　　这种方法能成功分页 结果如下 RN ID YYB XM MC 某证券总部 管理员 开发渠道为上海 某证券总部 管理员 万科A 某证券总部 管理员 某证券总部 管理员 今天过生日的客户 某证券总部 管理员 客户号包含 某证券总部 管理员 aaa 某证券总部 管理员 ssssssss 某证券总部 管理员 某证券总部 管理员 某证券总部 管理员 aaaaaa
　　 rows selected
　　从结果看来 有个问题 此语句Sql代码 order by CJSJ DESC 被执行 但是是在分后的第 到 条记录的结果集中再进行排序 而不是先排序后分页 （本来希望显示ID为 到 结果变为 到 ）
　　后来变为以下Sql代码查询
　　SELECT * FROM( SELECT ROWNUM RN TA * FROM( select t id ID o name YYB u name XM t MC from tZDYSX t tuser u lbanization o where t cjr=u id and id=code and t gx = order by t ID )TA WHERE ROWNUM <= )WHERE RN > SELECT * FROM( SELECT ROWNUM RN TA * FROM(select t id ID o name YYB u name XM t MCfrom tZDYSX t tuser u lbanization o where t cjr=u id and id=code and t gx = order by t ID)TA WHERE ROWNUM <= )WHERE RN >

　　结果如下 RN ID YYB XM MC 某证券总部 管理员 幸运客户 某证券总部 管理员 风险型 某证券总部 管理员 tst 白沙网上交易 安昌彪 安客户正常 某证券总部 管理员 某证券总部 管理员 客户号包含 某证券总部 管理员 aaa 某证券总部 管理员 ssssssss 某证券总部 管理员 某证券总部 管理员
　　 rows selected
　　看来结果是正确的
　　总结 第二种方法其中最内层的查询Sql代码
　　
　　select t id ID o name YYB u name XM t MC from tZDYSX t tuser u lbanization o where t cjr=u id and id=code and t gx = order by t ID

　　表示不进行翻页的原始查询语句 ROWNUM <= 和RN > 控制分页查询的每页的范围 第二种方法在大多数情况拥有较高的效率 分页的目的就是控制输出结果集大小 在上面的分页查询语句中 这种考虑主要体现在WHERE ROWNUM <= 这句上
　　选择第 到 条记录存在两种方法 第二种方法正是在查询的第二层通过ROWNUM <= 来控制最大值 在查询的最外层控制最小值 而第一种方法是去掉查询第二层的WHERE ROWNUM <= 语句 在查询的最外层控制分页的最小值和最大值
　　一般来说 第二个查询的效率比第一个高得多 这是由于CBO 优化模式下 Oracle可以将外层的查询条件推到内层查询中 以提高内层查询的执行效率 对于第二个查询语句 第 层的查询条件WHERE ROWNUM <= 就可以被Oracle推入到内层查询中 这Oracle查询的结果一旦超过了ROWNUM限制条件 就终止查询将结果返回了
　　而第一个查询语句 由于查询条件Where rn> and rn<= 是存在于查询的第三层 而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义 因为最内层查询不知道RN代表什么） 因此 对于第一个查询语句 Oracle最内层返回给中间层的是所有满足条件的数据 而中间层返回给最外层的也是所有数据 数据的过滤在最外层完成 显然这个效率要比第二个查询低得多
lishixinzhi/Article/program/Oracle/201311/17706

oracle怎么实现分页

因为Oracle数据库没有Top关键字，所以这里就不能够像微软的数据据那样操作，这里有两种方法：
一种是利用相反的。PAGESIZE：每页显示的记录数CURRENTPAGE：当前页号数据表的名字是：components索引主键字是：idselect * from components where id not in(select id from components where rownum<=(PAGESIZE*(CURRENTPAGE-1))) and rownum<=PAGESIZE order by id;如下例：select * from components where id not in(select id from components where rownum<=100) and rownum<=10 order by id;从101到记录开始选择，选择前面10条。
使用minus，即中文的意思就是减去，呵呵，这语句非常的有意思，也非常好记select * from components where rownum<=(PAGESIZE*(CURRENTPAGE-1)) minus select * from components where rownum<=(PAGESIZE*(CURRENTPAGE-2));如例：select * from components where rownum<=10 minus select * from
一种是利用Oracle的rownum，这个是Oracle查询自动返回的序号，一般不显示，但是可以通过select rownum from [表名]，可以看到，是从1到当前的记录总数。select * from (select rownum tid,components.* from components where rownum<=100) where tid<=10;

oracle分页详解（rownum与orderby）

　　Oracle的分页是通过rownum实现的
　　rownum是一个伪列 是oracle系统自动为查询返回结果的每行分配的编号 第一行为 第二行为 以此类推
　　一个oracle分页 至少要包含三层（除非不用order by 暂时可以用 层实现） 模板为
　　select temp * from(
　　select rownum num temp * from(

　　SQL查询
　　) temp where rownum<=n
　　)temp where temp num>n
　　例如 值返回查询结果第 条到 条著 条的信息的SQL如下
　　select temp *
　　from(
　　select rownum num temp *
　　from(
　　select tt title_id tt name
　　from t_title tt
　　where tt name like %美%
　　order by tt sort_seqs asc tt title_Id desc) temp
　　where rownum<=
　　)temp
　　where temp num>
　　分析
　　 首先是一个正常的查询语句（包含order by）
　　select tt title_id tt name
　　from t_title tt
　　where tt name like %美%
　　order by tt sort_seqs asc tt title_Id desc
　　这个和正常的SQL语句没有任何的区别
　　 添加rownum字段 显示列数
　　select rownum num temp *
　　from(
　　select tt title_id tt name
　　from t_title tt
　　where tt name like %美%
　　order by tt sort_seqs asc tt title_Id desc) temp
　　where rownum<=
　　我们添加了rownum 显示字段 这时候就会会每行添加一个行数的编号 并且只返回 条之前的数据（包含 条）
　　 截取第 条到 条的数据 SQL就是上面最完整的那个啦
　　使用精解
　　 rownum的使用
　　如下两条语句
　　select rownum id name from student where rownum> ;
　　select rownum id name from student where rownum<= ;
　　第一条语句的执行结果为空 第二条语句的执行结果为前 条记录
　　为什么会这样呢 我们知道rownum是伪列 是oracle为查询结果自动添加的伪列 第一行是 如果where rownum> 这时候查找第一条发现它的rownum= 不满足条件 于是抛弃掉 把第二条语句的rownum赋值为 再判断第二条记录是否满足条件 同样不满足 于是发生了死循环一样的判断 最终返回空
　　有人这时候就奇怪啦 为什么第一条记录rownum= 不满足条件时候 第二条记录rownum= 却要重新设值为 呢 非常简单 你直接在where后添加了条件rownum> 它是个条件啦 第一条记录不满足条件 叫抛弃掉啦 这时候结果集是空的 当然会一直rownum= 的赋值
　　解决办法:先查询 并为每条记录分配rownum 然后嵌套查询
　　select t * from (select rownum num id name from student) t where t num>
　　第二条语句可以正常的执行 根据上面的解释 这个可以理解了吧！
　　 rownum与order by同时存在的问题
　　当 where 后面有rownum的判断 并且存在order by时候 rownum的优先级高！
　　oracle会先执行rownum的判断 然后从结果中order by 很明显是错误的结果啦！就好像学校要取成绩最好的前 名同学 结果这种方法一执行 成了取出 名同学 然后按照成绩的高低排序！
　　这点与SQL Server的TOP完全不同 TOP遇上order by 是先执行order by 在分页的
lishixinzhi/Article/program/Oracle/201311/17827

java中分页查询oracle数据速度慢，怎么解决好

查询速度慢
1。sql复杂，多表格关联。无优化。
2。通信阻碍，网络慢
3。oracle内部查询慢，无索引。
4。查询数据量大，pga小。
你可以试一下，简单的表，简单的查询，速度会不会变化，
或则同样的表，简单的查询等。可以确定问题发生在哪个环节，再寻求解决方法。
同样的sql语句，直接放到oracle内部执行。速度如何。

Oracle的极大数据量的分页查询问题

1.把星都换成需要的字段名试一下。
2.索引顺序排列正确（这个你查一下，索引不是建 了就可以。查询时有顺序的，四年前的项目，改变顺序后，时间由35s 提升到6-8s，具体的记不清了，只记得有这么回事。）
回去以后试一下你的SQL，只有数据多才出现这个问题吗？字段长度大约都多少？
相同条件在第一次查询出结果立即再次进行查询时
你第二次查询你查出来的结果集是去全表遍历查的 结果集是没有就是一推数据 上面没有索引没有任何东西，建议能先处理逻辑关系减少数据量 orderby 这些能不用就不用 like也会让index失效 用between可以代替
没法优化。查询条件中用了like子句，索引会不起作用，造成遍历整张表。
如果没有like及order by子句的话，会快很多
你这样做分页是全部查询后分页吗？为何不做成分页后查询？因为你只写了语句示例具体情况不是很了解。我们曾经做过一次查询后分页会使得页面刷新很慢，但调整后就相对快很多了。

oracle怎么实现多表 连接查询 并分页。。。

多表连接最好一次从数据库中取出，在前台分页，否则每次做连接很耗费资源的。
从技术上你的写法也是对的：
select * from (select rownum as r,u.userid,u.userid,u.loginName,t.content from userinfo u,twitter t where u.userid=t.userid and loginname like '%java%')t where rownum<= currentPage*pageSize and rownum<= （currentPage+1）*pageSize
你试下。
select * from (select rownum as r,u.userid,u.userid,u.loginName,t.content from userinfo u,twitter t where u.userid=t.userid and loginname like '%java%' and rownum <4 )t where r > 2;
oracle使用rownum伪列可以实现分页，三表连接分页示例代码如下：
select * from (select rownum r,k.kch,k.kcm,cj.cj,x.xh,x.xm from KCB k,CJB cj,XSB x where k.kch = cj.kch and cj.xh = x.xh and rownum<=10) where r>0特别注意这样外层查询时由于内层查询的字段有重复列名，所以内层查询最后不要用*。取完每一个表字段，这样很容易报错（“无效字段”）

大数据量实时统计排序分页查询 优化总结

大数据量实时统计排序分页查询 (并发数较小时) 的瓶颈不是函数（count，sum等）执行，

不是having, 也不是order by，甚至不是表join, 导致慢的原因就在于“数据量太大本身”

就是将表划分为M份相互独立的部分,可以是分表，也可以是不分表但冗余一个取模结果字段

实际结果是不分表比分表更加灵活，只需稍加配置，就可以动态切分大表，随意更改M的大小。

将1条慢sql（大于30秒）拆分成为N条查询速度巨快的sql(单条sql执行时间控制在20毫秒以内)

然后再web应用中以适当的线程数去并发查询这些执行时间快的N条小sql再汇总结果

第一步查询中去并发执行这N条小sql, 只取排序字段和标识字段，其他字段一律丢弃

汇总结果后定位出当前页面要显示的pageNum条数据，再进行第二步查询，取出页面上需要展示的所有字段

PS：这一点是至关重要的，其他几点都可以不看，这点是最关键的。慢慢解释一下：

a) 第一种方式是把数据库中所有记录（只取排序字段和标识字段并且不做任何sum，count having order by等操作）

全部拉到web应用中，在web应用中完成所有的计算

b) 第二种方式是把数据库中所有记录做sum count having等操作之后的所有行数拉到web应用中，在web应用中完成剩余计算

c) 第三种方式是把数据库中所有记录做sum count having order by等操作之后把limit后的数据拉到web应用中，

在web应用中对limit后的数据再计算

显然，第一种方式 数据库什么活都不做只取数据 是不可行的。以lg_order_count_seller为例，1500万行，

如果只算id, seller_id和order_count 这三个bigint类型，至少需要拉8*3*1500 0000 = 360000000=340M,

拉到内存中之后存储需要8*4*15000000= 460M,这还不算List是的2的n次方这个特点和计算排序等的内存开销，

不仅数据库与web应用机器IO扛不住，就是应用自身恐怕也要OOM了。

第二种方式，所有记录做sum count having等操作之后,由于是group by seller_id的，总得数据量变为100万（就是卖家总数），

这样子一来，共需要拉8*3*100 0000 = 23M,拉到内存之后，需要8*4*100 0000 = 30M, 再算上List是的2的n次方这个特点和

计算排序等的内存开销也不会超过100M, IO的时间和内存开销勉强可以考虑接受。

第三种方式，所有记录做sum count having order by等操作之后把limit后的数据拉到web应用中，因为做了limit，所以，

数据量很小了，无论是IO还是内存开销都已经很小了。可以忽略。

综合以上三种，第三种方式适用于页面的前n页和后n页，因为这个limit的数据量随着页数的增大而增大，

当大到每个切分后的小表的数据量时就转为第二种方式了。

第二种方式适用于页面的第[n+1, totaoPageNum-n]页。

切分成N条小sql后并行执行时排序不稳定性的解决办法

① 问题描述：

优化之前，还是是一条大慢sql查询时，由于数据库排序是稳定排序，

所以当两条记录排序字段值相同时他们在页面上的页码位置是固定的。

优化之后，当并行执行这N条小sql时，由于无法控制这些小sql的先后执行顺序，

导致在web应用中当两条记录的排序字段值相同时在页面上的页码位置是随机的。

② 解决办法：

除了拉标识字段(seller_id)和排序字段(order_count_sum)之外，再取一个unique（id）的字段，当两条记录的排序字段值相同时，再用这个unique的字段（在卖家监控中这个字段是id）进行第二次排序.这样就解决了排序不稳定的问题。

③ 也许，看到这里会有疑问，为什么不用seller_id？seller_id也是唯一， 这样子不是少取id这个字段，减少IO了？

seller_id虽然也是唯一，可以辅助排序，但是不要忘记数据库的排序规则是：

如果两列的值相等，那么序号在前的排在前面，这里的序号就是主键(自动生成，autoincrement),

如果用seller_id的话还是不能保证排序的稳定性，只能用主键id.

优先加载页面上的主要元素，然后再去异步加载次要元素，

反应在卖家监控页面中，查数据和查页页码的sql语句基本相同，是在竞争同一资源，

所以，需要做一个策略，优先把资源让给查数，数据查完之后再去查页码。

限流

由于多线程取数据并没有从本质上提高数据库性能，所以必须针对大数据量实时统计排序分页查询做限流

我这里打个比方：食堂有6个窗口，物流团队吃饭要买6个菜，平均每买1个菜需要1分钟的时间，

如果派我一个人去一个窗口买的话需要6分钟的时间

假如派6个人分别去6个窗口买这6个菜，只需要1分钟的时间

但是，如果除了物流团队，再来其他5个团队呢，也就是说6个团队每个团队买6个菜共买36个菜，

这样子有的团队先买完，有的团队后买完，但平均时间还是6分钟。本质上没有变化。

所以，对于特定的查询条件，必须进行限流。让每分钟至多有6个团队买菜，这样子能使得情况变得不至于太糟糕。

从根本上改变现状

这一点从目前来看只能是展望了，比如mysql数据库换更为强大的oracle数据库，

或更换InnoDb引擎为其他，或更换SATA硬盘为SSD 。。。。。。

从实践效果来看，优化后的效果是很明显的。

相同的查询条件，原来一个页面查询时间由于超过60秒超时了，根据1-6点建议优化之后，查询时间变为2秒至3.5秒之间。

oracle分页查询慢，该怎么处理

看你的分页语法是这么写的？
推荐这么写：
SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (select count(*) over() DATACNT,ta.* from ta)) A WHERE ROWNUM <= 100) WHERE RN >= 75