公司开发同事发来一条语句,反应执行很慢,原语句如下:
SELECT COUNT(df.origrec)* it.scale AS pcount,
it.invcode AS invcode,
df.samplefrom AS rasclientid
FROM da_finance_pricesett df,
dbs_db_invbtest it
WHERE df.testcode = it.testcode
AND TO_CHAR(df.senddate,'yyyy-mm-dd') > '2013-02-25'
AND TO_CHAR(df.senddate,'yyyy-mm-dd') <= '2013-02-25'
AND it.invcode IN
(SELECT DISTINCT invcode FROM dbs_db_invbtest
)
GROUP BY it.scale,
it.invcode,
df.samplefrom;
存在的问题:
1、条件子句条件含函数引起的隐性转换导致全表扫描;
2、条件子句的嵌套条件存在无条件过滤导致全表扫描,而且从连接条件逻辑看,该子句与第一次等值连接是重复的。修改如下:
SELECT COUNT(df.origrec)* it.scale AS pcount,
it.invcode AS invcode,
df.samplefrom AS rasclientid
FROM da_finance_pricesett df,
dbs_db_invbtest it
WHERE df.testcode = it.testcode
AND df.senddate >to_date('2013-01-25','yyyy-mm-dd')
AND df.senddate <=to_date('2013-02-25','yyyy-mm-dd')
GROUP BY it.scale,
it.invcode,
df.samplefrom;