OracleClient之诡异现象
对于SQL语句
select nvl(to_char(floor(avg((apfndtm-apdldtm)*24*3600))),0) from emflib.eqapypf
where apydat >= 20080902 and apydat <= 20080902 and apcfmfg ='C' and apapyfg='A' and apytim >= 090000 and apytim <= 173000 AND apdeal >= 0 and apntg3 = 0 and apctg4='FANGXN'
如果使用参数化的
速度慢
select nvl(to_char(floor(avg((apfndtm-apdldtm)*24*3600))),0) from emflib.eqapypf
where apydat >= :apydat1 and apydat <= :apydat2 and apcfmfg =:apcfmfg and apapyfg=:apapyfg and apytim >= :apytim1
and apytim <= :apytim2 AND apdeal >= :apdeal and apntg3 = :apntg3 and apctg4=:apctg4
如下这条，少了一个条件，速度倒还可以
select nvl(to_char(floor(avg((apfndtm-apdldtm)*24*3600))),0) from emflib.eqapypf
where apydat >= :apydat1 and apydat <= :apydat2 and apcfmfg =:apcfmfg and apapyfg=:apapyfg and apytim >= :apytim1
and apytim <= :apytim2 AND apdeal >= :apdeal and apntg3 = :apntg3
如果把SQL改成
select nvl(to_char(floor(avg((apfndtm-apdldtm)*24*3600))),0) from emflib.eqapypf
where apydat >= :apydat1 and apydat <= :apydat2 and apcfmfg =:apcfmfg and apapyfg=:apapyfg and apytim >= :apytim1
and apytim <= :apytim2 AND apdeal >= :apdeal and apntg3 = :apntg3 and apctg4='FANGXN'
那么速度也是暴快，真是汗，有些搞不明白为何如此。数据分布
apctg4 count(*)
1 HUANGJF 106
2 ZHANGYJ 117
3 LINLIJU 2
4 WEISQ 53
5 ZHANGDC 28
6 FANGXN 217
7 LDWANGL 166
8 LINYZ 166
9 SHIXY 184
10 SHAONH 163
11 LIYY 160
12 LDCHENWF 9
根据经验估计，虽然是全部的SQL参数，但是SQL访问计划可能因为数据的分布或者SQL的统计信息的不及时，倒是ORACLE使用全表扫描，而使用直接SQL则可以使其又归回正确的轨道。不过看来，额滴ORACLE功底还是不够深，如果有人晓得，烦请告知原因，实在是没有精力挖那么深啊
select nvl(to_char(floor(avg((apfndtm-apdldtm)*24*3600))),0) from emflib.eqapypf
where apydat >= 20080902 and apydat <= 20080902 and apcfmfg ='C' and apapyfg='A' and apytim >= 090000 and apytim <= 173000 AND apdeal >= 0 and apntg3 = 0 and apctg4='FANGXN'
如果使用参数化的
速度慢
select nvl(to_char(floor(avg((apfndtm-apdldtm)*24*3600))),0) from emflib.eqapypf
where apydat >= :apydat1 and apydat <= :apydat2 and apcfmfg =:apcfmfg and apapyfg=:apapyfg and apytim >= :apytim1
and apytim <= :apytim2 AND apdeal >= :apdeal and apntg3 = :apntg3 and apctg4=:apctg4
如下这条，少了一个条件，速度倒还可以
select nvl(to_char(floor(avg((apfndtm-apdldtm)*24*3600))),0) from emflib.eqapypf
where apydat >= :apydat1 and apydat <= :apydat2 and apcfmfg =:apcfmfg and apapyfg=:apapyfg and apytim >= :apytim1
and apytim <= :apytim2 AND apdeal >= :apdeal and apntg3 = :apntg3
如果把SQL改成
select nvl(to_char(floor(avg((apfndtm-apdldtm)*24*3600))),0) from emflib.eqapypf
where apydat >= :apydat1 and apydat <= :apydat2 and apcfmfg =:apcfmfg and apapyfg=:apapyfg and apytim >= :apytim1
and apytim <= :apytim2 AND apdeal >= :apdeal and apntg3 = :apntg3 and apctg4='FANGXN'
那么速度也是暴快，真是汗，有些搞不明白为何如此。数据分布
apctg4 count(*)
1 HUANGJF 106
2 ZHANGYJ 117
3 LINLIJU 2
4 WEISQ 53
5 ZHANGDC 28
6 FANGXN 217
7 LDWANGL 166
8 LINYZ 166
9 SHIXY 184
10 SHAONH 163
11 LIYY 160
12 LDCHENWF 9
根据经验估计，虽然是全部的SQL参数，但是SQL访问计划可能因为数据的分布或者SQL的统计信息的不及时，倒是ORACLE使用全表扫描，而使用直接SQL则可以使其又归回正确的轨道。不过看来，额滴ORACLE功底还是不够深，如果有人晓得，烦请告知原因，实在是没有精力挖那么深啊
