关于分页查询的优化思路(r3笔记第7天)

目前在生产环境中有一个sql语句执行时间长达7分钟,而且执行频率极高。 其中PROC_INST中有将近6千万的数据。其中STEP_INST是一个物化视图,里面还有5千多条数据。 可以看到这个语句已经有了一些调优的痕迹,可以从里面的子查询和hint能够看出一些信息。 SELECT PROC_INST.OBJID, PROC_INST.CREATION_TIME FROM PROC_INST, (SELECT / * +leading(PROC_INST LIST table_bpm_step_inst) FULL(PROC_INST) hash_aj(LIST) use_nl(table_bpm_step_inst) * / PROC_INST.OBJID, PROC_INST.CREATION_TIME, ROW_NUMBER() OVER(ORDER BY creation_time) rn FROM PROC_INST, (SELECT / * +materialize FULL(in_step) PARALLEL(in_step, 8) * / in_step.root2proc_inst FROM STEP_INST in_step WHERE in_step.status NOT IN (?,) OR in_step.WAIT_TIME IS NOT NULL) LIST, STEP_INST WHERE STEP_INST.ROOT2PROC_INST = PROC_INST.OBJID AND PROC_INST.ROOT_STATUS = ? AND PROC_INST.STATUS = ? AND STEP_INST.OBJID = PROC_INST.BEGIN2STEP_INST AND STEP_INST.COMMITTER = ? AND STEP_INST.STATUS IN (?,) AND STEP_INST.WAIT_TIME IS NULL AND STEP_INST.ASSIGNEE = ? AND PROC_INST.OBJID = list.root2proc_inst(+) AND list.root2proc_inst IS NULL ORDER BY CREATION_TIME) INNER_QRY WHERE INNER_QRY.OBJID = PROC_INST.OBJID AND INNER_QRY.rn > ? AND INNER_QRY.rn < ? 上面的查询有几个地方需要注意 首先是分析函数row_number的使用。其实在大量的数据查询中直接使用rownum要高效一些,而且在子查询的结果又加了一层order by的排序操作,所以可以考虑去除row_number() 关于AND list.root2proc_inst IS NULL的查询条件,如果看得仔细一点就会发现,这个过滤条件完全可以放在子查询list里面,尽可能排除较多的数据。 子查询的输出结果集PROC_INST.OBJID, PROC_INST.CREATION_TIME,可以考虑直接使用rowid来代替对应的字段值,这样可能对于索引来说就可以是的索引的使用更加高效,如果是range scan就可以从某种程度上提升为fast scan. 最后的这个地方看似没有问题,其实是最需要做改进的地方。如果输出1000~2000行的数据,那么子查询就会先得查出2000条数据。 AND INNER_QRY.rn > ? AND INNER_QRY.rn < ? 如果输出100000~101000 这样的话,就得先得到101000行的数据,然后再排除过滤。这样的话每个查询的执行代价都会不同,可以考虑在回表的数据上进行一个统一的规划。 基本思路就是先在order by之后的子查询之后做一个rownum<? 然后只输出rowid。再上一层的子查询中继续拍段rownum>? 这样基于rowid的排除更加清晰。 改进之后的sql语句类似下面的样子。 SELECT PROC_INST.OBJID, PROC_INST.CREATION_TIME FROM PROC_INST where rid in ( (SELECT / * +leading(PROC_INST LIST table_bpm_step_inst) FULL(PROC_INST) hash_aj(LIST) use_nl(table_bpm_step_inst) * / rid FROM (select rowid rid,rownum rn from (select PROC_INST.rowid from PROC_INST, (SELECT / * +materialize FULL(in_step) PARALLEL(in_step, 8) * / in_step.root2proc_inst FROM STEP_INST in_step WHERE in_step.status NOT IN (?,) OR in_step.WAIT_TIME IS NOT NULL AND list.root2proc_inst IS NULL) LIST, STEP_INST WHERE STEP_INST.ROOT2PROC_INST = PROC_INST.OBJID AND PROC_INST.ROOT_STATUS = ? AND PROC_INST.STATUS = ? AND STEP_INST.OBJID = PROC_INST.BEGIN2STEP_INST AND STEP_INST.COMMITTER = ? AND STEP_INST.STATUS IN (?,) AND STEP_INST.WAIT_TIME IS NULL AND STEP_INST.ASSIGNEE = ? AND PROC_INST.OBJID = list.root2proc_inst(+) ORDER BY CREATION_TIME) INNER_QRY where rownum<?) AND INNER_QRY.rn >= ?) 最后优化的结果稍后奉上。

本文分享自微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2014-09-26

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Java学习123

ORACLE数据库日常维护

68180
来自专栏james大数据架构

你真的会玩SQL吗?和平大使 内连接、外连接

你真的会玩SQL吗?系列目录 你真的会玩SQL吗?之逻辑查询处理阶段 你真的会玩SQL吗?和平大使 内连接、外连接 你真的会玩SQL吗?三范式、数据完整性 你真...

19980
来自专栏ASP.NET MVC5 后台权限管理系统

ASP.NET MVC5+EF6+EasyUI 后台管理系统(41)-组织架构

本节开始我们要实现工作流,此工作流可以和之前的所有章节脱离关系,也可以紧密合并。 我们当初设计的项目解决方案就是可伸缩可以拆离,可共享的项目解决方案。所以我们同...

22570
来自专栏程序员的SOD蜜

SQLSERVER 2012计算上一条,下一条数据的函数

实际需求很普遍,比如求销售数据的每天与头一天的销售增长量。这里用一个汽车行驶数据来做例子: 先初始化数据: CREATE TABLE [dbo].[CarDat...

30550
来自专栏「3306 Pai」社区

不用MariaDB/Percona也能查看DDL的进度

使用MariaDB/Percona版本的一个便利之处就是可以及时查看DDL的进度,进而预估DDL耗时。 其实,在官方版本里也是可以查看DDL进度的,认真看手册的...

23800
来自专栏杨建荣的学习笔记

关于索引扫描的极速调优实战(第一篇) (r3笔记第81天)

一般在生产环境中,如果某个查询中涉及一个大表,走索引扫描是显然是最值得推荐的方式,但是索引扫描有unique index scan, range scan,sk...

25940
来自专栏杨建荣的学习笔记

通过执行计划中的CONCATENATION分析sql问题(r4笔记第16天)

昨天开发的一个同事找到我,说写了一条sql语句,但是执行了半个小时还没有执行完,想让我帮忙看看是怎么回事。 他大体上给我讲了下逻辑,表bl1_rc_rates是...

32340
来自专栏c#开发者

Paging of Large Resultsets in ASP.NET

The paging of a large database resultset in Web applications is a well known pro...

450110
来自专栏跟着阿笨一起玩NET

(1)显示每个类别最新更新的数据

在项目中经常遇到求每个类别最新显示的数据,比如显示某某某类别最新更新的5条数据。特写下这个sql记录于此:

9510
来自专栏ASP.NET MVC5 后台权限管理系统

ASP.NET MVC5+EF6+EasyUI 后台管理系统(42)-工作流设计-表建立

工作流在实际应用中还是比较广泛,网络中存在很多工作流的图形化插件,可以做到拉拽的工作流设计,非常简便,再配合第三方编辑器,可以直接生成表单,我没有刻意的浏览很多...

229100

扫码关注云+社区

领取腾讯云代金券