session hangs

[diwakar]

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

[tamilselvan]

Do you have an index on the column, hoclientid ?

Tamil

[diwakar]

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

[gbrabham]

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

[diwakar]

Thank U tamil and Gregg,

It goes on a Index range scan now.

Let me see how long it takes.

Regards,

K.Diwakar

[diwakar]

Dear All,

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

Regards,

K.Diwakar