The standard way to delete duplicate rows in Oracle is to keep one row per group and delete the rest using the ROWID pseudocolumn: DELETE FROM your_table WHERE rowid NOT IN (SELECT MIN(rowid) FROM your_table GROUP BY the, columns, that, define, a, duplicate). ROWID works because it uniquely identifies each physical row even when every column value is identical, which is exactly the case plain DISTINCT cannot handle on a delete.
Every answer online pastes the ROWID query and stops. The query is the easy part - the traps are performance on big tables and deleting the wrong rows because you defined duplicate wrong.
Table of contents
- Why ROWID and not DISTINCT
- Define duplicate correctly first
- Faster variants for large tables
- Safeguards before you run it
- Where RunxBuild fits
- FAQ
Why ROWID and not DISTINCT
DISTINCT removes duplicates from a query result, but you cannot DELETE DISTINCT - a delete has to target specific rows, and if two rows are byte-for-byte identical, there is no column value that tells them apart. That is where ROWID comes in: it is Oracle’s internal address for each physical row, guaranteed unique even for otherwise identical rows.
The pattern is: group by the columns that define a duplicate, keep the minimum (or maximum) ROWID in each group, and delete everything else.
DELETE FROM employees
WHERE rowid NOT IN (
SELECT MIN(rowid)
FROM employees
GROUP BY first_name, last_name, email
);That keeps one row per name-and-email group and removes the rest.
Define duplicate correctly first
The single most dangerous mistake is the GROUP BY list. The columns you group by are your definition of a duplicate, and if that list is wrong, you delete rows that are not actually duplicates.
Group by too few columns and you treat genuinely different rows as duplicates and delete real data. Group by every column and you only catch fully identical rows, which may miss the logical duplicates you meant. Before deleting anything, run the SELECT half as a count to see what you are about to remove:
SELECT first_name, last_name, email, COUNT(*)
FROM employees
GROUP BY first_name, last_name, email
HAVING COUNT(*) > 1;If that result does not match your mental model of duplicate, fix the column list before you touch DELETE.
Faster variants for large tables
NOT IN with a subquery is fine for modest tables but can be slow on large ones and behaves awkwardly if any ROWID could be null (it cannot here, but the habit matters). Two faster patterns:
Analytic function approach, which usually optimizes better:
DELETE FROM employees
WHERE rowid IN (
SELECT rid FROM (
SELECT rowid AS rid,
ROW_NUMBER() OVER (PARTITION BY first_name, last_name, email ORDER BY rowid) AS rn
FROM employees)
WHERE rn > 1);For very large tables, it is often faster to create a new table with the distinct rows (CREATE TABLE clean AS SELECT … with a dedup), then swap it in, rather than deleting millions of rows in place and generating huge amounts of undo and redo.
Safeguards before you run it
Deleting rows is not reversible once committed, so put guards in front of it. Back up the table first, even if just CREATE TABLE employees_bak AS SELECT * FROM employees. Run inside a transaction so you can ROLLBACK if the count is wrong - do not autocommit. Check the row count before and after against what you expected.
And if the table has foreign keys pointing at it, deleting a duplicate that is referenced elsewhere will either fail or cascade, so understand the relationships before deleting. The query is five lines; the discipline around it is what keeps a dedup from becoming a data-loss incident. Measure twice, delete once.
Where RunxBuild fits
RunxBuild offers managed MySQL and Postgres rather than Oracle, so this specific syntax is not something you would run on our platform - but the discipline is identical on any database: define the duplicate correctly, preview with a SELECT count, back up, and delete inside a transaction. On managed Postgres the equivalent uses ctid where Oracle uses rowid.
What the managed side gives you is the safety net around the operation: automated backups you can restore if a dedup goes wrong, and a database you did not have to provision and patch yourself. The database introduction docs cover backups and access, which is the part that turns a scary production DELETE into a recoverable one.
How this fits the rest of the stack
The cost of a project is the sum of its line items, not the headline number, and the RunxBuild hosting calculator is the right place to add them up: the service, the database, the storage, the bandwidth, each a separate figure. Model it before committing, not after the first invoice. The RunxBuild dashboard is where the running project shows its real usage in one place.
Useful related references:
FAQ
How do I delete duplicate rows in Oracle?
Keep one row per duplicate group and delete the rest using ROWID: DELETE FROM table WHERE rowid NOT IN (SELECT MIN(rowid) FROM table GROUP BY the columns that define a duplicate). ROWID uniquely identifies each physical row, so it can distinguish otherwise identical rows that DISTINCT cannot target for deletion.
Why use ROWID instead of DISTINCT?
DISTINCT filters a query result but cannot be used to DELETE specific rows, and when two rows are byte-for-byte identical, no column value tells them apart. ROWID is Oracle’s unique physical address for each row, so it can target exactly which of the identical rows to keep and which to remove.
How do I preview which rows will be deleted?
Run the grouping as a count first: SELECT the group columns, COUNT() FROM table GROUP BY them HAVING COUNT() > 1. This shows the duplicate groups and how many rows each has, so you can confirm your definition of a duplicate is correct before running any DELETE.
What is the fastest way to dedup a very large Oracle table?
For huge tables, creating a new table with the distinct rows via CREATE TABLE clean AS SELECT and then swapping it in is often faster than an in-place DELETE, because deleting millions of rows generates large amounts of undo and redo. A ROW_NUMBER analytic approach is also usually faster than NOT IN.
Is deleting duplicate rows reversible?
Not once the transaction is committed. Back up the table first, run the delete inside a transaction so you can ROLLBACK if the counts are wrong, and check foreign-key relationships beforehand, since deleting a referenced row can fail or cascade. Managed databases with automated backups let you restore if a dedup goes wrong.