SQL & PL/SQL

Hi ,

I have a stored procedure which has Select/Insert operation which is looped within the cursor. The Select uses variables from the cursor, and inserts the data into the table.
My problem is the peformance. The stored procedure executes in 2 mins if I hard code the where clause values in the Select. Instead if I use the variables obtained from the cursor, the query takes so long (more than 20 mins) to insert the same no of records into the table.

Below is the snippet of my code

create procedure sample is

declare

v_acct_month number(2);
v_sp_name varchar2(30);
cursor v_cur is Select distinct Acct_Month,salesperson_name from period;

begin

open v_cur;
loop
fetch v_cur into v_acct_month, v_sp_name;
exit when v_cur%notfound;

Insert into T1
Select * from T2,T3.. Where T2.month=v_acct_month,t3.sp_name=v_sp_name;

end loop;

End;


The query is not using the optimizer when the variables are used.The optimizer is used when I hard code the values in the select.

Please give me suggestions to make this query faster..


Thanks,
Arun

Edited by: arun_thesaga@yahoo.co.in on Mar 28, 2009 10:18 AM