Iam trying to remove the duplicate records from a table that has a total of around 32LAKH records with the query:
Delete from pdmcontact a where a.rowid > any (select rowid from pdmcontact b where a.hoclientid = b.hoclientid);
It has been running for more than around 3hours. Is there any other better way of deleting the duplicates or any idea as to how long should I wait or any clues to speed up the process??
Thanks in advance,
Do you have an index on the column, hoclientid ?
There is no index on hoclientid.
There is an index on the primary key columns...
Shall I remove the primary key till the delete completes??
Last edited by diwakar; 12-30-2003 at
At minimum, I would have an index on hoclientid if that is what you are joining the table to itself with.... I would also place a hint in sql statement to ensure it is being used ...
(or set autotrace trace and run the delete to see the execution plan)
Thank U tamil and Gregg,
It goes on a Index range scan now.
Let me see how long it takes.
This process takes around 2hours to get going. Any better way of getting the result??
Click Here to Expand Forum to Full Width