CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloper
Mark Harrison
Mark Harrison

Reputation: 304584

Why does Oracle SQL Developer use such strange delete criteria?

When deleting rows in the grid view, the message panel indicates that SQL Developer issues this delete command.

DELETE FROM "MH"."T" WHERE ROWID = 'AABUG+AAEAAEZtrAAA'
                       AND ORA_ROWSCN = '1220510600909'
                       and ( "A" is null or "A" is not null )

It seems specifying the ROWID should be sufficient to identify the row, so

Upvotes: 3

Views: 210

Answers (1)

Justin Cave
Justin Cave

Reputation: 231741

The ROWID is just the physical address of the row. If one row is deleted and another row is inserted, the new row could have the same ROWID as the old row. If the data in the row had been modified, it is also possible that its ROWID could have changed. The ORA_ROWSCN criteria ensures that neither of these have actually happened. It also allows SQL Developer to alert you if another session had modified the data since you read it so that you can confirm that you still want to delete the row.

I'm at a loss for what the A is null or A is not null predicate would be adding. If it was the first predicate, I would guess that it was the standard 1 = 1 predicate that folks sometimes add to queries that are dynamically built to simplify the process of building the SQL statement dynamically. But that doesn't fit with it being the last predicate in the query. Is A the primary key of the table?

Upvotes: 6

Related Questions