0

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.

  • 1
    Why are you doing row-by-row processing of your data? It sounds like you want a MERGE statement. I'm having a bit of trouble figuring out why you have three separate loops in your code, though. – Justin Cave Jan 5 '15 at 19:22
1

Find a small recordset and see how many loops your code does. How many does it do for 10, 100 and 1,000 records? You will see that the number of loops increases exponentially as the number of rows increases. My rough guess is that you have asymptotically big O(2^n+2^n+2^n). Asymptotics is a way of estimating work where the amount of rows processed is not known. Can't you just write some minus selects that are union all joined together? My example is looking at indexes and I use different filters so that the data sets will not be the same. Hence you will see the difference.

SELECT 'SDO_NETWORK_METADATA_TABLE_a' table_name, index_name, column_name, column_position
  FROM dba_ind_columns
 WHERE index_owner = 'MDSYS'
   AND table_name = 'SDO_NETWORK_METADATA_TABLE'
   AND index_name 
   NOT IN ( SELECT index_name
              FROM dba_ind_columns
             WHERE index_owner  = 'MDSYS'
               AND table_name   = 'SDO_NETWORK_METADATA_TABLE'
               AND column_name  = 'LRS_TABLE_NAME' )
MINUS
SELECT 'SDO_NETWORK_METADATA_TABLE_a' table_name, index_name, column_name, column_position
  FROM dba_ind_columns
 WHERE index_owner = 'MDSYS'
   AND table_name = 'SDO_NETWORK_METADATA_TABLE'
   AND index_name 
   NOT IN ( SELECT index_name
              FROM dba_ind_columns
             WHERE index_owner  = 'MDSYS'
               AND table_name   = 'SDO_NETWORK_METADATA_TABLE'
               AND column_name  = 'PATH_LINK_TABLE_NAME' )
UNION ALL
SELECT 'SDO_NETWORK_METADATA_TABLE_b' table_name, index_name, column_name, column_position
  FROM dba_ind_columns
 WHERE index_owner = 'MDSYS'
   AND table_name = 'SDO_NETWORK_METADATA_TABLE'
   AND index_name 
   NOT IN ( SELECT index_name
              FROM dba_ind_columns
             WHERE index_owner  = 'MDSYS'
               AND table_name   = 'SDO_NETWORK_METADATA_TABLE'
               AND column_name  = 'PATH_LINK_TABLE_NAME' )
MINUS
SELECT 'SDO_NETWORK_METADATA_TABLE_b' table_name, index_name, column_name, column_position
  FROM dba_ind_columns
 WHERE index_owner = 'MDSYS'
   AND table_name = 'SDO_NETWORK_METADATA_TABLE'
   AND index_name 
   NOT IN ( SELECT index_name
              FROM dba_ind_columns
             WHERE index_owner  = 'MDSYS'
               AND table_name   = 'SDO_NETWORK_METADATA_TABLE'
               AND column_name  = 'LRS_TABLE_NAME' )
 ORDER BY 1,2,4;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.