Shulou(Shulou.com)05/31 Report--

This article introduces how to delete duplicate data without primary key in batch in ORACLE. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

1. Requirement description

TEST table description:

A partition table partitioned by month

No primary key or unique index defined

Contains four columns of COL1,COL2,COL3,INSERTTIME

Duplicate data that existed on March 31, 2019 needs to be deleted.

two。 Solution 2.1 confirm the number of records without duplicate data SELECT COUNT (1) FROM (SELECT COL1,COL2,COL3,INSERTTIME FROM TEST PARTITION (P201903) A WHERE INSERTTIME > = DATE'2019-03-31' AND INSERTTIME

< DATE'2019-04-01' GROUP BY COL1,COL2,COL3);2.2 梳理需要筛选的数据 由于原表A数据量特别大，此处新建一张表将需要处理的数据单独存放 CREATE TABLE TEST_TMP NOLOGGING ASSELECT /*PARALLEL +8 */ A.*,A.ROWID ROWID_OLD FROM TEST PARTITION(P201903) A WHERE INSERTTIME >

= DATE'2019-03-31' AND INSERTTIME

< DATE'2019-04-01';2.2 确认需要删除的数据 理论上而言需要删除和需要保留的数据记录数应相等 --需要删除的数据记录数 SELECT COUNT(1) FROM TEST PARTITION(P201903) A WHERE ROWID IN ( SELECT MIN(ROWID_OLD) ROWID_OLD FROM TEST_TMP WHERE INSERTTIME >

= DATE'2019-03-31' AND INSERTTIME

< DATE'2019-04-01' GROUP BY COL1,COL2,COL3,INSERTTIME HAVING COUNT(1) >

1) AND INSERTTIME > = DATE'2019-03-31' AND INSERTTIME

< DATE'2019-04-01'--需要保留的数据记录数 SELECT COUNT(1) FROM TEST PARTITION(P201903) A WHERE ROWID NOT IN ( SELECT MIN(ROWID_OLD) ROWID_OLD FROM TEST_TMP WHERE INSERTTIME >

= DATE'2019-03-31' AND INSERTTIME

< DATE'2019-04-01' GROUP BY COL1,COL2,COL3,INSERTTIME HAVING COUNT(1) >

1) AND INSERTTIME > = DATE'2019-03-31' AND INSERTTIME

< DATE'2019-04-01'2.3 利用分批提交删除重复数据DECLARE TYPE ROWID_LIST IS TABLE OF UROWID INDEX BY BINARY_INTEGER; ROWID_INFOS ROWID_LIST; I NUMBER; CURSOR C_ROWIDS IS (SELECT MIN(ROWID_OLD) ROWID_OLD FROM TEST_TMP WHERE INSERTTIME >

= DATE'2019-03-31' AND INSERTTIME

< DATE'2019-04-01' GROUP BY COL1,COL2,COL3,INSERTTIME HAVING COUNT(1) >

1); BEGIN OPEN DELETE; LOOP-- where the value after LIMIT is the number of records submitted in batches, so you can adjust FETCH C_ROWIDS BULK COLLECT INTO ROWID_INFOS LIMIT 10000 according to the actual situation; FORALL I IN 1..ROWID_INFOS.COUNT-- the following DELETE statement is the part of DELETE FROM TEST WHERE ROWID=ROWID_INFOS (I) that needs to be executed in batch submission; COMMIT EXIT WHEN ROWID_INFOS.COUNT= DATE'2019-03-31 'AND INSERTTIME

< DATE'2019-04-01' GROUP BY COL1,COL2,COL3,INSERTTIME HAVING COUNT(1)>

1) about how to delete duplicated data without primary key in batch in ORACLE. I hope the above content can be helpful to you and learn more. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.