FAQ
Dear All,

One table is having more than 5000 duplicate record. We would like to remove
all the duplicate records.

Using rowid method, it takes much time to execute.

Is there any other way to remove all the duplicate rows in a faster manner.

Regards,
Balu
This mail is scaned by eScan Anti Virus Software

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.

Search Discussions

  • Natural Join B.V. at Jun 2, 2004 at 6:17 am
    CTASD (create table as select distinct) followed by a DROP and a RENAME?
    Dear All,

    One table is having more than 5000 duplicate record. We would like to remove
    all the duplicate records.

    Using rowid method, it takes much time to execute.
    Is there any other way to remove all the duplicate rows in a faster
    manner.

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Peter Robson at Jun 2, 2004 at 10:20 am
    I'm not sure why, but this here message is coming in with tomorrows
    date.....

    peter

    Thursday, June 3, 2004, 3:20:53 AM, you wrote:

    NJBV> CTASD (create table as select distinct) followed by a DROP and a RENAME?
    Dear All,

    One table is having more than 5000 duplicate record. We would like to remove
    all the duplicate records.
    --
    mailto:pgro_at_bgs.ac.uk

    *********************************************************************
    This e-mail message, and any files transmitted with it, are
    confidential and intended solely for the use of the addressee. If
    this message was not addressed to you, you have received it in error
    and any copying, distribution or other use of any part of it is
    strictly prohibited. Any views or opinions presented are solely those
    of the sender and do not necessarily represent those of the British
    Geological Survey. The security of e-mail communication cannot be
    guaranteed and the BGS accepts no liability for claims arising as a
    result of the use of this medium to transmit messages from or to the
    BGS. . http://www.bgs.ac.uk
    *********************************************************************

    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Jamadagni, Rajendra at Jun 2, 2004 at 6:30 am
    And then by doing CTASD you will loose all grants,synonyms etc ... I
    wonder how long it would take to delete 5000 rows. Create unique
    constraint and catch exceptions into an exceptions table (created with
    $OH/rdbms/admin/utlexcpt.sql) ... From there on it should be easy ...

    Raj

    Rajendra dot Jamadagni at nospamespn dot com
    All Views expressed in this email are strictly personal.
    select standard_disclaimer from company_requirements;
    QOTD: Any clod can have facts, having an opinion is an art !

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Natural Join B.V.
    Sent: Wednesday, June 02, 2004 9:21 AM
    To: oracle-l_at_freelists.org; ORALCE LIST
    Subject: Re: Duplicate rows

    CTASD (create table as select distinct) followed by a DROP and a RENAME?
    Dear All,

    One table is having more than 5000 duplicate record. We would like to
    remove all the duplicate records.

    Using rowid method, it takes much time to execute.
    Is there any other way to remove all the duplicate rows in a faster
    manner.

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Rachel Carmichael at Jun 2, 2004 at 10:23 am
    depends on if you want to remove ALL rows that are duplicates or just
    remove the second, third etc...

    since I JUST did this as part of a conversion from one app to another,
    it's slightly more involved but not really too hard.

    create exception table and unique constraint, try to enable
    constraint with exceptions into exception table. This gets BOTH sides
    of the duplication into the exception table.

    build a small table of columns that are unique plus the rowid from
    the source table, selecting rowids from the exceptions table

    delete the duplicates in the smaller table (runs a LOT faster than
    trying to delete the duplicates from the source)

    delete the rows in the source table where the rowid is in the
    smaller table

    reenable unique constraint to verify duplicates gone and ensure they
    don't come back

    figure out how the heck you got duplicates in in the first place!

    Sample code below:

    NOTE: when I do this, I do rowcounts of all tables in between each DML
    statement, just to verify that I'm deleting the right number of rows

    alter table source_table add constraint ux_constraint
    UNIQUE () exceptions into exceptions
    /

    drop table dupes
    /

    create table dupes as
    select, rowid myrowid from source_table
    where rowid in (select row_id from exceptions)
    /

    truncate table exceptions
    /

    3) delete from dupes a where rowid > (select min(rowid)
    from dupes b
    where a.unique_column1=b.unique_column1
    and a.unique_column2=b.unique_column2
    .... repeat above for all columns
    )
    /

    4) delete from source_table s where s.rowid in (select myrowid from
    dupes);

    5) alter table source_table add constraint ux_constraint
    UNIQUE () exceptions into exceptions
    /

    6) that one YOU have to figure out.... in my case, the original app did
    not have constraints on it. Bless those 3rd party apps written for
    "generic" databases!

    Rachel

    "Jamadagni, Rajendra" wrote:
    And then by doing CTASD you will loose all grants,synonyms etc ... I
    wonder how long it would take to delete 5000 rows. Create unique
    constraint and catch exceptions into an exceptions table (created
    with
    $OH/rdbms/admin/utlexcpt.sql) ... From there on it should be easy ...


    Raj
    --------
    Rajendra dot Jamadagni at nospamespn dot com
    All Views expressed in this email are strictly personal.
    select standard_disclaimer from company_requirements;
    QOTD: Any clod can have facts, having an opinion is an art !

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Natural Join B.V.
    Sent: Wednesday, June 02, 2004 9:21 AM
    To: oracle-l_at_freelists.org; ORALCE LIST
    Subject: Re: Duplicate rows

    CTASD (create table as select distinct) followed by a DROP and a
    RENAME?
    Dear All,

    One table is having more than 5000 duplicate record. We would like to
    remove all the duplicate records.

    Using rowid method, it takes much time to execute.
    Is there any other way to remove all the duplicate rows in a faster
    manner.

    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
    Do you Yahoo!?
    Friends. Fun. Try the all-new Yahoo! Messenger.
    http://messenger.yahoo.com/

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • David Kurtz at Jun 2, 2004 at 6:47 am
    Presumably you have a delete statement that looks something like

    DELETE FROM t1
    WHERE EXISTS(

    SELECT 'x'
    FROM t2
    WHERE t1. = t2.
    AND t1.rowid < t2.rowid
    AND rownum = 1);

    A non-unique index on the key fields should improve the performace of this
    query.

    How did the non-unique data get in there in the first place? Is there any
    condition on t1 that you could use to cut down the number of rows that the
    driving query will return? If your are trying to delete 5000 out of
    millions of rows then this isn't going to be quick either.

    David Kurtz
    Go-Faster Consultancy Ltd.
    tel: +44 (0)7771 760660
    fax: +44 (0)7092 348865
    mailto:david.kurtz_at_go-faster.co.uk
    web: www.go-faster.co.uk
    PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Computer Centre -
    NIIPL

    Sent: 02 June 2004 10:57
    To: ORALCE LIST
    Subject: Duplicate rows

    Dear All,

    One table is having more than 5000 duplicate record. We would like to remove
    all the duplicate records.

    Using rowid method, it takes much time to execute.

    Is there any other way to remove all the duplicate rows in a faster manner.

    Regards,
    Balu
    This mail is scaned by eScan Anti Virus Software

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
  • Jacques Kilchoer at Jun 3, 2004 at 7:04 pm
    If you have more than two duplicate rows, the ">" in step 3) should be
    an "=".

    -----Original Message-----
    Rachel Carmichael
    ...

    3) delete the duplicates in the smaller table (runs a LOT faster than
    trying to delete the duplicates from the source)

    ...

    Sample code below:

    ...

    JRK> with a ">" in the condition below you would end up listing
    JRK> only one of the duplicate rows in the "dupes" table

    3) delete from dupes a where rowid = (select min(rowid)
    from dupes b
    where a.unique_column1=b.unique_column1
    and a.unique_column2=b.unique_column2
    .... repeat above for all columns
    )
    /

    ...

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Seema Singh at Jun 30, 2004 at 5:18 pm
    Here is
    select count(test_id) from test;
    shows 102 rows

    select count(distinct(test_id) from test; shows 100 rows

    How to find those 2 rows ?
    I tried to run to find by using max rowid or min rowid but unable to.if some
    one have quesry can pl send to me
    thx

    Get fast, reliable Internet access with MSN 9 Dial-up � now 3 months FREE!
    http://join.msn.click-url.com/go/onm00200361ave/direct/01/

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
  • Bricklen Anderson at Jun 30, 2004 at 5:25 pm

    Seema Singh wrote:

    Here is
    select count(test_id) from test;
    shows 102 rows

    select count(distinct(test_id) from test; shows 100 rows

    How to find those 2 rows ?
    I tried to run to find by using max rowid or min rowid but unable to.if some
    one have quesry can pl send to me
    thx
    try
    select test_id,count(*)
    from test
    having count(*)>1
    group by test_id;

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Mark W. Farnham at Jun 30, 2004 at 5:25 pm
    select test_id, count(test_id) from test group by test_id having count > 1;

    then for the individual test_id problems, look at them and figure out which
    one to delete by row_id.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Seema Singh
    Sent: Wednesday, June 30, 2004 6:22 PM
    To: oracle-l_at_freelists.org
    Subject: DUPLICATE ROWS.

    Here is
    select count(test_id) from test;
    shows 102 rows

    select count(distinct(test_id) from test; shows 100 rows

    How to find those 2 rows ?
    I tried to run to find by using max rowid or min rowid but unable to.if some
    one have quesry can pl send to me
    thx

    Get fast, reliable Internet access with MSN 9 Dial-up – now 3 months FREE!
    http://join.msn.click-url.com/go/onm00200361ave/direct/01/

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
  • Muqthar Ahmed at Jun 30, 2004 at 5:31 pm
    Seema,

    Tom Kyte showed different examples....look for Removing Duplicates:

    http://otn.oracle.com/oramag/oracle/04-jul/o44asktom.html

    Muqthar
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Seema Singh
    Sent: Wednesday, June 30, 2004 6:22 PM
    To: oracle-l_at_freelists.org
    Subject: DUPLICATE ROWS.

    Here is
    select count(test_id) from test;
    shows 102 rows

    select count(distinct(test_id) from test; shows 100 rows

    How to find those 2 rows ?
    I tried to run to find by using max rowid or min rowid but unable to.if some
    one have quesry can pl send to me
    thx

    Get fast, reliable Internet access with MSN 9 Dial-up - now 3 months FREE!
    http://join.msn.click-url.com/go/onm00200361ave/direct/01/

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

    * * * * * * * *

    The information contained in this E-mail message is privileged, confidential, and may be protected from disclosure; please be aware that any other use, printing,copying,
    disclosure or dissemination of this communication may be subject to legal restriction or sanction. If you think that you have received this E-mail message in error, please
    reply to the sender and delete it from your computer. Thank you.

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
  • Kowdley, Chandra Mohan at Jun 30, 2004 at 9:00 pm
    try this

    select * from test where test_id in ( select test_id from test group by
    test_id having count(*) > 2 )

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Seema Singh
    Sent: Thursday, July 01, 2004 6:22 AM
    To: oracle-l_at_freelists.org
    Subject: DUPLICATE ROWS.

    Here is
    select count(test_id) from test;
    shows 102 rows

    select count(distinct(test_id) from test; shows 100 rows

    How to find those 2 rows ?
    I tried to run to find by using max rowid or min rowid but unable to.if some
    one have quesry can pl send to me
    thx

    Get fast, reliable Internet access with MSN 9 Dial-up - now 3 months FREE!
    http://join.msn.click-url.com/go/onm00200361ave/direct/01/

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

    This message is for the sole use of the intended recipient. If you received
    this message in error please delete it and notify us. If this message was
    misdirected, CSFB does not waive any confidentiality or privilege. CSFB
    retains and monitors electronic communications sent through its network.
    Instructions transmitted over this system are not binding on CSFB until they
    are confirmed by us. Message transmission is not guaranteed to be secure.

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
  • Karen Morton at Jun 30, 2004 at 9:25 pm
    Another way to find/remove duplicate rows is:

    SELECT test_id
    FROM test t
    WHERE t.rowid >
    (SELECT MIN(t1.rowid)

    FROM test t1
    WHERE t.test_id=t1.test_id);

    Then, if you wanted to get rid of the dups, just DELETE:
    DELETE FROM test t
    WHERE t.rowid >
    (SELECT MIN(t1.rowid)

    FROM test t1
    WHERE t.test_id=t1.test_id);




    Karen Morton
    Hotsos Enterprises, Ltd.
    http://www.hotsos.com

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Seema Singh
    Sent: Thursday, July 01, 2004 6:22 AM
    To: oracle-l_at_freelists.org
    Subject: DUPLICATE ROWS.

    Here is
    select count(test_id) from test;
    shows 102 rows

    select count(distinct(test_id) from test; shows 100 rows

    How to find those 2 rows ?
    I tried to run to find by using max rowid or min rowid but unable to.if
    some
    one have quesry can pl send to me
    thx

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------

Related Discussions

People

Translate

site design / logo © 2022 Grokbase