SQL优化案例-正确的使用索引(二)

下面sql 30秒执行出结果,查看sql谓词中有like,我们知道谓词中有这样的语句是不走索引的(为了保护客户的隐私,表名和部分列已经重命名)。

SELECT /*+1*/
CHECKNUM AS PINGZBSM,
CHECKDATE,
XXXMODE,
XXXRESULT,
(SELECT RESULT 
FROM (select ID,to_char(WMSYS.WM_CONCAT(xxxnum||xxxtype||xxxmode||xxxresult)) RESULT 
       from OOOO_XXXCHECKLOG 
      WHERE CHECKDATE BETWEEN DATE'2018-05-04' AND DATE'2018-05-04' and xxxtype like '%PAR'
      GROUP BY ID
    ) b where b.id=a.id
) RESULT,
CLERKNUM AS CHECKNUM
FROM OOO_XXXECHECKLOG A;

逻辑读600多万。查看索引情况如下

表过滤返回数据量如下:

SQL> select count(*) from OOOO_XXXCHECKLOG;
2799616
select count(*) from OOOO_XXXCHECKLOG WHERE CHECKDATE BETWEEN DATE'2018-05-04' AND DATE'2018-05-04' and xxxtype like '%PAR';
 12856
select count(*) from OOOO_XXXCHECKLOG WHERE CHECKDATE BETWEEN DATE'2018-05-04' AND DATE'2018-05-04';
197984

通过查询上面返回数据可知,因为xxxtype不走索引,所以通过索引要回表197984次,如果走了索引只回表12856次。

下面我们建立REVERSE索引IDX_ID_TYPE_RE

SELECT /*+OOOO_XXXCHECKLOG index(IDX_ID_TYPE_RE) 2*/
CHECKNUM AS PINGZBSM,
CHECKDATE,
XXXMODE,
XXXRESULT,
(SELECT RESULT 
FROM (select ID,to_char(WMSYS.WM_CONCAT(xxxnum||xxxtype||xxxmode||xxxresult)) RESULT 
       from OOOO_XXXCHECKLOG 
      WHERE CHECKDATE BETWEEN DATE'2018-05-04' AND DATE'2018-05-04' and REVERSE(xxxtype) like 'RAP%'
      GROUP BY ID
    ) b where b.id=a.id
) RESULT,
CLERKNUM AS CHECKNUM
FROM OOO_XXXECHECKLOG A;

查看执行计划如下,逻辑读将为300万,但是时间还是维持在18秒,根本原因在于这个索引因为标量子查询的问题被访问700万次导致。

下面我们改写sql如下

SELECT /*+ index(OOOO_XXXCHECKLOG IDX_ID_TYPE_RE) 3*/
CHECKNUM AS PINGZBSM,
CHECKDATE,
XXXMODE,
XXXRESULT,
B.RESULT,
CLERKNUM AS CHECKNUM
FROM OOO_XXXECHECKLOG A
left join (select ID,to_char(WMSYS.WM_CONCAT(xxxnum||xxxtype||xxxmode||xxxresult)) RESULT 
       from OOOO_XXXCHECKLOG 
      WHERE CHECKDATE BETWEEN DATE'2018-05-04' AND DATE'2018-05-04' and REVERSE(xxxtype) like 'RAP%'
      GROUP BY ID
    ) b on b.id=a.id;

执行计划中出现index_skip_scan。 

下面我们创建如下索引:

create index idx_date_seal_re on OOOO_XXXCHECKLOG(CHECKDATE,REVERSE(xxxtype));

可以看到,逻辑读降到64424,50个物理读是因为刚刚创建索引的原因,sql也秒出。

|  作者简介

姚崇·沃趣科技高级数据库技术专家

熟悉Oracle数据库内部机制,丰富的数据库及RAC集群层故障诊断、性能调优、OWI、数据库备份恢复及迁移经验。

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

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

生产环境大型sql语句调优实战第一篇(二) (r2笔记32天)

继续昨天的部分,上一篇的链接为: http://blog.itpub.net/23718752/viewspace-1217012/ 对这条大sql的性能瓶颈进...

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

MySQL表连接优化的初步分析

每每一些很深刻的优化案例时,就会无比想念Oracle里的优化技巧,因为无论是从工具还是信息,都会丰富许多。

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

merge语句导致的CPU使用率过高的优化(二) (r7笔记第9天)

之前分享过一篇关于merge语句导致的CPU使用率过高优化的案例。http://blog.itpub.net/23718752/viewspace-181947...

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

sql语句的简化(r2第7天)

今天碰到一个sql语句简化的问题,虽然也不复杂,但是也值得从中学习一些东西 SELECT MOD(((SELECT TO_NUMBER(TO_CHAR(LOG...

2676
来自专栏圣杰的专栏

Oracle:WITH AS () Merge ?

WITH AS 语法在SQL SERVER 和ORACLE数据库上均支持,主要用于子查询。语法如下: WITH expression_name [ ( colu...

3184
来自专栏芋道源码1024

电商系统设计之订单

1. 前言2. 付款2.1 成功2.2 人祸2.4 天灾2.4 注释2.5 表结构2.5.1 交易表2.5.2 支付记录表2.5.3 订单表3. 运输4. 收货...

1343
来自专栏「3306 Pai」社区

NOT NULL列用IS NULL也能查到数据?

有没有觉得很奇怪,为什么查到了2条 dt 列值为 '0000-00-00 00:00:00' 的记录?

880
来自专栏Java帮帮-微信公众号-技术文章全总结

Oracle应用实战七——多表查询+PL/SQL

1 多表查询 内连接 使用一张以上的表做查询就是多表查询 语法: SELECT {DISTINCT} *|列名.. FROM 表名 别名,表名1 别名 {WH...

5654
来自专栏me的随笔

T-SQL基础(五)之增删改

在前面的文章中对T-SQL的查询做了基本总结,接下来我们看下SQL中的另外一个常用操作——数据的修改。

792
来自专栏数据和云

实战演练:通过伪列、虚拟列实现SQL优化

本文是技术同仁 蔡亮 在日常工作中通过试验,总结出的一些技巧方案,供大家参考学习。在此,感谢蔡亮的供稿分享,希望大家也可以后续将学习工作中遇到的问题,解决方法分...

1093

扫码关注云+社区