It’s been a week of pondering since my last post on the 650k row table to table update. After a bunch of suggestions SQL Express finally met it’s match in Oracle. Below is the code for the same update using the Oracle MERGE functionality. To me it looks like the equivalent of the SQL Server join-update functionality. The statement executes in under 40 seconds with indexes on both the tag and index fields for both tables.
MERGE into FUNCTIONAL_POINT F
USING
(
select L.FL, L.TAG FROM LOCATION L
INNER JOIN FUNCTIONAL_POINT FP
ON FP.TAG=L.TAG
) L
ON ( F.TAG = L.TAG )
WHEN MATCHED THEN UPDATE SET F.FL = L.FL
WHEN NOT MATCHED THEN INSERT ( F.FL ) VALUES ( L.FL );
--NOT MATCHED is never hit, but required for merge statement
The only caveat is that the join must be on a unique field and produce only one result (thus one unique FL per TAG in the above case).
Granted it may take you a few seconds longer to write and add the table indexes I think it is absolutly worth it. Thanks for my bro for persisting with options to try on Oracle.
(for this example we’ll use OLDSERVER and NEWSERVER as the old and new names for the server).