DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: session hangs

  1. #1
    Join Date
    May 2002
    Posts
    193

    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

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Do you have an index on the column, hoclientid ?

    Tamil

  3. #3
    Join Date
    May 2002
    Posts
    193
    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
    Last edited by diwakar; 12-30-2003 at 04:34 PM.

  4. #4
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    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

  5. #5
    Join Date
    May 2002
    Posts
    193
    Thank U tamil and Gregg,

    It goes on a Index range scan now.

    Let me see how long it takes.

    Regards,

    K.Diwakar

  6. #6
    Join Date
    May 2002
    Posts
    193
    Dear All,

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

    Regards,

    K.Diwakar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width