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

Thread: deleting duplicate rows sitting for hours..

  1. #1
    Join Date
    Feb 2003
    Posts
    67

    deleting duplicate rows sitting for hours..

    I am trying to delete duplicate primary keys in a table with about 150,000 rows and it is sitting there for hours... no deadlocks... basically there is no other user in the database...

    is this normal?

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    "duplicate primary keys" - no this is not normal

    Are you sure you are using Oracle?

    Suggest you post SQL, explain plan, table & index definitions . . .

  3. #3
    Join Date
    Feb 2003
    Posts
    67
    BY DUPLICATE PRIMARY KEYS I MEANT >>>DUPLICATE ROWS OF THE PRIMARY KEY COLUMNS THAT ACCIDENTALLY GOT IN WHEN CONSTRAINTS WERE DISABLED DURING A DATABSE SYNC<<<<

    1 DELETE from clm_lne_log a
    2 WHERE ROWID NOT IN (SELECT MIN(ROWID)
    3 FROM clm_lne_log b
    4 where a.clm_log_nbr = b.clm_log_nbr
    5* and a.clm_lne_seqnbr = b.clm_lne_seqnbr)


    SQL> desc clm_lne_log
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    CLM_LOG_NBR NOT NULL NUMBER
    CLM_LNE_SEQNBR NOT NULL NUMBER
    REV_CD VARCHAR2(4)
    PROC_CD VARCHAR2(6)
    SVC_BGN_DT DATE
    SVC_END_DT DATE
    CHG_AMT NUMBER(10,2)
    UNTS_OF_SRVC NUMBER

    The first two columns represent the primary with a unique index on them. There is a foreign key constraint on clm_log_nbr.

    Plan Table
    --------------------------------------------------------------------------------
    | Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
    --------------------------------------------------------------------------------
    | DELETE STATEMENT | | | | | | |
    | DELETE |CLM_LNE_L | | | | | |
    | FILTER | | | | | | |
    | TABLE ACCESS FULL |CLM_LNE_L | | | | | |
    | SORT AGGREGATE | | | | | | |
    | TABLE ACCESS FULL |CLM_LNE_L | | | | | |
    --------------------------------------------------------------------------------[
    Last edited by sureshot; 12-12-2003 at 03:00 PM.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Two things.

    1. You say your PK constraint was disabled during database sync. Dpending on how your PK index/PK constraint were created, there is fairly high probability that the PK index was actually dropped when you disabled the PK constraint. Can you make sure that the index on (CLM_LOG_NBR, CLM_LNE_SEQNBR) still exists in your database? If it's not there (meaning it was dropped when PK was disabled), create a NUNUNIQUE index on them.

    2. Your should change your delete statement. When you are sure the index on those two columns exists, try delete the duplicates with the following statement:

    DELETE from clm_lne_log a
    WHERE EXISTS
    (SELECT null FROM clm_lne_log b
    WHERE a.clm_log_nbr = b.clm_log_nbr
    AND a.clm_lne_seqnbr = b.clm_lne_seqnbr
    AND a.ROWID < b.ROWID);
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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