在一个Oracle存储过程中使用游标获取一批数据,然后针对每一条数据,拼写动态SQL并利用execute immediate执行,结果发现每次执行需要的时间在2-12秒之间,但是如果把这条SQL拿出来单独执行则仅需要0.00x秒,请问Oracle存储过程中动态SQL的使用真的能能导致千倍的效率降低吗?谢谢!
按照这种打开游标的方式模拟了一个40多万条数据的表,导入到一个结构相同的空表,每条数据用0.00x秒,打开游标用了0.4秒,跑完40万条数据用了700秒。但是我们是500条数据,时间大概是1000秒,崩溃了!
把in 改成 exist的效果也不好。求了,小弟没什么分,但还是求谢了。create or replace procedure
proc_udms_out_dataload
(
--输入参数
-- out_nn out number,
IN_curr_code in string , --币种
IN_data_date in string ,--数据日期
IN_branch_nbr in string --机构代码
)
begin open c_cm_load ;--打开游标
loop
--逐行获取数据
FETCH c_cm_load INTO V_table_seq,V_Item_Code,V_Source_Table,V_Source_Line,V_Load_type,V_Cond_String,V_Sys_ID;
EXIT WHEN c_cm_load%NOTFOUND;
sql_statement:=sql_statement
||'insert into UDMS_05_DATA select '''
||V_table_seq||''','''||V_Item_Code||''',b.branch_nbr,'''||V_currency_code||''', d.item_data_type_match,'''||V_data_Date||''', 100*sum(a.'||V_Source_Line||')
from '||V_Source_Table||' a, gt_udms_sep_branch b, udms_99_other_system_branch c, udms_load_item_data_type_match d '
||'where a.L13 in (select cm_item_code from vt_udms_cm_items) and a.data_date='''||V_data_Date||'''
and a.L12='''||V_cm_currency||'''
and c.system_id='''||V_Sys_ID||'''
and a.L11=c.other_branch_nbr
and b.branch_nbr=c.branch_nbr
and '''||V_data_Date||'''>=c.start_date and c.end_date>'''||V_data_Date||'''
and ( '||V_Cond_String||' )
and d.item_data_type_match='''||V_Load_type||'''
and a.L107=d.source_item_data_type
group by b.branch_nbr,d.item_data_type_match
';
EXECUTE IMMEDIATE sql_statement; end loop;
close c_cm_load ;--关闭游标
commit;
end proc_udms_out_dataload;
按照这种打开游标的方式模拟了一个40多万条数据的表,导入到一个结构相同的空表,每条数据用0.00x秒,打开游标用了0.4秒,跑完40万条数据用了700秒。但是我们是500条数据,时间大概是1000秒,崩溃了!
把in 改成 exist的效果也不好。求了,小弟没什么分,但还是求谢了。create or replace procedure
proc_udms_out_dataload
(
--输入参数
-- out_nn out number,
IN_curr_code in string , --币种
IN_data_date in string ,--数据日期
IN_branch_nbr in string --机构代码
)
begin open c_cm_load ;--打开游标
loop
--逐行获取数据
FETCH c_cm_load INTO V_table_seq,V_Item_Code,V_Source_Table,V_Source_Line,V_Load_type,V_Cond_String,V_Sys_ID;
EXIT WHEN c_cm_load%NOTFOUND;
sql_statement:=sql_statement
||'insert into UDMS_05_DATA select '''
||V_table_seq||''','''||V_Item_Code||''',b.branch_nbr,'''||V_currency_code||''', d.item_data_type_match,'''||V_data_Date||''', 100*sum(a.'||V_Source_Line||')
from '||V_Source_Table||' a, gt_udms_sep_branch b, udms_99_other_system_branch c, udms_load_item_data_type_match d '
||'where a.L13 in (select cm_item_code from vt_udms_cm_items) and a.data_date='''||V_data_Date||'''
and a.L12='''||V_cm_currency||'''
and c.system_id='''||V_Sys_ID||'''
and a.L11=c.other_branch_nbr
and b.branch_nbr=c.branch_nbr
and '''||V_data_Date||'''>=c.start_date and c.end_date>'''||V_data_Date||'''
and ( '||V_Cond_String||' )
and d.item_data_type_match='''||V_Load_type||'''
and a.L107=d.source_item_data_type
group by b.branch_nbr,d.item_data_type_match
';
EXECUTE IMMEDIATE sql_statement; end loop;
close c_cm_load ;--关闭游标
commit;
end proc_udms_out_dataload;
为什么不去掉?
1)用 BULK COLLECT INTO V LIMIT N (N为自然数) --目的是减少提取的时间,好比硬盘缓存
2)结合结合forall --目的减少和sql引擎的交互时间。
3)不要用dynamic sql,看不出你的语句中有用那个的必要。先这样看看。