Hi Tom,

I am working on logic to execute 1M records but internally for each 200k records have different types of conditions and table joins.This whole logic should be executed in less than 5 mins. Is there a way to join plsql table with db tables. Elaborate the code please.

My requirement is : source table has 1m records => taget table with 1M with few updated new values

string with param=>200k records(BULK INSERT) into plsql table => process each record data and update a column A in plsql table=> BULK INSERT into TARGET TABLE
-- again same process repeats with different param and internally logic's are different for each passing params(5 params are there).

2 Questions=>
1) Does above process the whole source table and inserts data into target table in less than 5 mins or is there any tips to improve this logic.
2) for each param I want to join plsql table with corresponding DB tables. Is there any way to do it.If so,Elaborate.

--Added--

cursor returns 200k record for each parameter(5 param) and need to each update column A value with calculations based on source table data.

example:

procedure (param1) is
cursor c is select * from xyz where s=param1;
begin
open
fetch c into rec;
exit when c%notfound;
loop

for i in (select val,ct from emp where a=rec.val and b=rec.dat) loop --high priority  
if val>l_val then  --overriding
l_vt:=i.ct;
 end if;
end loop;

for i in (select val,ct from emp_ct,vol where a=rec.val and b=rec.dat) loop 
--passing parameter from rec to 2 different tables
if val>l_val then -- overriding
l_ct:=i.ct;
 end if;
end loop;

if l_vt !=0 then 
c= l_vt;               --updating c based on calcuation.
elsif l_ct!=0 then
c=c=l_ct;
else
c=def;
end if;

insert into target_table  --200k records
values(rec);

end loop;
close c;
end;

--this procedure repeat for 5 different params for 1M(200k*5) records. I want to optimize the performance.


Thanks,
Sravani

insert into target_table
select 
   ...,
   greatest(l_vt,ct1,ct2)
from (
  select xyz.*,
      ( select max(ct)
        from emp
        where a=xyz.val and b=xyz.dat
        and   val > l_val 
      ) ct1,
      ( select max(ct)
        from emp_ct,vol
        where a=xyz.val and b=xyz.dat
        and   val > l_val 
      ) ct2
  from xyz 
)