remove duplicates
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: remove duplicates

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346

    remove duplicates

    Hi,

    I have a table with 50 million rows. The table has 4 columns, where the 4 columns make up a unique row.

    Unfortunately, duplication has occurred on 500 thousand rows.

    I need to remove the duplicates and leave one row intact per duplication.

    I've looked at various ways of doing this, but does anyone have any experience of the quickest way??

    Would I be better off recreating the table (with a dedupe statement) or sticking with the delete??

    I'd appreciate any examples.....

    Thanks.

  2. #2
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    I'm looking at using this.....

    Code:
    delete from HORACE
     where rowid in 
         ( select rid
           from ( select rowid rid,
                  row_number() over
                      (partition by account_org
                                   ,account_number
                                   ,statement_id_code
                                   ,statement_sequence_number
                       order by rowid) rn
                  from HORACE
                 )
            where rn <> 1 );
    Which I've modified from a single column version by Mr. Kyte

  3. #3
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    The delete is taking way too long.

    What syntax would I use to create another table from the original table, but with the duplicates removed?

  4. #4
    Join Date
    Jan 2001
    Posts
    3,131
    SQL> Alter system execute developers;
    I remember when this place was cool.

  5. #5
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Originally posted by Mr.Hanky
    SQL> Alter system execute developers;
    wtf?!@!@!%$!!!!!

    PHP Code:
    SQLAlter system execute developers;
    ERROR at line 1:
    ORA-02065illegal option for ALTER SYSTEM
    ORA
    -02342you must purchase the advanced 'Kill all the developers' package to use this feature 
    I'm stmontgo and I approve of this message

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333

  7. #7
    Join Date
    Jan 2001
    Posts
    3,131
    Originally posted by stmontgo
    wtf?!@!@!%$!!!!!

    PHP Code:
    SQLAlter system execute developers;
    ERROR at line 1:
    ORA-02065illegal option for ALTER SYSTEM
    ORA
    -02342you must purchase the advanced 'Kill all the developers' package to use this feature 

    A good DBA is always prepared, you should have planned for this in your impact assessment and budgeted accordingly.
    I remember when this place was cool.

  8. #8
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    hi hunie,

    if ur table is partitioned, remove the duplicates by 1 partition
    range at a time. Try this one...


    Delete from table1 partition (1)
    where rowid in
    (select max(rowid) from table1 partition (1)
    group by col1,col2,col3,col4 having count(*)>1)
    /

  9. #9
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    Thanks for the advice guys,

    We have been using Quest's SQL Terminator application, but developers are 10 a penny, so the problem is not going away.

    The table is not partitioned, so that's not an option.

    Thanks for the link Davey, useful for identifying the duplicates, but no help in leaving a single valid row in the offending table, or in recreating a new table minus the duplication.

  10. #10
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    up to you to decide which row to delete then instead of deleting the wrong one by accident

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