I know I have seen this posted
before.......
We have a large range partitioned
table that has duplicates in it. What is the fastest way to remove the
dups.? I have the following scripts which do it but may be fast or
slow. What do you guys use?
DELETE FROM tablename
WHERE ROWID NOT IN
(SELECT MIN(ROWID)
FROM tablename
GROUP
BY fieldnames);
[Ferenc
Mantfeld] This will be your fastest way, provided you have an index on
the columns searched for. Actually the format of the statement would
be
delete from INVOICE_DETAILS A where A.rowid
>
(select
min(rowid) from INVOICE_DETAILS B where
B.INV_NUM=A.INV_NUM and
B.LINE_NUM=A.LINE_NUM )
;
Ensure you have a composite index on INVOICE_DETAILS
(INV_NUM, LINE_NUM).
Or
alter table &table_name
add constraint duplicate_cons
unique key (&column_name)
exceptions into exception
table;
[Ferenc Mantfeld] Problem with this is when you want to delete the
duplicates, you have no way of telling, unless you code the min function
again. If you have triplicates, and want to keep one of them and
blow away the other two, this is a tedious way, and all this does is to
help you identify the duplicates.
How to find duplicates:
select &column_name, count(&column_name)
from &table_name
group by &column_name
having count(&column_name) >
1;
[Ferenc Mantfeld] Same as above. only identifies the duplicates,
does nothing to remove them.
Tom
