Removing duplicated rows revisited - I'm feeln' mighty lame

Hi,

I found a table where there is a lot of duplicated telephone calls in it. To be more precise: 34,000 duplicate calls from a table containing 1.5 million rows. Some were posted the same day as its duplicate, others were posted on different days. I wrote a series of queries to extract the first posted call of a duplicated set, delete all the duplicated calls, then reinsert the first call back in. My concern is the actual delete step: it uses a correlated subquery. Previous experience with our machine (a RS/6000 running Oracle) showed me it has a hard time working with correlated subqueries of any kind. At one point I ran a SELECT correlated subquery for six hours without it ever completing. When I broke up the query into two, the inner query being a VIEW and the outer query being a SELECT based on the VIEW, it took two hours and completed. Go figure.

I'm presenting what I'm planning to do in the hopes someone may give advice on improving it. I'm mainly concerned about the DELETE step since every source I've found on deleting rows from one table based on another uses the correlated subquery method. Maybe someone knows a better way...

--

• create a table where the first call of a duplicated set is
• stored based on the earliest posted date
  --
  DROP TABLE TEMP_TABLE CREATE TABLE TEMP_TABLE AS SELECT DISTINCT person_id , date_called , origin_phone , destination_phone , duration_minutes , duration_seconds , MIN(date_posted) AS date_posted FROM CALL_TABLE GROUP BY person_id , date_called , origin_phone , destination_phone , duration_minutes , duration_seconds HAVING Count(*)>1 ;
  --
  
• Delete all duplicated calls from a table
  --
  DELETE FROM CALL_TABLE WHERE EXISTS ( SELECT * FROM CALL_TABLE Tmp GROUP BY Tmp.person_id , Tmp.date_called , Tmp.destination_phone , Tmp.origin_phone , Tmp.duration_minutes , Tmp.duration_seconds HAVING Count(*)>1 And Tmp.person_id = person_id And Tmp.date_called = date_called And Tmp.destination_phone = destination_phone And Tmp.origin_phone = origin_phone And Tmp.duration_minutes = duration_minutes And Tmp.duration_seconds = duration_seconds ) ;
  --
  
• Reinsert the first posted record
  --
  INSERT INTO CALL_TABLE ( person_id , date_called , origin_phone , destination_phone , duration_minutes , duration_seconds , date_posted ) SELECT person_id , date_called , origin_phone , destination_phone , duration_minutes , duration_seconds , date_posted FROM TEMP_TABLE ;
  --
  
• end of procedure
  --
  

Thanks in advance,
Boyce G. Williams, Jr.
Boyce G. Williams, Jr.

 .--------------------------------------------------------------------.
 | "People should have two virtues:  purpose- the courage to envisage |
 | and pursue valued goals uninhibited by the defeat of infantile     |
 | fantasies, by guilt and the failing fear punishment;  and wisdom- a|
 | detached concern with life itself, in the face of death itself."   |
 |                                                     Norman F. Dixon|
 '--------------------------------------------------------------------'