某客户数据库性能调优续

经过对部分SQL进行调优之后，目前系统主要受瓶颈于以下类型的SQL
SELECT COUNT(:"SYS_B_00") FROM
(select distinct t.zch, t.zs, t.fddbr, t.qymc, t.year, t.corpid as corpid, t.njhzzt as njhzztid, t.qylxdl, nvl(t.optimizeflag, :"SYS_B_01")
optimizeflag, t.fristsubmit as fristsubmit, t.jbrscrq, t.jbrscqz, t.czscqz, y.name as qylx, djjg.content as djjg, gxdw.content as gxdw,
njbm.content as njbm, decode(t.njba, :"SYS_B_02", :"SYS_B_03", :"SYS_B_04", :"SYS_B_05") njba
from (select year, zs, zch, fddbr, qylx, qymc, corpid, njhzzt, qylxdl, optimizeflag, fristsubmit,
jbrscrq, jbrscqz, czscqz, djjg, gxdw, njbm, njba from NJ_DJZGJGSH where qylxdl not between :"SYS_B_06" and :"SYS_B_07"
and njhzzt in (:"SYS_B_08", :"SYS_B_09", :"SYS_B_10") and year=:"SYS_B_11" and zch like :"SYS_B_12" and substr(nvl(fhdq, njbm),
:"SYS_B_13", :"SYS_B_14")=:"SYS_B_15" and nvl(fhdq, gxdw)=:"SYS_B_16" ) t , eptype y, bm_djjg djjg, bm_gxdw gxdw, bm_djjg njbm
where t.qylx=y.interfaceid(+) and t.djjg=djjg.code(+) and t.gxdw=gxdw.code(+) and t.njbm=njbm.code(+) )
此类型SQL中，子查询对NJ_DJZGJGSH表格的执行效率尤为糟糕。
select year, zs, zch, fddbr, qylx, qymc, corpid, njhzzt, qylxdl, optimizeflag, fristsubmit,
jbrscrq, jbrscqz, czscqz, djjg, gxdw, njbm, njba
from NJ_DJZGJGSH
where qylxdl not between :"SYS_B_06" and :"SYS_B_07"
and njhzzt in
(:"SYS_B_08", :"SYS_B_09", :"SYS_B_10") and year=:"SYS_B_11"
and zch like :"SYS_B_12" and substr(nvl(fhdq, njbm),
:"SYS_B_13", :"SYS_B_14")=:"SYS_B_15" and nvl(fhdq, gxdw)=:"SYS_B_16"
其执行计划为为全表扫描，其COST高达2510
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
8   (1)| 00:05:02 |

|   8 |         TABLE ACCESS FULL          | NJ_DJZGJGSH  |     1 |   164 | 2510
7   (1)| 00:05:02 |

建议：
对此类SQL语句在SELECT中进行函数转换，在没有建立函数索引的前提下，执行效率将会很糟糕。
建议如下：
1、 在insert时进行函数转换。
2、 根据修改后的SQL，必要时考虑建联合索引。