6楼说的情况有道理, 可是我的这里只返回一条数据,以下是我的其中一段代码: insert into t_total select '5' as item_group, v_temp_chart_group as chart_group, 2 as item_kind, 'I' as item_type, min(d.dict_prompt) as item_name, to_char(count(*)) as item_value from facco a, dict d where (trim(p_en_branch_no) is null or instr(',' || p_en_branch_no || ',', ',' || to_char(a.branch_no) || ',') > 0) and (trim(p_en_client_group) is null or instr(',' || p_en_client_group|| ',', ',' ||to_char(a.client_group)|| ',') > 0 ) and (trim(p_en_room_code) is null or instr(',' || p_en_room_code || ',',',' || to_char(a.room_code) || ',') > 0) and (trim(p_en_asset_prop) is null or instr(p_en_asset_prop, a.asset_prop) > 0 ) and (trim(p_en_risk_level) is null or instr(',' || p_en_risk_level || ',',',' || to_char(a.risk_level) || ',') > 0) and (trim(p_en_corp_client_group) is null or instr(',' || p_en_corp_client_group|| ',', ',' ||to_char(a.corp_client_group)|| ',') > 0 ) and (trim(p_en_corp_risk_level) is null or instr(',' || p_en_corp_risk_level|| ',', ',' ||to_char(a.corp_risk_level)|| ',') > 0 ) and (trim(p_en_asset_level) is null or instr(p_en_asset_level, a.asset_level) > 0)/**/ and d.dict_entry = 1201 and instr(a.en_entrust_way, d.subentry) > 0 group by d.subentry;其中t_total 是临时表; facco 表在where 条件中用到的字段上都没有建索引,有20000条数据
如果是的话,你用plsql developer等工具运行sql时,只会返回前面若干条记录,而不会全部返回,这样,就显得速度比较快。
而你调用存储过程则需要返回全部记录,速度就会较慢。
楼主可以说一下你的sql语句的功能以及返回记录数等信息。以便大家进一步分析。
select '5' as item_group,
v_temp_chart_group as chart_group,
2 as item_kind,
'I' as item_type,
min(d.dict_prompt) as item_name,
to_char(count(*)) as item_value
from facco a, dict d
where (trim(p_en_branch_no) is null or instr(',' || p_en_branch_no || ',', ',' || to_char(a.branch_no) || ',') > 0)
and (trim(p_en_client_group) is null or instr(',' || p_en_client_group|| ',', ',' ||to_char(a.client_group)|| ',') > 0 )
and (trim(p_en_room_code) is null or instr(',' || p_en_room_code || ',',',' || to_char(a.room_code) || ',') > 0)
and (trim(p_en_asset_prop) is null or instr(p_en_asset_prop, a.asset_prop) > 0 )
and (trim(p_en_risk_level) is null or instr(',' || p_en_risk_level || ',',',' || to_char(a.risk_level) || ',') > 0)
and (trim(p_en_corp_client_group) is null or instr(',' || p_en_corp_client_group|| ',', ',' ||to_char(a.corp_client_group)|| ',') > 0 )
and (trim(p_en_corp_risk_level) is null or instr(',' || p_en_corp_risk_level|| ',', ',' ||to_char(a.corp_risk_level)|| ',') > 0 )
and (trim(p_en_asset_level) is null or instr(p_en_asset_level, a.asset_level) > 0)/**/
and d.dict_entry = 1201
and instr(a.en_entrust_way, d.subentry) > 0
group by d.subentry;其中t_total 是临时表; facco 表在where 条件中用到的字段上都没有建索引,有20000条数据
3.9s 是怎么统计的? 是整个存储过程的执行时间吧?
回13楼: 临时表里面的数据量不大,25条