By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,377 Members | 490 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,377 IT Pros & Developers. It's quick & easy.

Delete Duplicate Records from a large table

P: 21
Hi All,

I want to delete duplicate records from a large table.

There is one index(INDEX_U1) on 4 columns(col1,col2,col3,col4) which is in unusable state.
First of all when i tried to rebuild index it showed error as unique key violation.

So i want to delete duplicate records for col1,col2,col3,col4 combination.

How can i delete the duplicate records from this large table?

Please suggest some effective way of deleting duplicate records so that i can rebuild the index.

Thanks
Dilip
Sep 1 '08 #1
Share this Question
Share on Google+
6 Replies

debasisdas
Expert 5K+
P: 8,127
please check for some articles in the how to section.
Sep 2 '08 #2

P: 21
Thanks Debashish

I wanted to delete the records from that table with out dropping the index or truncating the table.

Is there anyway for this

Thanks
Dilip
Sep 2 '08 #3

amitpatel66
Expert 100+
P: 2,367
What did you try so far??
Sep 3 '08 #4

P: 21
I tried using alter session SKIP_INDEX_UNUSUABLE=TRUE

Still i could not delete the records.

Thanks
Dilip
Sep 3 '08 #5

amitpatel66
Expert 100+
P: 2,367
I tried using alter session SKIP_INDEX_UNUSUABLE=TRUE

Still i could not delete the records.

Thanks
Dilip
YOU will need to write a DELETE query to delete the dupliactes from a table
Sep 4 '08 #6

P: 21
I have already tried to write the query which will delete duplicate records.

Query:
delete from table where row_id not in
(
select min(row_id) from table group col1,col2,col3,col4
);

Since i am not able delete a single duplicate record from the table with index in unusuable state, the above query also gave the same error.

Finally i had to drop the index and delete the duplicate records and then recreate the index.

But i wanted to do the same with out dropping the index.

Is there any way to do so?

Thanks & Regards
Dilip
Sep 4 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.