session hangs

Dear All,

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,

K.Diwakar

Do you have an index on the column, hoclientid ?

Tamil

Dear Tamil,

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??

Regards,

K.Diwakar

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)

HTH
Gregg

Thank U tamil and Gregg,

It goes on a Index range scan now.

Let me see how long it takes.

Regards,

K.Diwakar

Dear All,

This process takes around 2hours to get going. Any better way of getting the result??

Regards,

K.Diwakar