I need to compare records between two tables in a cursor so that I can insert data into another table if the data is NOT same:
CURSOR cur1 is select t1.*, t2.* from t1, t2 where t1.col1 = t2.col2
FOR rec1 IN c1_cursor LOOP
NVL(rec1.col1,'*') = NVL(rec1.col2,'*') AND
NVL(rec1.col3,'*') = NVL(rec1.col4,'*')
The left hand side records in the cursor are from t1 table and right from t2 table. I have 1 million records to compare and 30 columns to compare, how do I increase performance. Currently it takes about 40 minutes for the cursor to go through this comparison.
I have to use a cursor b/c there is come processing that needs to be done for columns before they are compared in the IF stmt.
I "assume" that some primary key exists which can be used to "match"
identical records within the two tables.
Keep in mind that you need to deal with three different cases where the data is "different".
1) A record exists in table X and does not exist within table Y.
2) A record exists in table Y and does not exist within table X.
3) The same key exists in both table but 1 or more fields differ.
One approach is to use TWO cursors so that only a single full table scan through each table is required be getting the records using the same ORDER.
1) Get a record from each table.
2) If the PK from both tables match then compare the other fields.
3) If PK from X > PK from Y, then row from Y is not in X
a) process the row from Y get next row from Y
b) go to 2
4) If PK from Y > PK from X, then row from X is not in Y
a) process the row from X, get next row from X
b) go to 2
Another approach would be to populate two work tables
which contain the ROWIDs of rows in the two tables
that are different as derived by using MINUS
I am trying to match between base table and production table. These are the steps I take:
1) Match of PK of prod and base, if they do not match insert into Prod
2) If the do match the compare other columns
3) if other columns are equal then do nothing
4) else insert into work table
5) from work table update prod table
I tried to use 2 cursors, but it takes more time than one cursor doing a join.