Does Oracle MERGE statement return before commit is finished

Question

I have a table with some 7M rows and an SSIS package that updates 5.6M rows in that table with a statement like the following.

MERGE INTO Target t
USING
(
 select ID,
 Type
 from SomeTable
) s
ON (t.ID = s.ID)
WHEN MATCHED THEN UPDATE SET t.Type = s.Type

I then start several large select statements that do an inner join to Target. I've been getting ORA-01555 (snapshot too old: rollback segment number string with name "string" too small) from those select statements. This looks to me like the MERGE statement returns before the commit has finished. My initial thought was to increase the size of the rollback segment (which admittedly might introduce integrity problems) but our DBAs were not keen on that idea. They suggested I wait for 1 minute before starting the select statements. (To me synchronization with artificial waits is a red flag.)

My question is, is there any way to ensure that the MERGE commit has finished before the MERGE statement returns? To me that would solve the problem.

Answer 1

I would avoid merging entire tables (if possible):

MERGE INTO Target t
USING( select ID, Type
      from SomeTable
      where some_kind_of_timestamp_column > date_of_last_incremental_load
) s
ON (t.ID = s.ID)
WHEN MATCHED THEN UPDATE SET t.Type = s.Type
                  WHERE (t.Type != s.Type)
                    OR (t.Type IS NULL AND s.TYPE IS NOT NULL)
                    OR (t.Type IS NOT NULL AND s.TYPE IS NULL)
                  -- there is no need for updating A -> A

COMMIT;

-- some select