[Oracle] fast way to delete duplicate data

[Oracle] the quick way to DELETE duplicate data is that you have never seen this method on the Internet: delete from prod_grid_incWHERE rowid in (SELECT B. row_id2 FROM (select rowid row_id2,. *, row_number () over (partition by. prod_id order by. 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 temporary table's tablespace and index tablespace must meet the requirements of the formal table: eg: INSERT/* + append */INTO ACCT_ITEM_MIDSELECT * FROM ACCT_ITEM SUBPARTITION (p_2011__sub_p_xx) a where rowid = (select max (ROWID) FROM ACCT_ITEM SUBPARTITION (P_201109_SUB_P_XX) a1 WHERE. cust_id = a1.cust _ id); COMMIT; alter table ACCT_ITEM exchange subpartition p_201511_sub_p_xx WIT H table ACCT_ITEM_MID; of course, although the exchange method is fast, this situation will cause index failure. You need to re-create the index: alter index ind_name rebuild subpartition subpartition_name; so that you can.