There are a variety of ways to delete duplicate data on the Internet. Oracle has never seen such a method:
Delete from prod_grid_inc
Where rowid in (SELECT B. row_id2
FROM (select rowid row_id2,
A .*,
Row_number () over (partition by a. prod_id order by a. insert_date DESC) row_num
FROM prod_grid_inc a) B
WHERE B. row_num> = 2 );
You may try it. After testing, it is indeed much faster.
If it is a big table of several GB (usually a partition table in this case), it is best to append + nologging, insert the unique record into the temporary table, and then exchange it by exchanging partitions, it takes only a few minutes before and after, and the tablespace of the temporary table and the index tablespace must meet the requirements of the formal table:
Eg:
INSERT/* + append */INTO ACCT_ITEM_MID
SELECT *
FROM ACCT_ITEM SUBPARTITION (p_201511_sub_p_xx)
Where rowid = (select max (ROWID)
FROM ACCT_ITEM SUBPARTITION (p_201511_sub_p_xx) a1
WHERE a. cust_id = a1.cust _ id );
COMMIT;
Alter table ACCT_ITEM exchange subpartition p_201511_sub_p_xx with table ACCT_ITEM_MID;
Of course, although the exchange method is fast, this situation will cause index failure and requires re-indexing:
Alter index ind_name rebuild subpartition subpartition_name;
In this case.