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" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
