Problem in deleting duplicate rows

[himridul]

Hi,

Following is my De-Dup(Delete Duplicate rows) query . It deletes duplicate rows in ph_extract_tmp1 table on the basis of where condition. Now for 122 millions of rows , it's getting hanged . So can I use any analytical function to solve this problem ? OR is there any other way for De-Dup the records from this table ?

ph_extract_tmp1 table is properly partitioned and indexed .

DELETE ph_extract_tmp1 A
WHERE EXISTS
(
SELECT /*+ USE_HASH(A,B) */
1
FROM
ph_extract_tmp1 B
WHERE
A.aol_indv_id = B.aol_indv_id
AND NVL(A.aol_day_ph_nb,'0') = NVL(B.aol_day_ph_nb,'0')
AND NVL(A.aol_ngt_ph_nb,'0') = NVL(B.aol_ngt_ph_nb,'0')
AND NVL(A.csi_day_ph_nb,'0') = NVL(B.csi_day_ph_nb,'0')
AND A.ROWID > B.ROWID
)


Thanks in advance ....

 

[stefanhei]

Hi.
For a table of that size I'd create a table with the unique rows and replace the original table with that one.

create table tmp as select ...
drop table original;
rename tmp to original;
create index and constraints...

Stefan