Re: De-duplication of large table

I am a novice to Oracle. I did think of that but was unsure because of the size of the thing. 43 million records, 407 char record length. Takes up about 7 gb of space plus two index columns taking up about 2gb.

The Column I want to de-duplicate on contains a recnum (Varchar2) which starts at 1 and goes to about 45,000,000. There are some gaps and some duplicates. I can probably generate a list of the duplicate numbers but am unsure how I would go about deleting the first occurrence of a duplicate record in the table using SQL.

Any suggestions would be much appreciated. Dave

Alan Shein <alanshein_at_erols.com> wrote in message news:811n6u$p4u$1_at_autumn.news.rcn.net...
> There are many ways, depending on other factors, but the easiest way is to
>
> CREATE TABLE newtable AS SELECT DISTINCT * FROM oldtable;
>
> then drop the oldtable and rename the newtable to the oldtable's name.
>
> There are some disadvantages to doing it this way, but, as I said, it
> depends on what you ultimately need to do.
>
> Dave <none_at_nowhere.nothere.oops> wrote in message
> news: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 - 14:47:14 CST