记一次Oracle数据库慢查询SQL优化

1、先决分析提供的SQL语句，相应表及列已做脱敏处理

select 
u.settleyear||u.settlemonth||u.settleday,
u.systrano,
u.terstan,
u.transmsnmonth||u.transmsnday||u.transmsnhout||u.transmsnminute||u.transmsnsecond,
u.priacctno,
u.transat/100,
u.TERMID,
u.mchntcd,
u.CARDACCPTRNMLOC,
u.expand2,
tra.EaAreaCode,
tra.eaareaname,
tra.EaHouseAccountCode,
tra.BUILDACCOUNTCODE,
tra.EAAREAADDRESS,
tra.EAHOUSEADDRESS,
tra.BUILDACCOUNTNAME,
tra.OWNERNAME
from table1 u 
left join 
(select * from (select t.dt,t.eaareacode,t.eaareaname,t.EaAreaAccountName,t.EaHouseAccountCode,t.noticecode,t.BUILDACCOUNTCODE,t.EAAREAADDRESS,t.EAHOUSEADDRESS,t.BUILDACCOUNTNAME,t.OWNERNAME,row_number() over(partition by noticecode order by dt desc) r
from tra.recv t where t.rescode=0) where r=1
) tra on u.expand2=tra.noticecode
where u.mchntcd in('897493990015','897441390018','89744139019')
and u.settleyear||u.settlemonth||u.settleday BETWEEN '20190801' and '20190831'
order by u.settleyear||u.settlemonth||u.settleday;

关联表查询的一个多条件查询语句，存在in条件查询，询问到此处是动态的，当in参数过多时，可能会导致全表搜索

2、查看表数据量

左表’table1’数据量：1,589,359
右表’recv’数据量：266,136

3、执行计划分析一下

发现两张表都是FULL，顺势赶紧查看两张表的索引建立情况，额...，果然两张表都未建立索引，询问后暂不做索引。

附：

索引建立规则：#####

1、表的主键、外键必须有索引；

2、数据量超过300的表应该有索引；

3、经常与其他表进行连接的表，在连接字段上应该建立索引；

4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；

5、索引应该建在选择性高的字段上；

6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；

7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

  A、正确选择复合索引中的主列字段，一般是选择性较好的字段；

  B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；

  C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；

  D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；

  E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；

8、频繁进行数据操作的表，不要建立太多的索引；

9、删除无用的索引，避免对执行计划造成负面影响；