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 (<unique_columns list>) exceptions into exceptions
/
- drop table dupes
/
create table dupes as
select <unique_columns list>, 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 (<unique_columns list>) 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" <Rajendra.Jamadagni_at_espn.com> 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
> [mailto: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
-----------------------------------------------------------------
Received on Wed Jun 02 2004 - 10:23:50 CDT
