What is the fastest way to delete duplicate rows from a table?
Poster on Online 7.x asked for the fastest way to remove duplicate rows without relying on rowids, since the table might be fragmented. Replies: fragmented tables lose unique rowids unless created WITH ROWIDS, so the real fix is giving every table a unique key. Suggested approaches were SELECT DISTINCT * INTO TEMP, then delete/reload (or drop and recreate the table from a dbschema script), or, if rowids exist, a GROUP BY ... HAVING COUNT(*)>1 temp table joined back and deleting by min(rowid). Thread ends with joking about temp-table naming.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Online 7.x What is the fastest way to delete duplicate rows from a table? I don't really want anything that relies on rowids, because the table may eventually be fragmented. Thanks :) Sent via Deja.com http://www.deja.com/ Before you buy.
First off, one should always maintain a unique value within a table. The reliance on rowid is overused since, as you mention, rowid within a fragmented table is not unique. You can, of course, use the syntax WITH ROWID when you fragment the table to give it an unique value once again. Take care. Clifton Bean <strangiato@my-deja.com> wrote in message news:8i2v71$80v$1@nnrp1.deja.com... > Online 7.x > > What is the fastest way to delete duplicate rows from a table? > > I don't really want anything that relies on rowids, because the table > may eventually be fragmented. > > Thanks :) > > > Sent via Deja.com http://www.deja.com/ > Before you buy.
For fragmented table use
BEGIN WORK;
SELECT DISTINCT * FROM Table INTO TEMP Temp1;
DELETE FROM Table WHERE 1 = 1;
INSERT INTO Table SELECT * FROM Temp1;COMMIT WORK;
Other Way can be
create schema for the table using dbschema -d database -t table -ss
create.sql on unix prompt
Then run
SELECT DISTINCT * FROM Table INTO TEMP Temp1;
Drop table TableRecreate table using create.sql
INSERT INTO Table SELECT * FROM Temp1;
Sameer Tumbde
Clifton M. Bean <cmbean@home.com> wrote in message
news:z_d15.246$x_6.15137@news1.rdc2.tx.home.com...
> First off, one should always maintain a unique value within a table. The
> reliance on rowid is overused since, as you mention, rowid within a
> fragmented table is not unique.
>
> You can, of course, use the syntax WITH ROWID when you fragment the table
to
> give it an unique value once again.
>
> Take care.
> Clifton Bean
>
>
> <strangiato@my-deja.com> wrote in message
> news:8i2v71$80v$1@nnrp1.deja.com...
> > Online 7.x
> >
> > What is the fastest way to delete duplicate rows from a table?
> >
> > I don't really want anything that relies on rowids, because the table
> > may eventually be fragmented.
> >
> > Thanks :)
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>
strangiato@my-deja.com wrote:
>
> Online 7.x
>
> What is the fastest way to delete duplicate rows from a table?
>
> I don't really want anything that relies on rowids, because the table
> may eventually be fragmented.
First if your fragmented table is not created WITH ROWIDS there is NO way
to easily remove duplicates. All schemes for removing duplicate rows from
a table that does not have a unique key depend on the rowid to BE that
unique key. It is cleanups like this and the garbaging up that causes one
to have to perform them that prompt Relational hardliners (myself included)
to insist that EVERY table have a unique key for each row. There is NO GOOD
reason for not having a unique key on any table!
Now WITH rowids you can:
select {keycols}, count(*)
from atable
group by {keycols}
having count(*) > 1
into temp fred;
delete from atable
where rowid in (
select min(t.rowid)
from atable t, fred f
where {join conditions on {keycols} between t & f}
group by {keycols}
);
If you do not have rowids the ONLY solution is to SELECT DISTINCT into a
new table, drop the old one, and rename the new one. HOWEVER, if there
are columns other than the keys that might differ and you want to drop all
but one of those anyway it gets more complicated yet.
Art S. Kagel
> select {keycols}, count(*)
> from atable
> group by {keycols}
> having count(*) > 1
> into temp fred;
>
> delete from atable
> where rowid in (
> select min(t.rowid)
> from atable t, fred f
> where {join conditions on {keycols} between t & f}
> group by {keycols}
> );> Art S. Kagel
That's funny, Art: every time you need a temp table (not worth to stay after
use)
you call it "Fred". Does that mean you had a guy in your childhood named Fred
you didn't like at all ?
It would be funny to know every DBAs favourite "garbage-name"... :-)
By the way: my garbage table sounds most of the time "jau" or "jepp" or
"jawoll"...
Thanks for your input guys, much appreciated :) Regarding naming of temp tables, maybe the optimiser looks favorably on temp tables called Fred? :) In article <8i2v71$80v$1@nnrp1.deja.com>, strangiato@my-deja.com wrote: > Online 7.x > > What is the fastest way to delete duplicate rows from a table? > > I don't really want anything that relies on rowids, because the table > may eventually be fragmented. > > Thanks :) > > Sent via Deja.com http://www.deja.com/ > Before you buy. > Sent via Deja.com http://www.deja.com/ Before you buy.
Chris Brauer wrote:
>
> > select {keycols}, count(*)
> > from atable
> > group by {keycols}
> > having count(*) > 1
> > into temp fred;
> >
> > delete from atable
> > where rowid in (
> > select min(t.rowid)
> > from atable t, fred f
> > where {join conditions on {keycols} between t & f}
> > group by {keycols}
> > );> > Art S. Kagel
>
> That's funny, Art: every time you need a temp table (not worth to stay after
> use)
> you call it "Fred". Does that mean you had a guy in your childhood named Fred
> you didn't like at all ?
>
> It would be funny to know every DBAs favourite "garbage-name"... :-)
>
> By the way: my garbage table sounds most of the time "jau" or "jepp" or
> "jawoll"...
Actually I tend to name temp tables after Flintstones characters:
Fred, Barney, Wilma, Betty, Pebbles, ...
I have not had a need to go to Bam Bam yet though ;-)
Lord knows why I started that, and please don't tell my shrink.
Art S. Kagel