下面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。

下面我们创建如下索引:

点击(此处)折叠或打开

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

| 作者简介

ITPub博客

SQL优化案例-正确的使用索引（二）