t_task表中有13361条纪录,执行下面的语句需要数十秒,可否优化,求细解!select prj_id,ta_id
from t_task ta
where ta_flag =1 and
ta_appflag=0 and
--frebot(prj_id,fnmc_id)=0 and
not exists (select 'Y'
from t_task tab
where instr(','||ta.ta_prenode||',',','||tab.fnmc_no||',')>0 and
tab.ta_appflag =0 and
ta.prj_id = tab.prj_id
) and
ta.userro_id = 14
from t_task ta
where ta_flag =1 and
ta_appflag=0 and
--frebot(prj_id,fnmc_id)=0 and
not exists (select 'Y'
from t_task tab
where instr(','||ta.ta_prenode||',',','||tab.fnmc_no||',')>0 and
tab.ta_appflag =0 and
ta.prj_id = tab.prj_id
) and
ta.userro_id = 14
from t_task ta
where ta_flag =1 and
ta_appflag=0 and
not exists (select 'Y'
from t_task tab
where instr(','||ta.ta_prenode||',',','||tab.fnmc_no||',')>0 and
tab.ta_appflag =0 and
ta.prj_id = tab.prj_id
) and
ta.userro_id = 14
我不太懂啊,执行计划是什么呀,我门外汉!拜托!
instr(','||ta.ta_prenode||',',','||tab.fnmc_no||',')> 0 这个记录有多少?
有建INDEX吗?
select prj_id,ta_id
from t_task ta
where ta_flag =1 and
ta_appflag=0 and
ta.userro_id = 14
)
where not exists (select 'Y'
from t_task tab
where instr(','||ta.ta_prenode||',',','||tab.fnmc_no||',')>0 and
tab.ta_appflag =0 and
ta.prj_id = tab.prj_id
) ta_flag ta_appflag ta.userro_id 创建索引
select prj_id,ta_id
from t_task ta where not exists (select 'Y'
from t_task tab
where instr(','||ta.ta_prenode||',',','||tab.fnmc_no||',')>0)2762条,用24.156s
估计主要是这个破坏了INDEX,用的是全表扫描。
SQL> set autotrace traceonly /*traceonly 可以不显示执行结果*/
SQL> 语句...
SQL> /
Execution Plan
..
..
..