ITPub博客

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

原创 Oracle 作者:沃趣科技 时间:2018-05-25 12:06:36 0 删除 编辑

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

点击(此处)折叠或打开

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




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


表过滤返回数据量如下:

点击(此处)折叠或打开

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

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

下面我们建立REVERSE索引IDX_ID_TYPE_RE

点击(此处)折叠或打开

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

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



   下面我们改写sql如下

点击(此处)折叠或打开

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


执行计划中出现index_skip_scan。 

下面我们创建如下索引:

点击(此处)折叠或打开

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




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

| 作者简介

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

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28218939/viewspace-2155102/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
杭州沃趣科技股份有限公司创建于2012年(股票代码:839849),是一家专注为企业用户提供基于高性能、高可用、可扩展的开放数据库云平台解决方案的国产厂商。公司创始团队为原阿里巴巴数据库技术团队核心骨干,凭借丰富的研发及运维经验,为行业客户提供数据库云产品及软硬件一体化解决方案。

注册时间:2016-07-18

  • 博文量
    170
  • 访问量
    611028