下面
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。
下面我们创建如下索引:
|
1
|
create
index
idx_date_seal_re
on
OOOO_XXXCHECKLOG(CHECKDATE,REVERSE(xxxtype));
|
可以看到，逻辑读降到64424，
50
个物理读是因为刚刚创建索引的原因，
sql
也秒出。
