​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


elmama

                             De-duplicate an Oracle table

A request at work to de-duplicate an Oracle table - such is the minutiae of daily working life. Approx 200,000 rows out of a total of about 3,000,000.
Many ways to do this, including:
Create a new table as select distinct … then drop the old table and rename the new one to the old (not so good if the orig table has indexes)
My preferred way is to use analytics to do the delete in-situ (this courtesy of Tom Kyte - checkout his excellent site http://asktom.oracle.com/ if you haven’t already done so)

The column(s) you want to check for duplicatedness (if there is such a word) go after the partition by clause:

delete from tab1
where rowid in ( 
select rid from ( 
select rowid rid,row_number() over (partition by col1,col2 order by rowid) rn from tab1) 
where rn <> 1)