Loading [MathJax]/jax/output/CommonHTML/config.js
首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >使用 Order by 与 rownum SQL 优化案例一则

使用 Order by 与 rownum SQL 优化案例一则

作者头像
老虎刘
发布于 2022-06-22 09:30:44
发布于 2022-06-22 09:30:44
4920
举报

这是某客户AWR报告中的一个TOP SQL,执行时间8.8小时(也有执行12.7小时报错退出的情况)

SELECT *

FROM

(

SELECT rownum num ,t.*

FROM TI_BI_CAMPN_USERORDER_TEMP t

WHERE oper_type = '0' OR oper_type = '2' OR

(oper_type = '1' AND end_date <= add_months(trunc(sysdate, 'mm'), 1))

ORDER BY oper_time ASC

) c

WHERE c.num <= : 1;

表记录数280M,返回其中符合条件并按oper_time排序的500条记录。

看到上面sql monitor显示的执行计划,可能有人会生出疑问:

返回了表的大部分记录,为什么不做全表扫描,而是使用了比较差的索引扫描?

答案是:

因为修改了索引相关的两个系统参数,把索引的COST大大的降低了,让优化器认为走索引总是比全表扫描要好:

optimizer_index_caching = 95(默认值0)

optimizer_index_cost_adj = 3(默认值100)

这个SQL如何优化呢?

根据SQL monitor显示的信息及表的实际记录数,我们可以通过创建oper_time字段上的索引来优化:

create index idx_name on TI_BI_CAMPN_USERORDER_TEMP(oper_time);

预计创建索引后,该SQL的执行时间应该在1~2秒左右

小结:

order by 与 rownum 搭配使用时,可以创建谓词字段与order by字段上的联合索引(本例不可,因为有or和不对称的谓词条件);或者在大部分数据都符合条件的情况下,创建order by字段上的索引(本例),避免大结果集的排序。

系统默认参数一般不建议修改(内存参数、bug除外),因为很多性能测试是在默认参数条件下完成。

其他情况:

如果这个SQL执行频率低,也可以选择不创建索引,使用并行加全表扫描来提高响应速度,使用下面的hint:

SELECT *

FROM

(

SELECT /*+ full(t) parallel(4) */rownum num ,t.*

FROM TI_BI_CAMPN_USERORDER_TEMP t

WHERE oper_type = '0' OR oper_type = '2' OR

(oper_type = '1' AND end_date <= add_months(trunc(sysdate, 'mm'), 1))

ORDER BY oper_time ASC

) c

WHERE c.num <= : 1;

如果索引那两个系统参数是默认值,其中的full hint是可以去掉的。

当前使用的并行度是4,具体的并行度可以根据实际需要适当增减。

注意:11g的并行写法已经不要求加表名或别名。10g中加表名或别名的写法繁琐而且容易遗漏,抛弃了吧!

各位网友如果有什么意见、建议、问题都可以与老虎刘沟通。

