Re: How to make faster deletion on duplicated records?

This follows on an earlier thread about duplicate deletion. The ssential question is:

Are the rows complete duplicates, or can two rows exist with different non-key values for the same primary key?

In the first case, the swiftest solution is: create new_table as

        select distinct * from old_table;

(then drop/rename)

In the second case you will have to establish which rows contain the "correct" non-key values.

In the first case, the use of min(rowid) is unnecessary. In the second case, it is a cop-out.

Chrysalis.

shu_at_openix.com wrote:
>
> Hi, all,
>
> I tried the following two ways to delete duplicate records on
> a 5000 records table:
>
> 1). DELETE FROM Customer C
> WHERE C.rowid > ( SELECT MIN(X.rowid)
> FROM Customer X
> WHERE X.phone = C.phone);
>
> 2). CREATE TABLE temp
> AS
> (SELECT * FROM Customer C
> WHERE C.rowid > ( SELECT MIN(X.rowid)
> FROM Customer X
> WHERE X.phone = C.phone)
> );
>
> DROP TABLE Customer;
>
> CREATE TABLE Customer
> AS
> (SELECT * FROM temp);
>
> DROP TABLE temp;
>
> The second way is a little faster than the first one, but both of them took
> about 40 minutes to finish the deletion. Even after I indexed on the "phone"
> column, the performance is not improved.
>
> Is there any better way to do that? Thanks.
Received on Fri Mar 07 1997 - 00:00:00 CST