-
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?
-
"duplicate primary keys" - no this is not normal
Are you sure you are using Oracle?
Suggest you post SQL, explain plan, table & index definitions . . .
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|