Re: De-duplication of large table

From: no spam <no_at_spam.com>
Date: Thu, 18 Nov 1999 22:37:32 GMT
Message-ID: <Mc%Y3.32981$m4.112818003@news.magma.ca>

--to delete duplicates delete highest rowid for each recnum

delete from test a
where rowid = ( select max(rowid)

                             from test b
                              where b.rec_num = a.rec_num )

i would recommend doing the following first to check the duplicates for other problems

CREATE TABLE EXCEPTIONS( row_id rowid,

                                                           owner
varchar2(3),
                                                           table_name
varchar2(30),
                                                           constarint

varchar2(30))
- utlexcpt.sql script in admin directory will do this

ALTER TABLE TEST
 ENABLE CONSTRAINT PK_TEST
EXCEPTIONS INTO EXCEPTIONS; SELECT *
FROM TEST t, EXCEPTIONS e
WHERE t.rowid = x.rowid
- this will list both duplicate records and allow you to check there contents

Dave wrote in message <811m34$na5$1_at_lure.pipex.net>...
>I have a 43,000,000 row table with about 225,000
>duplicate records.
>What is the easiest way to remove the duplicates.
>I want to remove one of every duplicate record
>from the table.
>
>Dave
>
>
>
Received on Thu Nov 18 1999 - 16:37:32 CST