Re: removing duplicates rows

CJJITP wrote:

> <<From: "al" <asalvado_at_ev1.net>
> Date: Thu, 24 June 1999 08:39 PM EDT
> Message-id: <gjAc3.5747$d5.725031_at_news21b.ispnews.com>
>
> If a tables rows are accidently duplicated, how do you remove duplicates?
>
> 1st you can count the rows, and then use this count in the following SQL:
> DELETE: FROM table
> WHERE rownum < ( (row_count/2) + 1);
>
> This works only if you duplicated the rows all at once. What's the proper
> way of doing it?
>
> Thanks.
> >>
>
> You have not stated the table size or the number of dups. The DELETE statement
> after a fashion would work, but may create some serious performance problems.
> The most direct way would be something like:
>
> create table new_tbl_nm
> .... storage parameters ....
> unrecoverable parallel(degree 3)
> as
> select /*+ parallel(a,3) */
> distinct
> *
> from old_tbl_nm a
> /
> rename old_tbl_nm to old_tbl_nm_backup
> /
> rename new_tbl_nm to old_tbl_nm
> /
>
> After you are sure that everything is OK, then and only then:
>
> DROP TABLE
>
> Cecil J. Jones
> Chief Technologist
> Information Technology Paradigms, Incorporated
> Data Warehouse, Data Marts and
> Decision Enabling Technology Consultants
> (415) 454-9424 -- Fax (415) 454-9489
> E-Mail: cjjitp_at_aol.com

is it good for table with ~ 20 mln records ? Received on Fri Jun 25 1999 - 09:15:07 CEST