Deleting Duplicate Rows in Oracle

bhaskar says:

Nice, but may be you should elaborate a little on why certain approaches are slow and inefficient while others are better suited for the job.
May be a explain plan for each of the delete statements will help further explain your point.

Ritesh Bhatnagar says:

Hi Anuj,
Had some time today so was browsing through your Oracle posts. Nice issue and resolutions but I think the solution for removing duplicates needs to be corrected above.

1. First, your query would retain only the duplicates at the end as you are CTAS using row number 1. This mean it will return duplicates in the test1 table. The correct solution would be to put rn=1.

2. Since you have the row_number value , why are you using ROWID again to query tbl_test? This is an unnecessary join, you can directly CTAS from the inner query (as shown below). This way you are eliminating the re-query of the tbl_test table based on ROWID.

CREATE TABLE tbl_test1 NOLOGGING
AS
SELECT * FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY ser_no, fst_nm, deptid, cmnt ORDER BY ROWID) rn, tbl_test.*
FROM tbl_test)
WHERE rn=1;
And then perform step 2 and step3.

Ritesh Bhatnagar says:

Hi Anuj,
Point 1: I see the new corrected query now. Thanks for making the correction.
Point 2: I would still feel that providing full column names in the select query would be a simpler option than re-querying the table. If you are re-querying the table just to avoid the column list then I would point that you are anyway using the full column list in the PARTITION BY clause of the ROW_NUMBER function. It would be matter of pasting the same column in the SELECT clause. Here would be the fine tuned version of the query without “rn” being part of the tbl_test1 table –
CREATE TABLE tbl_test1 NOLOGGING
AS
SELECT ser_no, fst_nm, deptid, cmnt FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY ser_no, fst_nm, deptid, cmnt ORDER BY ROWID) rn, tbl_test.*
FROM tbl_test)
WHERE rn=1;
You can now perform step 2 and step3 as before.

ranjeet says:

CREATE TABLE tbl_test1 NOLOGGING
AS
SELECT distinct ser_no, fst_nm, deptid, cmnt FROM tbl_test

and now perform step 2 and 3

Info says:

All the contents of this Website is a copy paste from other website like tutorialspoint,javaworld
,coderancs,stack overflow

book like headfirst etc..

Could face copy protection law

Prajeesh says:

Good one.. But the 5th one using analytic fn is not giving correct result.

akash says:

may be this would work
create new table using following query
create table new as select distinct * from old;
and then delete old table

Nitin Agrawal says:

Very good posts…I have many of your posts on Java also & those were very helpful & informative. Similarly this one also has great stuff…keep posting such information
Thanks

Jay says:

Thanks. Nice post. Helped

Ivan says:

CREATE-DROP-RENAME

Step 1: CREATE TABLE tbl_test1 NOLOGGING
AS
SELECT DISTINCT t .* FROM FROM tbl_test t ;

STEP 2 : DROP TABLE tbl_test;

STEP 3: RENAME tbl_test1 TO tbl_test;

Kovid Rathee says:

The 6th option is the most efficient with tables with a large number of records. I’ve performed this on 6 million records and it worked out just fine.

Hiren says:

Hi!

I’m new to Oracle. I’ve a problem that, I’ve eight table. Table-1 contains Customer Detail, and other contains different details related to customer in Table-1. I want to find out Customer whose details not available in all other tables. I want to mark this records as UNUSED. One field is common in all tables. I tried a lot but I’m not able to do this in single query.

I tried to do it with Union All but it takes tooooooo mush time.

How can I do this ?

angel ikaz says:

i would suggest you to try DuplicateFilesDeleter , it can help resolve duplicate files issue.

SANKARAN DHANANJAYAN says:

Thanks a lot … excellent tutorials on deleting duplicate records…

Helcio Furlan says:

And this solution?

DELETE tbl_test a
WHERE a.ROWID > (SELECT MIN(b.ROWID)
FROM tbl_test b
WHERE b.col_pk = a.col_pk);

OR

DELETE tbl_test a
WHERE a.ROWID < (SELECT MAX(b.ROWID)
FROM tbl_test b
WHERE b.col_pk = a.col_pk);

where col_pk is a primary key column

Bilal\ says:

In Microsoft Access I have never get this issue i think you should switch to access from oracle because of this and some other reason like auto backup, well user interface etc.
Regards Bilal Hussain

anonymous says:

good article

Fawad khan says:

HI
i would suggest you to try DuplicateFilesDeleter, it can help resolve duplicate files issue.

thx

albus says:

I believe this query can work
create table new as select distinct * from old;
and then delete old tables

Deepak Kushwaha says:

Wow Very Nice Information Thanks For Sharing

Jhon Yasin says:

Right here is the right web site for anyone who wishes to understand this topic. You understand so much its almost hard to argue with you (not that I actually would want to…HaHa). You definitely put a fresh spin on a topic that’s been written about for decades. Wonderful stuff, just excellent!

jinal says:

Nice Blog Post.Thanks For Sharing this information.

getintopc says:

Nice Information. I read your blog very helpful to me. Thanks for sharing it.

Nouman Ali says:

Wow so nice Amazing post, thanks for sharing this article.
Thanks again