Delete Duplicate Records from a large table

[Dilip1983]

Hi All,

I want to delete duplicate records from a large table.

There is one index(INDEX_U1) on 4 columns(col1,co l2,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

[debasisdas]

please check for some articles in the how to section.

[Dilip1983]

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

[amitpatel66]

What did you try so far??

[Dilip1983]

I tried using alter session SKIP_INDEX_UNUS UABLE=TRUE

Still i could not delete the records.

Thanks
Dilip

[amitpatel66]

I tried using alter session SKIP_INDEX_UNUS UABLE=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

[Dilip1983]

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