FAQ
Hello all,

could someone please tell me why the procedure
below(Author:Nick Butcher) takes less than a minute 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

Search Discussions

  • Jack C. Applewhite at Dec 3, 2001 at 10:16 pm
    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

    -----Original Message-----From: 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 minute 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 3, '01 at 9:27p
activeDec 3, '01 at 10:16p
posts2
users2
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase