lundi 5 janvier 2015

Compare two big tables in PL/SQL


I need to implement comparing 2 tables by set of keys (columns of compared tables). By this comparing i should check equal records (equal = equal by set of defined keys (columns in these input tables)) and then insert these records to report table with some calculated columns.


Also i need to get the different (by keys) records in these 2 tables and insert them to result table. This pl/sql stored procedure works correct, but completely unusable on real datasets (~ 30 million entries in each input table).



CREATE OR REPLACE procedure ASUPR_OUP."6.3.2" (
upd_date in date
)

as

-- filtered first input table
cursor c_end is select * from mon_end inner join dep on dep.dep = mon_end.depprod and dep.gpp_op = 1 and mon_end.division != 9 and mon_end.tehind not in (7,23) and (mon_end.depprod != mon_end.depcons or (mon_end.depprod = mon_end.depcons and dep.s_s = 1)) and mon_end.cyphdir in (1,7);

-- filtered second input table
cursor c_begin is select * from mon_begin inner join dep on dep.dep = mon_begin.depprod and dep.gpp_op = 1 and mon_begin.division != 9 and mon_begin.tehind not in (7,23) and (mon_begin.depprod != mon_begin.depcons or (mon_begin.depprod = mon_begin.depcons and dep.s_s = 1)) and mon_begin.cyphdir in (1,7);

is_found boolean;

begin

for b_row in c_begin loop
for e_row in c_end loop
if (b_row.depprod = e_row.depprod) and (b_row.depcons = e_row.depcons) and
(b_row.detail = e_row.detail) and (b_row.series = e_row.series) and (b_row.stk = e_row.stk) then
begin
if (b_row.trs != e_row.trs) or (b_row.trn != e_row.trn) or (b_row.kolsd != e_row.kolsd) or (b_row.applic != e_row.applic) then
begin
if e_row.s_s = 0 then
begin
-- insert to result table
end;
else
begin
-- insert to result table
end;
end if;
end;
end if;
end;
end if;
end loop;
end loop;
for b_row in c_begin loop
is_found := false;
for e_row in c_end loop
if (b_row.depprod = e_row.depprod) and (b_row.depcons = e_row.depcons) and (b_row.detail = e_row.detail)
and (b_row.series = e_row.series) and (b_row.stk = e_row.stk) then
begin
is_found := true;
end;
end if;
end loop;
if is_found = false
then
begin
if b_row.s_s = 0 then
begin
-- insert to result table
end;
else
begin
-- insert to result table
end;
end if;
end;
end if;
end loop;
for e_row in c_end loop
is_found := false;
for b_row in c_begin loop
if (b_row.depprod = e_row.depprod) and (b_row.depcons = e_row.depcons) and (b_row.detail = e_row.detail)
and (b_row.series = e_row.series) and (b_row.stk = e_row.stk) then
begin
is_found := true;
end;
end if;
end loop;
if is_found = false then
begin
if e_row.s_s = 0 then
begin
-- insert to result table
end;
else
begin
-- insert to result table
end;
end if;
end;
end if;
end loop;
end;


Can anyone suggest some way to boost performance of this procedure (i am using oracle 11g2) ? All input tables have indexes on used columns.





Aucun commentaire:

Enregistrer un commentaire