RE: Duplicate rows
- From: Rachel Carmichael <wisernet100@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 2 Jun 2004 08:26:31 -0700 (PDT)
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.
1) 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.
2) build a small table of columns that are unique plus the rowid from
the source table, selecting rowids from the exceptions table
3) delete the duplicates in the smaller table (runs a LOT faster than
trying to delete the duplicates from the source)
4) delete the rows in the source table where the rowid is in the
smaller table
5) reenable unique constraint to verify duplicates gone and ensure they
don't come back
6) 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
1) alter table source_table add constraint ux_constraint
UNIQUE (<unique_columns list>) exceptions into exceptions
/
2) 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@xxxxxxxx> 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@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Natural Join B.V.
> Sent: Wednesday, June 02, 2004 9:21 AM
> To: oracle-l@xxxxxxxxxxxxx; 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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
