| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> help with deleting duplicate records from very large table
<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
 |
 |