Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> help with deleting duplicate records from very large table

help with deleting duplicate records from very large table

From: Suhen Pather <Suhen.Pather_at_strandbags.com.au>
Date: Tue, 11 Sep 2001 21:21:02 -0700
Message-ID: <F001.0038AD6F.20010911205018@fatcity.com>

<span style='font-size:10.0pt;

font-family:Arial'>List,

<span style='font-size:10.0pt;

font-family:Arial'> 

<span style='font-size:10.0pt;

font-family:Arial'>I need to delete duplicate records from a very large table (60 millions records +).

<span style='font-size:10.0pt;

font-family:Arial'>There would be about 3 million duplicate entries.

<span style='font-size:10.0pt;

font-family:Arial'> 

<span style='font-size:10.0pt;

font-family:Arial'>What is the quickest way to do this?

<span style='font-size:10.0pt;

font-family:Arial'> 

<span style='font-size:10.0pt;

font-family:Arial'>The syntax that I am using is

<span

style='font-size:10.0pt;font-family:Arial'>delete<font face=Arial> from invaudee

<span

style='font-size:10.0pt;font-family:Arial'>where<font face=Arial> rowid not in (select min(rowid) from invaudee

<span

style='font-size:10.0pt;font-family:Arial'>group<font face=Arial> by audit_number);

<span style='font-size:10.0pt;

font-family:Arial'> 

<span style='font-size:10.0pt;

font-family:Arial'>This is taking a long time to run. I cannot see any entries in v$transaction

<span style='font-size:10.0pt;

font-family:Arial'>for the delete.

<span style='font-size:10.0pt;

font-family:Arial'> 

<span style='font-size:10.0pt;

font-family:Arial'>There is no indexes on the INVAUDEE table.

<span style='font-size:10.0pt;

font-family:Arial'>I created an index on the primary key column but it still takes forever to run.

<span style='font-size:10.0pt;

font-family:Arial'> 

<span style='font-size:10.0pt;

font-family:Arial'>I do not have the space to CTAS.

<span style='font-size:10.0pt;

font-family:Arial'> 

<span style='font-size:10.0pt;

font-family:Arial'>Or should I write the duplicates to an EXCEPTIONS table and perform the delete based on the entries

<span

style='font-size:10.0pt;font-family:Arial'>in<font face=Arial> the EXCEPTIONS table.

<span style='font-size:10.0pt;

font-family:Arial'> 

<span style='font-size:10.0pt;

font-family:Arial'>Any help would be greatly appreciated.

<span style='font-size:10.0pt;

font-family:Arial'> 

<span style='font-size:10.0pt;

font-family:Arial'>Suhen

<span style='font-size:10.0pt;

font-family:Arial'>  Received on Tue Sep 11 2001 - 23:21:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US