How to Delete Duplicate Records in Oracle Database?

Duplicate records in a database can lead to inefficiencies and incorrect query results. Oracle SQL provides several efficient methods for identifying and removing these duplicates, ensuring data accuracy and integrity.

This article explains step-by-step how to remove duplicates using the ROWID, a unique physical address for each row. This method ensures optimal performance and accuracy.

What is ROWID in Oracle?

In Oracle, ROWID is a unique identifier that specifies the physical location of a row in the database. It is particularly useful for managing duplicate records because it allows us to distinguish between identical rows.

Steps to Delete Duplicate Records in Oracle SQL

Step 1: Create a Demo Table

Let’s first create a sample table to demonstrate the process of identifying and deleting duplicate records.

Query:

CREATE TABLE Demo (
  PersonID INT,
  LastName VARCHAR(255),
  FirstName VARCHAR(255)
);

INSERT INTO Demo (PersonID, LastName, FirstName)
VALUES (1, 'Geek1', 'Geeksforgeeks'),
       (2, 'Geek2', 'Geeksforgeeks'),
       (3, 'Geek3', 'Geeksforgeeks'),
       (1, 'Geek1', 'Geeksforgeeks'), 
       (2, 'Geek2', 'Geeksforgeeks'),
       (2, 'Geek2', 'Geeksforgeeks'); 

Output

Step 2: Identify Unique Records

To identify unique records, use a GROUP BY query along with MAX(ROWID) to pick one record for each duplicate group.

Query:

SELECT *
FROM Demo d
WHERE d.rowid IN (
  SELECT MAX(rowid)
  FROM Demo
  GROUP BY PersonID, LastName, FirstName
);

Output

Step 3: Delete Duplicate Records

To remove duplicate records, delete rows where the ROWID is not part of the unique records identified in the previous step.

Query:

DELETE Demo  
WHERE rowid NOT IN 
  (SELECT MAX(rowid)  
FROM Demo
follows PersonID, LastName, FirstName);

Output

Benefits of Using ROWID for Deleting Duplicates

1. Performance: Using ROWID avoids unnecessary sorting and is faster than other methods.
   
2. Accuracy: Ensures that only the exact duplicates are removed while keeping unique records intact.
   
3. Simplicity: Easy to implement with minimal SQL commands.

Conclusion

Deleting duplicate records in Oracle using ROWID is an efficient and reliable method. It uses the database's internal mechanics to ensure optimal performance. This approach is particularly useful in scenarios involving large datasets where maintaining referential integrity and performance is crucial.