Re: Eliminating duplicates

In article <2phqp3$g95_at_search01.news.aol.com>, DRathbun <drathbun_at_aol.com> wrote:
>Create table newtable
>as
>select distinct (field1, field2, field3... fieldn)
>from oldtable
>/
>drop oldtable
>/
>rename newtable to oldtable
>/
>This is an easy enough trick to use, and also releases the free space used by
>the extra rows added in the original table; the resulting newtable is only as
>large as allocated (or needed to hold the data).

    You must remember to redo the GRANTs which were made on the old table. Furthermore, as has been mentioned elsewhere, you may run into a rollback segment problem if the table is large. (This problem is even more likely for single SQL command DELETE statements.

    I normally eliminate duplicates via a PL/SQL program that loops through a cursor with the rows ordered by primary key, remembering the key of the previous row and deleting the current row if it has the same key. Periodic COMMITs can be done to avoid the rollback segment problem.

-- 
Mike Stein			The above represents the Absolute Truth.
POB 10420			Therefore it cannot possibly be the official
Arlington, VA  22210		position of my employer.