Xref: alice comp.databases.oracle.tools:27193
Path: alice!news-feed.fnsi.net!news.maxwell.syr.edu!news.algonet.se!newsfeed1.telenordia.se!algonet!news.man.lodz.pl!news.man.poznan.pl!polsl.gliwice.pl!not-for-mail
From: Piotrek <piotrek@wasko.gliwice.pl>
Newsgroups: comp.databases.oracle.tools
Subject: Re: removing duplicates rows
Date: Fri, 25 Jun 1999 09:15:07 +0200
Organization: Politechnika Slaska, Gliwice
Lines: 50
Message-ID: <37732C7B.375153AE@wasko.gliwice.pl>
References: <gjAc3.5747$d5.725031@news21b.ispnews.com> <19990625004144.21783.00001519@ng-cg1.aol.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Mailer: Mozilla 4.5 [en] (X11; I; SunOS 5.6 sun4u)
X-Accept-Language: en

CJJITP wrote:

> <<From: "al" <asalvado@ev1.net>
> Date: Thu, 24 June 1999 08:39 PM EDT
> Message-id: <gjAc3.5747$d5.725031@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@aol.com

is it good for table with ~ 20 mln records ?