Using rowid in a delete statement

Question

Is there any SEVERE contraindications to delete using rowid?

DELETE FROM NETATEMP.SFAC_TESTATA_CASISTICHE
      WHERE ROWID IN (  SELECT MIN (ROWID)
                          FROM NETATEMP.SFAC_TESTATA_CASISTICHE
                      GROUP BY ID_CASO,
                               DESCRIZIONE_TECNICA,
                               DESCRIZIONE_ANALISI,
                               PDF,
                               SCARTI,
                               DATA_INIZIO_ANALISI,
                               DATA_FINE_ANALISI,
                               DATA_INSTRADAMENTO,
                               DATA_RISOLUZIONE,
                               STRINGA_RICERCA,
                               SETTIMANA,
                               DATA_INIZIO_SETT,
                               DATA_FINE_SETT,
                               FATTURAZIONE,
                               IN_ELABORAZIONE
                        HAVING COUNT (1) > 1);

Solution 1

If you assume that min(rowid) returns the "earliest" row, then yes, you have a problem (because it won't)

If you use min(rowid) only to get one of the duplicates (and you don't care which), then no, there is nothing wrong with that statement.

Solution 2

A few things to be aware of. It seems you are depending on the rowid to give you the earliest row for your particular grouping (select min(rowid) ...). This won't always be true. The only way to guarantee you are deleting the earliest record is to use some column (like timestamp) that you can order by. Tom Kyte explains this better than I could.

A rowid implies NOTHING other than the location of the row physically. It does not imply age, order of insertion, or anything like that.

Also, depending on the size of the table, you will generate a lot of redo/undo, and it may be beneficial in large tables to use pl/sql to delete in chunks and commit every x rows.

Just my thoughts