老虎刘的文章都是原创,欢迎大家转发。

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2016-07-21,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 老虎刘谈oracle性能优化 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
Oracle的使用
oracle数据库中是没有limit关键字的,想在Oracle数据库中实现limit功能可以通过使用rownum来限制结果集行数。
栖西
2023/10/17
5420
45 个非常有用的 Oracle 查询语句
这里我们介绍的是 40+ 个非常有用的 Oracle 查询语句,主要涵盖了日期操作,获取服务器信息,获取执行状态,计算数据库大小等等方面的查询。这些是所有 Oracle 开发者都必备的技能,所以快快收藏吧!
用户7705674
2021/09/23
7670
高薪面试题之三.DB必备
40+ 个非常有用的 Oracle 查询语句,主要涵盖了日期操作,获取服务器信息,获取执行状态,计算数据库大小等等方面的查询。这些是所有 Oracle 开发者都必备的技能,所以快快收藏吧!
张哥编程
2024/12/17
1490
Oracle到高斯数据库的SQL语法迁移手册(建议收藏)
异构数据库的迁移(譬如从Oracle迁移到openGauss)工作主要包括三个方面,
PawSQL
2024/08/20
8800
Oracle到高斯数据库的SQL语法迁移手册(建议收藏)
【DB笔试面试570】在Oracle中,SQL优化在写法上有哪些常用的方法?
一般在书写SQL时需要注意哪些问题,如何书写可以提高查询的效率呢?可以从以下几个方面去考虑:
AiDBA宝典
2019/09/29
3.8K0
rownum应用系列之分页查询--续1
创建col_1和col_2字段上的联合索引,避免排序,提高效率。联合索引的字段顺序不能错,order by的字段要放在联合索引的最后。如果是order by desc,正常情况优化器会自动使用index descending扫描方式,不需要建索引的时候加desc 。也有sql复杂的时候优化器没有使用index descending扫描方式,则可以用index_desc来纠正。
老虎刘
2022/06/22
3450
Oracle到PostgreSQL数据库的语法迁移手册(建议收藏)
异构数据库的迁移(譬如从Oracle迁移到PostgreSQL)工作主要包括三个方面,
PawSQL
2024/08/20
5460
Oracle到PostgreSQL数据库的语法迁移手册(建议收藏)
109-特定场景深度分页SQL优化技巧
注:本文在oracle 19c版本下测试,其他数据库的写法差不多,也可以借鉴这个思路。
老虎刘
2023/09/14
7150
109-特定场景深度分页SQL优化技巧
基于DB time的调优分析 (r6笔记第79天)
继昨天使用DB time能够快速灵活的定位sql语句之后,发现分析问题更快捷,高效了。今天就牛刀小试,把一个数据库从500%的负载调到不到100%的负载。前提是确实有可提升可改进的空间。 首先查看了在快照57611的时间段里DB time很快,也收到了zabbix的邮件通知, ZABBIX-监控系统: ------------------------------------ 报警内容: DB time is too high ------------------------------------ 报警
jeanron100
2018/03/16
7740
基于DB time的调优分析 (r6笔记第79天)
Oracle迁移MySQL 8特殊SQL处理 顶
我们创建一个表,并生成两个表分区CUS_PART1,CUS_PART2.关于分区的分类可以参考https://www.cnblogs.com/wnlja/p/3979684.html
算法之名
2020/05/18
1.2K0
Oracle迁移MySQL 8特殊SQL处理
                                                                            顶
关于Oracle Job定时任务配置讲解
几天前,公司的job调度出现了问题,由于权限管的严,没有查看Oracle 一些重要的数据字典,后面联系DBA,是由于数据库切换到备机时,参数设置不对,导致db job没有正常调度。
星哥玩云
2022/08/17
2.5K0
关于Oracle Job定时任务配置讲解
优化一个rownum=1的sql
,t1.TemplateID AS templateId ,t1.parameter AS parameter
老虎刘
2022/06/22
5990
优化一个rownum=1的sql
Oracle DBA的SQL编写技能提升宝典(含SQL资源)
背景:要迁移数据库,需要创建与源库相同的表空间,大小与源库相同。由于个别表空间较大,手工添加可能需要写很多的脚本,于是同事通过PL/SQL解决了问题。
数据和云
2021/10/13
1.2K0
Oracle DBA的SQL编写技能提升宝典(含SQL资源)
SQL优化
例:select *from tempagreement where rownum<10;
一觉睡到小时候
2019/07/02
9890
Oracle sql 性能优化(三)
携手创作,共同成长!这是我参与「掘金日新计划 · 8 月更文挑战」的第16天,点击查看活动详情 >>
默默的成长
2022/11/02
6690
Oracle sql 性能优化(三)
常见Oracle HINT的用法
Hint概述 基于代价的优化器是很聪明的,在绝大多数情况下它会选择正确的优化器,减轻了DBA的负担。但有时它也聪明反被聪明误,选择了很差的执行计划,使某个语句的执行变得奇慢无比。
jack.yang
2025/04/05
1520
Oracle知识点总结(一)
这里用1,也是为了方便,当然如果数据量较大的话,也可以提高速度,因为写count(*)的话会所有列扫描,这里用1的话或者用字段名的话,只扫描你写的那个列其实1就代表你这个查询的表里的第一个字段
用户1093975
2018/08/02
2.4K0
Oracle优化06-Hint
在Oracle 10g中,CBO 可选的运行模式有2种: (1) FIRST_ROWS(n) (2) ALL_ROWS – 10g中的默认值
小小工匠
2021/08/16
1.3K0
笔记分享(1) oracle常用查询语句
以下的oracle常用查询笔记是我之前工作中用到过的. 其实常用的查询差不多就是这些.
大大刺猬
2021/04/01
1.3K0
TiDB SQL调优案例之避免TiFlash帮倒忙
早上收到某系统的告警tidb节点挂掉无法访问,情况十万火急。登录中控机查了一下display信息,4个TiDB、Prometheus、Grafana全挂了,某台机器hang死无法连接,经过快速重启后集群恢复,经排查后是昨天上线的某个SQL导致频繁OOM。
HOHO
2023/03/31
6610
TiDB SQL调优案例之避免TiFlash帮倒忙
相关推荐
Oracle的使用
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档