Re: Remove Duplicates

From: Rachel Carmichael <wisernet100_at_yahoo.com>
Date: Tue, 04 Jun 2002 11:44:08 -0800
Message-ID: <F001.00473F2C.20020604114408@fatcity.com>

your second query will identify the rowids that are duplicates but will not delete them. You'd have to then go through the exceptions table to actually delete the rows.

however, the exceptions table is a good way to get an idea of how many rows will be deleted. If you do have a large number of rows to be deleted that might cause either out of space on the rollback segment or an ora-1555, you can loop through that table and do intermittent commits while deleting. The first query is an "all or nothing" situation.

I've used both ways, but tend to use the delete when it's a small table.

• "Terrian, Tom" <tterrian_at_daas.dla.mil> wrote:
  > I know I have seen this posted before.......
  >
  >
  >
  > We have a large range partitioned table that has duplicates in it.
  > What is the
  > fastest way to remove the dups.? I have the following scripts which
  > do it but
  > may be fast or slow. What do you guys use?
  >
  > DELETE FROM tablename
  > WHERE ROWID NOT IN
  > (SELECT MIN(ROWID)
  > FROM tablename
  > GROUP BY fieldnames);
  >
  > Or
  >
  > alter table &table_name
  > add constraint duplicate_cons
  > unique key (&column_name)
  > exceptions into exception table;
  >
  > How to find duplicates:
  >
  > select &column_name, count(&column_name)
  > from &table_name
  > group by &column_name
  > having count(&column_name) > 1;
  >
  >
  >
  > Tom
  >
  >
  >
  >
  

  ---

Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  INET: wisernet100_at_yahoo.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 - 14:44:08 CDT