Deleting Same Tuples

[karan_21584]

hai, i m new to this site and my 1st post is this. I have a table which consist of some tuples. i didnt set any primary key. by mistakely i entered the same values for two tuples. i want to delete a single tuple without copying the table to another new table or using DISTINCT keyword... how its possible. My professor told there is an way... and ask me to find out. CAN anyone help me? thanks in advance....CLEAR MY DOUBT IF U GENIUS

[davidcairns]

You could use the rowid, it's probably the simplest solution. For info the rowid will uniquely identify a row within a table. It is a virtual column that Oracle maintains for all tables. Other than for situations where no primary or unique exists it shouldn't be relied upon too heavily though.

I'll let you figure out how to apply it to this problem

[karan_21584]

thanks Mr.Davidcairns..could u figure out how to apply it to this problem? plz..thanks in advance

[Memento]

If you add the rowid pseudo column to your initial select statement you would see that it is different for both rows. The where clause of your update statement would then reference the rowid of the record you want to update.

[davidcairns]

For small sample sizes that is the best way, but for a more generic and scalable approach I would go with the following

DELETE
FROM tablename
WHERE rowid = (SELECT t.rowid
FROM tablename t,
(SELECT keyval, min(rowid) as keeprowid
FROM tablename
GROUP BY keyval) k
WHERE t.rowid(+) = k.keeprowid
AND k.keeprowid IS NULL
AND t.rowid = tablename.rowid)

Note that I used = rather than IN as the performance difference can be huge. This will work well on almost any table, but will probably work better in this case if a non unique index is applied to the keyval field. The keyval may actually be more than one field, it could if you wish be every field in the table.

[Memento]

That is pretty slick... I'm going to have to save that :cheesy:.

[~s.o.s~]

Another approach would be :

delete from TABLE_NAME T where rowid not in 
   (select max(rowid) 
    from TABLE_NAME R 
    where R.common_column = T.common_column;) ;

Here common_column is the criteria for filtering out duplicates.

[davidcairns]

Technically though if 3 copies of a single row existed you would only delete 1 with that script, hence why I used the 1 to keep instead of 1 to delete

Also in large tables that will be much slower

[~s.o.s~]

Technically though if 3 copies of a single row existed you would only delete 1 with that script, hence why I used the 1 to keep instead of 1 to delete

Nope. Take a look at it once again. I delete those tuples who don't satisfy the criteria of having the max ROWID. In any case when duplicate exists, only one row will have the max rowid among all the duplicates. Hence the query. Try running it and you will know.

> Also in large tables that will be much slower

Correlated queries are generally faster as compared to other types of queries. On what basis can you say that the query would be slow ?

[davidcairns]

Fair enough, should read more carefully :)

In my experience with large databases providing an exact match is far more efficient than using IN or EXISTS clauses. This may be improved with adequate use of indexes, and the latest versions of Oracle may have made further improvements, but in essence it does hold. The larger the table the more pronounced this issue becomes. I have run this kind of things over tables with row counts in excess of 20 million in production.
Also joining using queries in the from clause is far more efficient in Oracle for at least versions 7, 8 and 9 (I have very limited experience working with 10).
Essentially limiting all queries to only those rows that actually need to be returned is efficient for any database.