Hi All,

My first thread to this sub-forum and first thread of this sub-forum

Here it is,

Am trying to delete duplicates from a table retaining just 1 duplicate value out of the duplicate records

for example : from n records of a table out of which x are duplicates, I want to remove x - 1 records retaining 1 record from the x duplicates

This is the query am using,

but I think this could still be optimized for this query really takes time for huge dumps.

DELETE FROM tableA
WHERE rowid not in
(SELECT MIN(rowid) FROM tableA GROUP BY column1)

Oracle 9i

rowid is not an indexed column - it is a "pseudocolumn'. the in () subselect will read thru the entire select statement's result set each time. When I get back in a while I'll write something that is faster. You may need to add an index.

Matrix,
Your solution is very simple and easy to understand.

Although it works fine for small to medium tables, or for tables with low access/update,
for very large tables, or tables with heavy access/update, it may:
1) Generate an abnormally long transaction.
2) Fill the logs.
3) Be involved with another process in a deadlock.
4) Run for a very long time.

It is also important to note that for large tables, the internals of your query will be
very ineficient as the system will store a rowid for each unique key and loop thru
each one for every row.

The best and optimized solution would be to write a program to loop thru each row
in the table, begin a transaction and commit every number of deleted rows -- usually
one to five thousand is very quick, safe and easy on the database.

Good luck!

I don't know if this is more efficient, but it seems like a positive approach might be better where there are only a few duplicates.

DELETE FROM tableA A1
WHERE column1 in (SELECT column1 FROM tableA GROUP BY column1 having count(*) > 1)
and rowid != (select min(rowid) from tableA A2 where A1.column1 = A2.column1)

Does that mean its executing in this fashion.

rowid <1> - evaluate sub query
rowid <2> - evaluate sub query
.
.
.
rowid <n> - evaluate sub query

Shell_Life

out of the 4 potential hazards that you have listed

since the query is executed only on a table with 0.25 million records, I just encounter the 4th hazard which is taking real long time.

When it initially took such a long time, I though I might be receiving ' Long transaction aborted '. But didn't.

Considering the alternative of programmatically deleting is a fine idea without filling the logs.

By definition, rowids are the physical address of each row, thus it is also an index.

It is also important to note that the database server does not assign rowids to rows
in fragmented tables.