duplicate records
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: duplicate records

  1. #1
    Join Date
    Oct 2000
    Posts
    211
    Hi Friends,
    I have a table with 42 fields. No constraints are defined on any of them. Every hour fresh records are being inserted using SQL Loader. Average daily records being loaded is around 60 million.
    Problem is quite a number of the records are duplicate and these are increasing the total size of the database. If I use a trigger for checking the duplicates during insert phase, it slows down the insert. Can somebody suggest the script, to clean the duplicate records so that the script can be run during lean hours and clean up the duplicate records(except the original record ofcourse)?
    I am running 8.1.6 on Unix.
    Thanks
    manjunath

  2. #2
    Join Date
    Jun 2000
    Posts
    179

    Talking

    Why do not you add a constraint to the table ??
    Hisham Nagia
    IT Manager For Development
    Oracle Consultant - OCP

  3. #3
    Join Date
    Jan 2001
    Posts
    126
    Hi,

    As a one time activity I suggest you export the table, create the table with unique key constraint and import the data. Subsequently you should not be having problems on duplicates.

    Baliga

  4. #4
    Join Date
    Feb 2001
    Posts
    13
    Try this :

    Delete
    from table_name T1
    where T1.RowID >
    (select min(T2.RowID)
    from table_name T2
    where T1. Column_name =T2.column_name);


    Pands

  5. #5
    Join Date
    Feb 2001
    Posts
    184
    It could have been better to have a constraints, but no Problem.

    Please have the index on the columns that you need to fnd the duplicates, once you are done ypu can drop that index. This way you will save a huge time.

    spool duplicaterecords.sql
    create index idx_columnname on tablename (columnnames)
    /

    delete from tablename a
    where rowid not in (select max(rowid) from tablename b
    where a.duplicatedcolname = b.duplicatecolname
    and same for the other columns where you don't want to have duplicated columns.

    the spool off
    where.
    Tablename : your Table name
    Columnname : Your Columnname that needs to be searched for Duplicated values.

    Thanks..

  6. #6
    Join Date
    Oct 2000
    Posts
    211
    thanks everybody.
    Export/Import is out of question due to the size of database.
    I will follow what oracledba has suggested, that is creating index when necessary, deleting the duplicates and then dropping the index.
    manjunath

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