1、问题
一个查询未走索引,从一个视图中查询,视图定义为5张表的union all。
有明显的条件TMH12 = '1001271876'
我有几张阿里云幸运券分享给你,用券购买或者升级阿里云相应产品会有特惠惊喜哦!把想要买的产品的幸运券都领走吧!快下手,马上就要抢光了。
2、排查
@?/rdbms/admin/sqltrpt 脚本生成优化建议
通过建议来看,是生成索引的建议,对列进行格式转换,TO_CHAR("TMH12"),
1- Index Finding (see explain plans section below)
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 99.48%)
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index HICANO_POS.IDX
20260001onHICANOPOS.ECPHPDA(TOCHAR("TMH12"));−ConsiderrunningtheAccessAdvisortoimprovethephysicalschemadesignorcreatingtherecommendedindex.createindexHICANOPOS.IDX
_20260002 on
HICANO_POS.E_CPXSDA(TO_CHAR("TMH12"));
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index HICANO_POS.IDX
20260003onHICANOPOS.EJLZCPCPXXMXB("TMH8");−ConsiderrunningtheAccessAdvisortoimprovethephysicalschemadesignorcreatingtherecommendedindex.createindexHICANOPOS.IDX
_20260004 on