Clever Geek Handbook

Does Oracle (RDB as a whole?) Provide a snapshot of the table affected by DML?

purpose

To understand the mechanism / implementation when handling DML with a table. Does the database (I'm working on Oracle 11G R2) use snapshots (for each DML) of the tables to apply the DML?

Background

I am running SQL to update the AID field of the target table containing the old values ​​with the new values ​​from the original table.

Target and correlated source table

UPDATE CASES1 t
SET t.AID=(
  SELECT DISTINCT NID
  FROM REF1
  WHERE
    oid=t.aid
)
WHERE EXISTS (
  SELECT 1
  FROM REF1
  WHERE oid=t.aid
);

I thought "OLD01" can be updated twice (OLD01 -> NEW01 -> SCREWED).

enter image description here

However, this did not happen.

enter image description here

Question

For each DML, the database takes a snapshot of table X (call it X + 1) for DML (1st) and then keep taking a snapshot (call it X + 2) of the result (X + 1) for the next DML (2nd ) in the table, etc. for each DML that succeeds? Is this also used as a mechanism to perform a rollback / commit?

Is this the expected behavior as standard? If so, please suggest relevant links. I Googled but don't know what keywords should be for the correct result.

Thanks in advance for your help.

Update

Started reading Oracle Core (ISBN 9781430239543) by Jonathan Lewis and saw a diagram. So the current understanding is that UNDO records are created in the UNDO tablespace for each update, and the original data is restored from there, which I originally thought of as snapshots.

REDO & UNDO

+3


source to share


1 answer


In Oracle, if you run this update twice in a row in the same session, with the data as you have shown, I believe you should get the expected results. I think you must have gone somewhere. (For example, if you did the update once, then without committing, you opened a second session and did the same update again, then your result would make sense.)

Conceptually, I think the answer to your question is yes (speaking specifically about Oracle, that is). An SQL statement effectively works with a snapshot of tables from the moment the statement begins execution. The correct term for this in Oracle is read consistency. However, the mechanism for this does not involve taking a snapshot of the entire table before making changes. This is more of the opposite — change records are stored in undo segments and are used to return table blocks at the appropriate point in time as needed.



The documentation you should study to understand this to some extent is in the Oracle Concepts manual: http://docs.oracle.com/cd/E11882_01/server.112/e40540/part_txn.htm#CHDJIGBH

+4


source






More articles:


All Articles