这篇文章主要介绍SQL优化之如何使用索引,文中介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们一定要看完!下面sql30秒执行出结果,查看sql谓词中有like,我们知道谓词中有这样的语句是不走索引的(为了保护客户的隐私,表名和部分列已经重命名)。1234567891011121314SELECT
/*+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_XXXECHECKLOGA;
逻辑读600多万。查看索引情况如下表过滤返回数据量如下:123456SQL>
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_RE1234567891011121314SELECT
/*+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_XXXECHECKLOGA;
查看执行计划如下,逻辑读将为300万,但是时间还是维持在18秒,根本原因在于这个索引因为标量子查询的问题被访问700万次导致。下面我们改写sql如下12345678910111213SELECT
/*+
index
(OOOO_XXXCHECKLOGIDX_ID_TYPE_RE)3*/
CHECKNUM
AS
PINGZBSM,
CHECKDATE,
XXXMODE,
XXXRESULT,
B.RESULT,
CLERKNUM
AS
C免费主机域名HECKNUM
FROM
OOO_XXXECHECKLOGA
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。下面我们创建如下索引:1create
index
idx_date_seal_re
on
OOOO_XXXCHECKLOG(CHECKDATE,REVERSE(xxxtype));
可以看到,逻辑读降到64424,50个物理读是因为刚刚创建索引的原因,sql也秒出。以上是“SQL优化之如何使用索引”这篇文章的所有内容,感谢各位的阅读!希望分享的内容对大家有帮助,更多相关知识,欢迎关注云技术行业资讯频道!
相关推荐: 如何使用paramiko监控Oracle alert日志
小编给大家分享一下如何使用paramiko监控Oracle alert日志,相信大部分人都还不怎么了解,因此分享这篇文章给大家参考一下,希望大家阅读完这篇文章后大有收获,下面让我们一起去了解一下吧!环境设置Linux系统为 Centos 6.8Python环境…