RE: deleting duplicate records

From: Jack C. Applewhite <japplewhite_at_inetprofit.com>
Date: Mon, 03 Dec 2001 14:16:32 -0800
Message-ID: <F001.003D3DEA.20011203135552@fatcity.com>

Sunil,
 

If there are multiple
duplicates for even a few SKU_Num values, you're doing multiple scans (full table or full index) to get all the dups out.  You might reconstruct the SQL to not use a looping construct if there are lots of duplicate rows for each SKU_Num
 

Delete From
FMS_Test
Where ( SKU_Num, RowID )
In
(
 Select SKU_Num,

RowID
 From  

FMS_Test
 Minus
 Select SKU_Num, Max (

RowID )
 From  

FMS_Test
 Group By

SKU_Num
) ;
 

Or keep the loop and add a
Commit right after the Delete statement - that will cut down on Rollback segment usage.
 

Is there an index on the column
sku_num?  It would probably help as well.
 

Jack

--------------------------------Jack C. 

ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin,
Texaswww.iNetProfit.comjapplewhite_at_inetprofit.com(512)327-9068

  <FONT face=Tahoma
  size=2>-----Original Message-----From: root_at_fatcity.com   [mailto:root_at_fatcity.com]On Behalf Of   Sunil_Nookala_at_Dell.comSent: Monday, December 03, 2001 3:10   PMTo: Multiple recipients of list ORACLE-LSubject:   deleting duplicate records
  Hello all,
   
  could someone please tell me why the procedure   below(Author:Nick Butcher) takes less than a min<SPAN   class=175265920-03122001>ute ona table with 50,000 rows and about   21 mins on a table with 235,000 rows??    
  i have created a bigger rollback segment to take care of   this, but no improvement.where should i be looking for   bottlenecks??
   
  CREATE PROCUDURE DUPES_DEL ASBEGIN
   
   LOOP DELETE from fms_test where
  row_id in(select min(rowid)   from
  fms_test   group by sku_num   having
  count (*) >1);  EXIT WHEN SQL%NOTFOUND END   LOOP;    COMMIT;END;
   
  appreciate it.Sunil NookalaDellCorp.Austin,   TX Received on Mon Dec 03 2001 - 16:16:32 CST