Deleting duplicate rows from a table.
A user had ~80,000 duplicate rows in a 350-million-row table after an HPL load aborted on space shortage and was restarted, reloading the same unload files. Suggestions included using rowid to keep the lowest rowid per key and delete the rest, unloading with SELECT DISTINCT/GROUP BY into a fresh table, or inserting into a copy with a unique index and trapping errors. The poster rejected these: no spare 150GB, no useful index for a self-join, and rowid tricks don't work on his 24-way fragmented table. Jonathan Leffler suggested finding duplicate key groups with GROUP BY/HAVING COUNT(*)>1 into a temp table, deleting all matching rows, then reinserting one copy, and processing fragment by fragment if fragmented by expression — but questioned whether simply dropping and reloading from good data would be faster. The poster was leaning toward dropping and reloading; no final outcome is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, I have got a big table containing about 350 million records. There are some duplicate records in the table ( around 80,000). Please suggest some way to delete the duplicate records keeping one copy of them. TIA, Manoj Background info : We loaded the data using HPL. Because of space crunch, the HPL stopped inbetween. After adding extra chunks, when we restarted it, it loaded the same unl files again which are causing the trouble. __________________________________ Do you Yahoo!? Yahoo! Mail - 250MB free storage. Do more. Manage less. http://info.mail.yahoo.com/mail_250
On Tue,
Jan 25, 2005 at 04:17:26AM -0500, manoj wadhwa wrote:
> Hi,
>
> I have got a big table containing about 350 million
> records. There are some duplicate records in the table
> ( around 80,000). Please suggest some way to delete
> the duplicate records keeping one copy of them.
Use the Row Ids to differentiate rows.
You may have to create a temp table to list the rows to delete
select pk, rowid into <tmp-table>
from <table> t1
where rowid > ( select min(rowid) from <table> where pk = t1.pk );
delete from <table>
where (pk, rowid) in ( select pk, rowid from <tmp-table> )
E&OE
HTH
--
Mark Thornber
=========================
E M Thornber CEng MIEE
Enchanted Systems Limited
Software Toolsmiths
+44 (0) 1503 272097
Hi, we use rowid column for did something like it. You can create another table with the same structure of original table and create a primary key or an unique index over that table. After that scan original table and insert records in the new table, with an exception block for cougth it. Paola Mensaje citado por manoj wadhwa <itm_manoj@yahoo.com>: > Hi, > > I have got a big table containing about 350 million > records. There are some duplicate records in the table > ( around 80,000). Please suggest some way to delete > the duplicate records keeping one copy of them. > > TIA, > Manoj > > Background info : We loaded the data using HPL. > Because of space crunch, the HPL stopped inbetween. > After adding extra chunks, when we restarted it, it > loaded the same unl files again which are causing the > trouble. > > > > > > __________________________________ > Do you Yahoo!? > Yahoo! Mail - 250MB free storage. Do more. Manage less. > http://info.mail.yahoo.com/mail_250 > > >
--------------Boundary-00=_06HWQL80000000000000
Content-Type: Multipart/Alternative;
boundary="------------Boundary-00=_06HWLVC0000000000000"
--------------Boundary-00=_06HWLVC0000000000000
Content-Type: Text/Plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi =0D
=0D
I faced the similar problem before.=0D
What i did was :-=0D
a. create a new table with similar table structure=0D
b.do select query from the table which u got the duplication=0D
=0D
example:-=0D
=0D
unload to /tmp/filename.unl=0D
select field_1, field_2 , field_3 . . . . .=0D
from table_name=0D
group by field_1, field_2 , field_3 . . . . .=0D=0D
* Note : assuming the duplication happen on all the columns. The unload w=
ill
only store non duplicate records.=0D
=0D
c. load the unload file into a new table=0D
=0D
Hope this will work.=0D
=0D
Bye=0D
=0D
=0D
=0D
=0D
=0D
=0D
=0D
Syed Ahmad Najmi Syed Md Nasir=0D
Senior System Analyst=0D
Century Software Malaysia Sdn Bhd=0D
57-5 Block G, Jalan PJU 1/37=0D
Dataran Prima=0D
47301 Petaling Jaya, Selangor=0D
Ph: (60 3) 7804 4464=0D
Fax: (60 3) 7804 4494=0D
http://www.CenturySoftware.com.au=0D
=0D
This E-mail from Century Software Pty Ltd expresses the views of the send=
er
and not necessarily the views of the Company. The E-mail and any=0D
files transmitted with it are confidential to the intended recipient at t=
he
E-mail address to which it has been addressed. The E-mail may not be=0D
disclosed or used by any other than the addressee, nor may it be copied i=
n
any way. If you are not the intended recipient please contact the sender =
as
soon as possible and delete any copies of this message. Please note that
although this E-mail has been checked, we cannot accept any responsibilit=
y
for any transmitted viruses. It is therefore your responsibility to virus
scan attachments (if any).=0D
=0D
=0D
-------Original Message-------=0D
=0D
From: pamadeo@cespi.unlp.edu.ar=0D
Date: 01/25/05 22:45:05=0D
To: ids@iiug.org=0D
Subject: Re: Deleting duplicate rows from a table. [4099]=0D
=0D
Hi, we use rowid column for did something like it.=0D
You can create another table with the same structure of original table an=
d=0D
create a primary key or an unique index over that table. After that scan=0D
original table and insert records in the new table, with an exception blo=
ck
for=0D
cougth it.=0D
=0D
Paola=0D
=0D
Mensaje citado por manoj wadhwa <itm_manoj@yahoo.com>:=0D
=0D
> Hi,=0D
>=0D
> I have got a big table containing about 350 million=0D
> records. There are some duplicate records in the table=0D
> ( around 80,000). Please suggest some way to delete=0D
> the duplicate records keeping one copy of them.=0D
>=0D
> TIA,=0D
> Manoj=0D
>=0D
> Background info : We loaded the data using HPL.=0D
> Because of space crunch, the HPL stopped inbetween.=0D
> After adding extra chunks, when we restarted it, it=0D
> loaded the same unl files again which are causing the=0D
> trouble.=0D
>=0D
>=0D
>=0D
>=0D
>=0D
> __________________________________=0D
> Do you Yahoo!?=0D
> Yahoo! Mail - 250MB free storage. Do more. Manage less.=0D
> http://info.mail.yahoo.com/mail_250=0D
>=0D
>=0D
>=0D
=0D
=0D
=0D
=2E
--------------Boundary-00=_06HWLVC0000000000000
Content-Type: Text/HTML;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-=
1">
<META content=3D"IncrediMail 1.0" name=3DGENERATOR></HEAD>
<BODY style=3D"BACKGROUND-POSITION: 0px 0px; FONT-SIZE: 12pt; MARGIN: 5px=
10px 10px; FONT-FAMILY: Arial" bgColor=3D#ffffff background=3D"" scroll=3D=
yes ORGYPOS=3D"0">
<TABLE id=3DINCREDIMAINTABLE cellSpacing=3D0 cellPadding=3D2 width=3D"100=
%" border=3D0>
<TBODY>
<TR>
<TD id=3DINCREDITEXTREGION style=3D"FONT-SIZE: 12pt; CURSOR: auto; FONT-F=
AMILY: Arial" width=3D"100%">
<DIV><FONT face=3D"Comic Sans MS" size=3D2>Hi </FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3D"Comic Sans MS" size=3D2>I faced the similar=
problem before.</FONT></DIV>
<DIV><FONT face=3D"Comic Sans MS" size=3D2>What i did was :-</FONT></DIV>
<DIV><FONT face=3D"Comic Sans MS" size=3D2>a. create a new table wit=
h similar table structure</FONT></DIV>
<DIV><FONT face=3D"Comic Sans MS" size=3D2>b.do select query from the tab=
le which u got the duplication</FONT></DIV>
<DIV><FONT face=3D"Comic Sans MS" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Comic Sans MS" size=3D2>example:-</FONT></DIV>
<DIV><FONT face=3D"Comic Sans MS" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Comic Sans MS" size=3D2>unload to /tmp/filename.=
unl</FONT></DIV>
<DIV><FONT face=3D"Comic Sans MS" size=3D2>select field_1, field_2 , fiel=
d_3 . . . . .</FONT></DIV>
<DIV><FONT face=3D"Comic Sans MS" size=3D2>from table_name</FONT></DIV>
<DIV><FONT face=3D"Comic Sans MS" size=3D2>group by field_1, field_2 , fi=
eld_3 . . . . .</FONT></DIV>
<DIV><FONT face=3D"Comic Sans MS" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Comic Sans MS" size=3D2>* Note : assuming the duplicat=
ion happen on all the columns. The unload will only store non duplicate r=
ecords.</FONT></DIV>
<DIV><FONT face=3D"Comic Sans MS" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Comic Sans MS" size=3D2>c. load the unload file i=
nto a new table</FONT></DIV>
<DIV><FONT face=3D"Comic Sans MS" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Comic Sans MS" size=3D2>Hope this will work.</FONT></D=
IV>
<DIV><FONT face=3D"Comic Sans MS" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Comic Sans MS" size=3D2>Bye</FONT></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><FONT face=3D"Comic Sans MS" size=3D2></FONT> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><IMG id=3DINCREDI_SIGIMG src=3D"cid:58C56123-90FC-41F0-8CC7-16ACAC35=
AB34"></DIV>
<DIV><SPAN style=3D"FONT-SIZE: 10pt">
<DIV>
<P><SPAN style=3D"FONT-SIZE: 10pt"><FONT size=3D1><STRONG><FONT style=3D"=
BACKGROUND-COLOR: #ffff00" color=3D#fe0000>Syed</FONT> <FONT style=3D"BAC=
KGROUND-COLOR: #ffff00" color=3D#fe0000>Ahmad</FONT> <FONT style=3D"BACKG=
ROUND-COLOR: #ffff00" color=3D#fe0000>Najmi</FONT> <FONT style=3D"BACKGRO=
UND-COLOR: #ffff00" color=3D#fe0000>Syed</FONT> <FONT style=3D"BACKGROUND=
-COLOR: #ffff00" color=3D#fe0000>Md</FONT> <FONT style=3D"BACKGROUND-COLO=
R: #ffff00" color=3D#fe0000>Nasir</FONT><BR></STRONG>Senior System Analys=
t<BR>Century Software Malaysia <FONT style=3D"BACKGROUND-COLOR: #ffff00" =
color=3D#fe0000>Sdn</FONT> <FONT style=3D"BACKGROUND-COLOR: #ffff00" colo=
r=3D#fe0000>Bhd</FONT><BR>57-5 Block G, <F
Ok. I'll take it. What's the procedure? J. -----Original Message----- From: "manoj wadhwa " <itm_manoj@yahoo.com> To: ids@iiug.org Date: Tue, 25 Jan 2005 04:17:26 -0500 (EST) Subject: Deleting duplicate rows from a table. [4097] Hi, I have got a big table containing about 350 million records. There are some duplicate records in the table ( around 80,000). Please suggest some way to delete the duplicate records keeping one copy of them. TIA, Manoj Background info : We loaded the data using HPL. Because of space crunch, the HPL stopped inbetween. After adding extra chunks, when we restarted it, it loaded the same unl files again which are causing the trouble. __________________________________ Do you Yahoo!? Yahoo! Mail - 250MB free storage. Do more. Manage less. http://info.mail.yahoo.com/mail_250 Jean Sagi jeansagi@myrealbox.com jeansagi@yahoo.com
Hi All, Thanks alot for the suggestions. Three major problems that i faced is : a) Creation of another table and loading that with unique rows from first table would require me to allocate another 150 GB of diskspace which i dont' have. 2) Making self join for a table containing 350 million records, with no meaningfull index present, will take lot of time. 3) Since the table is fragmented (24 frags), rowid solutions also don't work. currently i'm planning to drop the table and recreate the indexes. Would appreciate any other solution/suggestions. Thanks alot, Manoj --- Bob Allan <allan_bob@hotmail.com> wrote: > Unload the table selecting using the select unique * > from table-a > drop table > recreate table > reload info. > > If you cannot do that then create another table with > the same field > definition and insert into that table selecting > unique from the origirnal > table. > Then drop original table and rename new table to old > table > > > >From: "manoj wadhwa " <itm_manoj@yahoo.com> > >To: ids@iiug.org > >Subject: Deleting duplicate rows from a table. > [4097] Date: Tue, 25 Jan > >2005 04:17:26 -0500 (EST) > > > >Hi, > > > >I have got a big table containing about 350 million > >records. There are some duplicate records in the > table > > ( around 80,000). Please suggest some way to > delete > >the duplicate records keeping one copy of them. > > > >TIA, > >Manoj > > > >Background info : We loaded the data using HPL. > >Because of space crunch, the HPL stopped > inbetween. > >After adding extra chunks, when we restarted it, it > >loaded the same unl files again which are causing > the > >trouble. > > > > > > > > > > > >__________________________________ > >Do you Yahoo!? > >Yahoo! Mail - 250MB free storage. Do more. Manage > less. > >http://info.mail.yahoo.com/mail_250 > > > > > __________________________________ Do you Yahoo!? Yahoo! Mail - now with 250MB free storage. Learn more. http://info.mail.yahoo.com/mail_250
Various
comments - not all helpful.
First off, when the HPL failed, why on earth didn't you undo the previous
broken load before completing the reload? This would have avoided the
problem.
Secondly, are you sure it isn't the quickest way to deal with the problem
- drop the existing data and reload empty tables from good data?
Next - are your tables fragmented by round robin or by expression?
If you've got round robin, you've made life very difficult. It isn't much
easier with expressions, but at least you can divide your data into 24
smaller subsets that are processed in turn - you know that whenever
duplicates exist, they are found in the same fragment.
You've presumably got fairly significant numbers of duplicate rows - you
did a whole lot of loading, then had to add a bunch more space, etc.
What are your primary keys? I'm guessing you don't have indexes in place
because unique indexes would have prevented the chaos.
Identifying the rows with duplicates will require a statement such as:
SELECT COUNT(*), a, b, c, ..., z
FROM YourTable
HAVING COUNT(*) > 1;
I'd probably arrange to have enough space available to store these into a
temp table. Then you've got the DUP rows, and one copy of the dups in the
temp table, so you can do a delete. That may be tricky too, but would
involve selecting rows from the main table that match the values in the
temp table:
DELETE FROM YourTable
WHERE EXISTS (SELECT * FROM TempTable
WHERE YourTable.A = TempTable.A
AND YourTable.B = TempTable.B
AND ...
AND YourTable.Z = TempTable.Z);
You can optimize the condition to include just the 'should be primary key'
columns.
This gives you a table with no instances of the duplicates at all.
You can now transfer everything except the count column from the temp
table back into the main table.
With expression fragmentation, you can apply the fragment filters and deal
with small portions of the table at once. With round robin fragmentation,
you probably have to deal with everything. Don't forget to create an
index on the temp table and to run update statistics on it. You might
want to do an update statistics on the big table too - at least LOW. I
hope you've got lots of logical log space.
Are you sure this is going to be quicker than reloading the data?
--
Jonathan Leffler (jleffler@us.ibm.com)
STSM, Informix Database Engineering, IBM Information Management Division
4100 Bohannon Drive, Menlo Park, CA 94025
Tel: +1 650-926-6921 Tie-Line: 630-6921
"I don't suffer from insanity; I enjoy every minute of it!"
forum.subscriber@iiug.org wrote on 01/28/2005 04:13:15 AM:
> Thanks alot for the suggestions.
> Three major problems that i faced is :
> a) Creation of another table and loading that with
> unique rows from first table would require me to
> allocate another 150 GB of diskspace which i dont'
> have.
> 2) Making self join for a table containing 350 million
> records, with no meaningfull index present, will take
> lot of time.
> 3) Since the table is fragmented (24 frags), rowid
> solutions also don't work.
>
> currently i'm planning to drop the table and recreate
> the indexes. Would appreciate any other
> solution/suggestions.
>
> Thanks alot,
> Manoj
> --- Bob Allan <allan_bob@hotmail.com> wrote:
>
> > Unload the table selecting using the select unique *
> > from table-a
> > drop table
> > recreate table
> > reload info.
> >
> > If you cannot do that then create another table with
> > the same field
> > definition and insert into that table selecting
> > unique from the origirnal
> > table.
> > Then drop original table and rename new table to old
> > table
> >
> >
> > >From: "manoj wadhwa " <itm_manoj@yahoo.com>
> > >To: ids@iiug.org
> > >Subject: Deleting duplicate rows from a table.
> > [4097] Date: Tue, 25 Jan
> > >2005 04:17:26 -0500 (EST)
> > >
> > >I have got a big table containing about 350 million
> > >records. There are some duplicate records in the
> > >table (around 80,000). Please suggest some way to
> > >delete the duplicate records keeping one copy of them.
> > >
> > >
> > >Background info : We loaded the data using HPL.
> > >Because of space crunch, the HPL stopped
> > >in between.
> > >After adding extra chunks, when we restarted it, it
> > >loaded the same unl files again which are causing
> > >the trouble.