Here is a quick demo to show the case:
1. Create table
DROP TABLE ora_rowscn_test CASCADE CONSTRAINTS;
CREATE TABLE ora_rowscn_test(
row_id NUMBER PRIMARY KEY,
row_value NUMBER NOT NULL
)
ROWDEPENDENCIES;
Note that the table is created with ROWDEPENDENCIES flag, so two rows in same block can have different ROWSCN.
2. Prepare data
BEGIN
delete ORA_ROWSCN_TEST;
INSERT INTO ORA_ROWSCN_TEST (ROW_ID,ROW_VALUE) VALUES ( 1, 1 );
commit;
INSERT INTO ORA_ROWSCN_TEST (ROW_ID,ROW_VALUE) VALUES ( 2, 2 );
-- commit;
INSERT INTO ORA_ROWSCN_TEST (ROW_ID,ROW_VALUE) VALUES ( 3, 3 );
commit;
END;
/
Note that commit matters here. If multiple rows are updated in one transaction, they will get same ORA_ROWSCN, otherwise, ORA_ROWSCNs will be different. This behavior seems correct according to Oracle Doc https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns007.htm, .
It is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides.
3. Check ORA_ROWSCN of rows
/*******************************************************************************
Since we updated row 1 and 2 in two seperate transactions, their ORA_ROWSCN is different,
but row 2 and row 3 share same ORA_ROWSCN.
1116438854 1 1
1116438857 2 2
1116438857 3 3
*******************************************************************************/
select ort.ora_rowscn, ort.* from ora_rowscn_test ort;
4. Update values
DECLARE
lv_number_of_rows integer;
lv_test_method integer := 1; -- 1:exists, 2:merge, 3:in
lv_first_rowscn number := 1116438854; -- SCN of row 1
lv_second_rowscn number := 1116438857; -- SCN of row 2
BEGIN
if lv_test_method = 1 then
UPDATE ora_rowscn_test ort
SET ROW_VALUE = dbms_random.random
WHERE EXISTS (
SELECT 1
FROM (
SELECT 1 row_id, lv_first_rowscn last_ora_rowscn FROM dual
UNION ALL
SELECT 2 row_id, lv_second_rowscn last_ora_rowscn FROM dual
) tmp
WHERE ort.row_id = tmp.row_id
AND ort.ora_rowscn = tmp.last_ora_rowscn
);
elsif lv_test_method = 2 then
MERGE INTO ora_rowscn_test ort
USING ( SELECT 1 row_id, lv_first_rowscn last_ora_rowscn FROM dual
UNION ALL
SELECT 2 row_id, lv_second_rowscn last_ora_rowscn FROM dual
)
tmp ON ( tmp.last_ora_rowscn = ort.ora_rowscn
AND tmp.row_id = ort.row_id )
WHEN MATCHED THEN UPDATE
SET ROW_VALUE = dbms_random.random;
elsif lv_test_method = 3 then
UPDATE ora_rowscn_test ort
SET ROW_VALUE = dbms_random.random
WHERE ( ort.row_id, ort.ora_rowscn ) IN (
SELECT 1 row_id, lv_first_rowscn last_ora_rowscn FROM dual
UNION ALL
SELECT 2 row_id, lv_second_rowscn last_ora_rowscn FROM dual
);
end if;
lv_number_of_rows := sql%rowcount;
dbms_output.put_line('lv_number_of_rows: ' || lv_number_of_rows);
dbms_output.put_line(case when lv_number_of_rows = 2 then 'SUCCESS' else 'FAILURE' end);
rollback; -- rollback for repeatedly testing
-- commit; -- commit to check result
END;
/
Here I tried 3 methods to update rows and they are supposed to be equivalent to each other, at least I thought it is. But UPDATE..WHERE..EXISTS failed when multiple unequal SCNs are joined (e.g. 1116438854 and 1116438857 are joined simultaneously), whereas MERGE and UPDATE..WHERE..IN succeed in both cases.
| Cases | UDPATE..WHERE..EXISTS | MERGE | UPDATE..WHERE..IN |
|---|---|---|---|
| Same SCNs | success | success | success |
| Diff SCNs | fail | success | success |
So my question is what makes the difference, exist clause, ORA_ROWSCN or combined?