Deleting rows in Oracle: evaluating vs. executing

Question

When I run a DELETE command in Oracle, it does all of the should-I-delete-this-row evaluation before it performs any deletion, right? That's well-defined Oracle behavior, right?

For example, if I have a table ATABLE with numeric ID and alphanumeric VALUE columns, and three rows with different IDs but all the same VALUE, and I run this command ...

delete from ATABLE A1
where exists (select * from ATABLE A2
where A1.VALUE = A2.VALUE and A1.ID <> A2.ID);

... then it deletes all three rows. To me that indicates evaluate-evaluate-evaluate, delete-delete-delete. If it were evaluate-delete, evaluate-delete, evaluate-delete, then there would be one row remaining at the end, because it would have no row to match with.

Again, just checking that the theory matches the practice here. Thank you.

Answer 1

if I have a table ATABLE with numeric ID and alphanumeric VALUE columns, and three rows with different IDs but all the same VALUE, and I run this command ...

delete from ATABLE A1
where exists (select * from ATABLE A2
where A1.VALUE = A2.VALUE and A1.ID <> A2.ID);

... then it deletes all three rows.

Yes, but you can test that yourself:

CREATE TABLE atable (id, value) AS
SELECT LEVEL + 0, 1 FROM DUAL CONNECT BY LEVEL <= 3 UNION ALL
SELECT LEVEL + 3, 2 FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT LEVEL + 4, 3 FROM DUAL CONNECT BY LEVEL <= 2;

Then the table contains:

ID	VALUE
1	1
2	1
3	1
4	2
5	3
6	3

If you run your DELETE query then, after the DELETE the table will contain:

ID	VALUE
4	2

All the duplicate VALUE rows are deleted.

fiddle