oracle主键重复报错,ORACLE批量删除无主键重复数据

## 1.需求说明

TEST表情况说明:

- 按月进行分区的分区表

- 未定义主键或唯一索引

- 包含COL1,COL2,COL3,INSERTTIME四列

现需要删除2019年3月31日当天存在的重复数据

## 2.解决方法

### 2.1 确认无重复数据的记录数

```sql

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数据量特别大,此处新建一张表将需要处理的数据单独存放

```sql

CREATE TABLE TEST_TMP NOLOGGING AS

SELECT /*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 确认需要删除的数据

理论上而言需要删除和需要保留的数据记录数应相等

```sql

--需要删除的数据记录数

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 利用分批提交删除重复数据

```sql

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 C_ROWIDS;

LOOP

--此处LIMIT后的值为分批提交的记录数,可以根据实际情况调整

FETCH C_ROWIDS BULK COLLECT INTO ROWID_INFOS LIMIT 10000;

FORALL I IN 1..ROWID_INFOS.COUNT

--如下的DELETE语句为分批提交实际需要执行的部分

DELETE FROM TEST WHERE ROWID=ROWID_INFOS(I);

COMMIT;

EXIT WHEN ROWID_INFOS.COUNT<10000;

END LOOP;

CLOSE C_ROWIDS;

END;

```

### 2.4 确认无重复数据

```sql

SELECT * 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,INSERTTIME

HAVING COUNT(1)>1 )

);

```

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/69923980/viewspace-2676483/,如需转载,请注明出处,否则将追究法律责任。

参与评论 您还未登录,请先 登录 后发表或查看评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:数字20 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值