Oracle-L: Re: Remove Duplicates

From: Stephane Faroult <sfaroult_at_oriole.com>
Date: Tue, 04 Jun 2002 13:55:40 -0800
Message-ID: <F001.00474402.20020604135540@fatcity.com>

Ferenc,

I agree with your improvement on the first query but I disagree about your opinion about the EXCEPTIONS method. Especially if there are not too many (in proportion) duplicates you can remove them as follows :

       create table my_table_dup
       as select distinct *
          from my_table
          where rowid in (select row_id from exceptions);
       delete my_table
       where rowid in (select row_id from exceptions);
       insert into my_table
       select * from my_table_dup;

Granted, not a single shot, but fairly straighforward and relatively easy to follow.

The big advantage here is that you do one sort (the SELECT DISTINCT) but it is performed on the limited subset of the duplicate rows. All statements involved perform fast, and the current indexing is pretty irrelevant. Concerning the ADD CONSTRAINT itself, this is the kind of operation where the Oracle kernel guys usually write good code.
--

Regards,

Stephane Faroult
Oriole Software
--

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

Author: Stephane Faroult
INET: sfaroult_at_oriole.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Jun 04 2002 - 16:55:40 CDT