Re: duplicate rows in big table

In article <G04B63.4HD_at_news.boeing.com>,   "James" <jialong.x.xie_at_boeing.com> wrote:
> I have a table, which has more than one million rows but a few
 duplicates.
> Is there any quick way to single out these duplicates? The normal way
 that
> compares rowid by rowid is too time consuming. Any help will be
 appreciated.
>
> James
>
>

i.e. assume emp_id is primary key in emp table

create table tmp1 nologging as (
select emp_id, min(rowid) id from emp
group by emp_id
having count(*) > 1);

might want index in tmp1?

create unique index tmp1_ind on tmp1(id);

delete from emp a where exists (select 'x' from tmp1 b where a.rowid = b.id);

if your sure!
commit;

-Ethan
http://www.freetechnicaltraining.com
http://www.gnumetrics.com (another free oracle performance monitor)

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Aug 30 2000 - 14:46:07 CDT