How to update foreign keys in a large fact table using FORALL

oracleplsql

I have 2 tables, one is a fact table (100000000 records), and the other is a catalog (2000), I need to update the FK at the fact table, can I use FORALL in this case?. I wrote this but it doesn't work.

CURSOR cur_matched IS
SELECT  PER_C_PERIOD_ORIG, PER_C_PERIOD
FROM   TBL_CATALOG
order by PER_C_PERIOD;

registro cur_matched%ROWTYPE;

VAR_PER_C_PERIOD_ORIG VARCHAR2(150);
VAR_PER_C_PERIOD  NUMBER;
vEstatus varchar(3);

TYPE tab_matched IS TABLE OF TBL_BIG_TABLE.PER_C_PERIOD%TYPE;

rec_matched tab_matched := tab_matched();
rec_match_tot tab_matched := tab_matched();

indx  number;

BEGIN

         OPEN cur_matched;
         LOOP

                indx := 0;

             FETCH cur_matched INTO VAR_PER_C_PERIOD_ORIG,VAR_PER_C_PERIOD;
             EXIT WHEN cur_matched%NOTFOUND;


                 SELECT  PER_C_PERIOD_ORIG 
                 BULK COLLECT INTO  rec_matched
                 FROM TBL_BIG_TABLE 
                 WHERE PER_C_PERIOD_ORIG = VAR_PER_C_PERIOD_ORIG;

                 FORALL indx IN 1 .. rec_match_tot.COUNT
                 UPDATE TBL_BIG_TABLE
                 SET PER_C_PERIOD =  VAR_PER_C_PERIOD
                 WHERE PER_C_PERIOD_ORIG = rec_match_tot(indx);

                COMMIT;


        END LOOP; 
        CLOSE cur_matched;

Best Answer

I think this will do the trick, unless I have missed something. It has the benefit of less and more maintainable code, and performance wise it will likely outperform PL/SQL versions.

MERGE INTO tbl_big_table big
USING (
  SELECT 
    per_c_period_orig, 
    per_c_period
  FROM tbl_catalog
  ORDER BY per_c_period
) small
ON (big.per_c_period_orig = small.per_c_period_orig)
WHEN MATCHED THEN 
  UPDATE SET big.per_c_period = small.per_c_period;

COMMIT;
Related Question