Find duplicate rows in Oracle table

August 25, 2009 by 4 Comments 

You are an Oracle DBA or developer and want to determine the location of duplicate rows in an Oracle table. You need it for example before attempting to place a unique index on the table. See below how can this be done.

Oracle tables always have one guaranteed unique column, the rowid column. If you use a min/max function against your rowid and then select against the proposed primary key you can squeeze out the rowids of the duplicate rows pretty quick. For example:

SELECT * FROM customers o
WHERE rowid > (SELECT min(rowid) FROM customers i
WHERE o.city = i.city and o.rating = i.rating)

In the situation where multiple columns make up the proposed key,
they must all be used in the WHERE clause.

Here is another simple and dirty way to accomplish the same:

SELECT city, rating, count(*)
FROM customers
GROUP BY city, rating
HAVING count(*) > 1

Enjoyed this article? Please share it with others using the social site of your choice:

Comments

4 Responses to “Find duplicate rows in Oracle table”
  1. Pawel says:

    I was actually searching for tips how to speed this up on two huge sub-sets of the table that has 1 billion records and matching is done on 10 columns.

  2. Koteswara says:

    If we combining those two scenarios then we can able to retrieve duplicate rows entered recently.

    SELECT * FROM hrww_transferred_data o
    WHERE rowid > (SELECT min(rowid) FROM hrww_transferred_data i WHERE o.client_workerid = i.client_workerid)
    and o.client_workerid in
    (select client_workerid from hrww_transferred_data where deleted=’F’
    group by client_workerid having count(client_workerid) > 1) order by o.askid;

  3. enrique says:

    I have a table with 40 000 000 of rows. I run this query and, after two hours, i quit the process… What can I do? Please. kike_86@msn.com

  4. Kirill Loifman says:

    Hi Enrique
    Ensure you key (a combination of rows for which you find duplicates) is indexed. In this case the query should go faster.
    — Kirill

Add a Comment

We welcome thoughtful and constructive comments from readers.
If you want your own picture to show with your comment?
Go get a Globally Recognized Avatar!

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email