Oracleデータベースで重複するレコードを削除する方法

通常の作業では、ライブラリ・テーブルの1つまたは複数のカラムに一意のインデックスを作成しようとすると、ORA-01452に一意のインデックスを作成できず、重複レコードを発見するよう求められる場合があります.
重複するレコードの検索と削除方法(表CZを例に)をいくつかまとめます.
表CZの構成は以下の通りである.
 

SQL> desc cz
Name Null? Type
----------------------------------------- 

C1 NUMBER(10)
C10 NUMBER(5)
C20 VARCHAR2(3)

重複記録を削除する方法の原理:
(1).Oracleでは、各レコードに1つのrowidがあり、rowidはデータベース#データベース#全体で一意であり、rowidは各レコードがOracleのどのデータファイル、ブロック、行にあるかを決定します.
(2).重複するレコードでは、すべてのカラムの内容が同じかもしれませんが、rowidは同じではありませんので、重複レコードの中で最大rowidを持っているものを特定すればいいので、残りはすべて削除します.
繰り返し記録判定の基準は、
C 1,C 10,C 20の3列の値が同じであることが重複記録となる.
調査によると、表CZは全部で16件の記録がある.
 

SQL>set pagesize 100
SQL>select * from cz;

C1 C10 C20
---------- ---------- ---
1 2 dsf
1 2 dsf
1 2 dsf
1 2 dsf
2 3 che
1 2 dsf
1 2 dsf
1 2 dsf
1 2 dsf
2 3 che
2 3 che
2 3 che
2 3 che
3 4 dff
3 4 dff
3 4 dff
4 5 err
5 3 dar
6 1 wee
7 2 zxc

20 rows selected.

1.重複記録を検索する方法:
 

(1).SQL>select * from cz group by c1,c10,c20 having count(*) >1;
C1 C10 C20
---------- ---------- ---
1 2 dsf
2 3 che
3 4 dff

(2).SQL>select distinct * from cz;

C1 C10 C20
---------- ---------- ---
1 2 dsf
2 3 che
3 4 dff

(3).SQL>select * from cz a where rowid=(select max(rowid) 
from cz where c1=a.c1 and c10=a.c10 and c20=a.c20);
C1 C10 C20
---------- ---------- ---
1 2 dsf
2 3 che
3 4 dff

2.重複記録を削除する方法:
(1).大量の重複記録がある場合(C 1,C 10,C 20列にインデックスが作成されている場合、以下の文を使用すると効率的になります):
 

SQL>delete cz where (c1,c10,c20) in (select c1,c10,
c20 from cz group by c1,c10,c20 having count(*)>1) and rowid not in
(select min(rowid) from cz group by c1,c10,c20 having count(*)>1);

SQL>delete cz where rowid not in
(select min(rowid) from cz group by c1,c10,c20);

(2).少量の重複レコードがある場合に適用されます(重複レコードが多い場合は、次の文で効率が低下することに注意してください).
 

SQL>delete from cz a where a.rowid!=(select max(rowid)
 from cz b where a.c1=b.c1 and a.c10=b.c10 and a.c20=b.c20);

SQL>delete from cz a where a.rowid<(select max(rowid) 
from cz b where a.c1=b.c1 and a.c10=b.c10 and a.c20=b.c20);

SQL>delete from cz a where rowid <(select max(rowid) 
from cz where c1=a.c1 and c10=a.c10 and c20=a.c20);

(3).少量の重複記録がある場合(テンポラリ・テーブル法):
 

SQL>create table test as select distinct * from cz; 
(      test         ）

SQL>truncate table cz; (  cz    ，   cz    ）

SQL>insert into cz select * from test; 
(     test        ）

(4).大量の重複記録がある場合(Exception into句法):
また、alter tableコマンドのException into句を使用して、出庫テーブルに重複するレコードを特定することもできます.この方法は少し面倒ですが、「excepeion into」句を使用するには、まずEXCEPTIONSテーブルを作成する必要があります.このテーブルを作成するSQLスクリプトファイルはutlexcptです.sql .win 2000システムとUNIXシステムでは、Oracleがファイルを格納する場所が少し異なります.win 2000システムでは、スクリプトファイルは$ORACLE_に格納されます.HOMEOra 90 rdbmsadminディレクトリの下;一方、UNIXシステムでは、スクリプトファイルは$ORACLE_に格納されます.HOME/rdbms/adminディレクトリの下にあります.
具体的な手順は次のとおりです.
 

SQL>@?/rdbms/admin/utlexcpt.sql

Table created.

SQL>desc exceptions
Name Null? Type
----------------------------------------- 

ROW_ID ROWID
OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
CONSTRAINT VARCHAR2(30)

SQL>alter table cz add constraint 
cz_unique unique(c1,c10,c20) exceptions into exceptions; 
*
ERROR at line 1:
ORA-02299: cannot validate (TEST.CZ_UNIQUE) - duplicate keys found

SQL>create table dups as select 
* from cz where rowid in (select row_id from exceptions); 

Table created.

SQL>select * from dups; 

C1 C10 C20
---------- ---------- ---
1 2 dsf
1 2 dsf
1 2 dsf
1 2 dsf
2 3 che
1 2 dsf
1 2 dsf
1 2 dsf
1 2 dsf
2 3 che
2 3 che
2 3 che
2 3 che
3 4 dff
3 4 dff
3 4 dff

16 rows selected.

SQL>select row_id from exceptions;

ROW_ID
------------------
AAAHD/AAIAAAADSAAA
AAAHD/AAIAAAADSAAB
AAAHD/AAIAAAADSAAC
AAAHD/AAIAAAADSAAF
AAAHD/AAIAAAADSAAH
AAAHD/AAIAAAADSAAI
AAAHD/AAIAAAADSAAG
AAAHD/AAIAAAADSAAD
AAAHD/AAIAAAADSAAE
AAAHD/AAIAAAADSAAJ
AAAHD/AAIAAAADSAAK
AAAHD/AAIAAAADSAAL
AAAHD/AAIAAAADSAAM
AAAHD/AAIAAAADSAAN
AAAHD/AAIAAAADSAAO
AAAHD/AAIAAAADSAAP

16 rows selected.

SQL>delete from cz where rowid in 
( select row_id from exceptions);

16 rows deleted.

SQL>insert into cz select distinct * from dups;

3 rows created.

SQL>select *from cz;

C1 C10 C20
---------- ---------- ---
1 2 dsf
2 3 che
3 4 dff
4 5 err
5 3 dar
6 1 wee
7 2 zxc

7 rows selected.

結果から重複レコードが削除されたことがわかります.