RE: deleting duplicate records

[Jack C. Applewhite]

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. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of [EMAIL PROTECTED] Sent: Monday, December 03, 2001 3:10 PM To: Multiple recipients of list ORACLE-L Subject: deleting duplicate records Hello all,   could someone please tell me why the procedure below(Author:Nick Butcher) takes less than a minute on a 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 AS BEGIN    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 Nookala DellCorp. Austin, TX