2

Suppose, i issued merge statement to merge two tables. There is a huge amount of data, so merge command was running. In midway, a different user inserted new data in source table. So, will the new data be read as a part of merge command?

Tulika
  • 128
  • 8
  • 1
    No, data inserted into the **source** table will not be seen by the MERGE statement. –  Jun 04 '20 at 12:45

2 Answers2

1

Oracle's data concurrency model is such that a given transaction only sees the database at it was when the transaction started. It does not see any changes that began after it started. And it does not see any uncommited changes that happened to exist when it did start. See the discussion on Transaction Management in the Database Concepts manual.

EdStevens
  • 3,708
  • 2
  • 10
  • 18
  • Thanks for the input everyone...i tried it and yes..merge command considered only the previous set of records. – Tulika Jun 05 '20 at 13:13
1

MERGE statement is complicated. As @EdStevens writes, you session should only see data as they were before your statement started. That's rule of thmb for Oracle

On the other side I would say, that most likely your statements will fail with:

ORA-30926: unable to get a stable set of rows in the source tables
Cause: A stable set of rows could not be got because of large dml activity or a non-deterministic where clause.

Whenever you call MERGE, be ready that it can fail, and will have to be re-executed.

MERGE statements seems to be a useful Swiss army knife for various situations. But rather be careful when you use it. Usually when your MERGE statement uses only UPDATE part, then Update able JOIN will be more efficient.

Merge also can trigger various triggers INSERT/UPDATE/DELETE BEFORE/AFTER and the order of trigger firing is documented, but it might happen that actually it does not work as documented.

There is huge and very interesting discussion on Postgres, where they explain why did they not implement ISO SQL MERGE, but rather they implemented proprietary, but more reliable UPSERT.

ibre5041
  • 4,903
  • 1
  • 20
  • 35