How to Use rowid in Oracle to find and delete duplicate records in a table)

How to Use rowid in Oracle to find and delete duplicate records in a table

From: csdn During normal work, you may encounter a system prompt ORA-01452 when you try to create a unique index for one or more columns in the database table: you cannot create a unique index and duplicate records are found. The following describes several methods for searching and deleting duplicate records (using the table CZ as an example ): The structure of the table CZ is as follows: SQL> DESC CZ Name null? Type ------------------------------------------------------------------- C1 number (10) C10 number (5) C20 varchar2 (3) How to delete duplicate records: (1 ). in Oracle, each record has a rowid, which is unique throughout the database, rowid determines which data file, block, and row of each record in Oracle. (2 ). in a duplicate record, the content of all columns may be the same, but the rowid may not be the same. Therefore, you only need to determine those with the largest rowid in the record, and delete all the others. The criteria for repeat record determination are: Only when the values of the C1, C10, and C20 columns are the same are repeated records. The following table shows a total of 16 records in the CZ table: 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. Several Methods for searching duplicate records: (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 =. c1 and C10 =. c10 and C20 =. c20 ); C1 C10 C20 ----------------------- 1 2 DSF 2 3 Che 3 4 DFF 2. Several Methods to delete duplicate records: (1). Suitable for the case of a large number of Repeated Records (when an index is built on the C1, C10, and C20 columns, the efficiency of using the following statements will be high ): 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). applicable to the case of a small number of Repeated Records (note that the efficiency of the following statement is very low for the case of a large number of Repeated Records ): 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 B where. c1 = B. c1 and. c10 = B. c10 and. 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). applicable to the case of a small number of duplicate records (temporary table method ): SQL> Create Table Test as select distinct * From CZ; (create a temporary table test to store duplicate records) SQL> truncate table CZ; (Clears data in the CZ table, but retains the structure of the CZ table) SQL> insert into CZ select * from test; (insert the content in test in the temporary table) (4). applicable to the case of a large number of Repeated Records (exception into clause ): The exception into clause in the alter table command can also be used to determine duplicate records in the warehouse picking table. This method is a little more troublesome. To use the "upgrade eion into" clause, you must first create the conditions table. The SQL script file used to create the table is utlexcpt. SQL. For Win2000 and Unix systems, Oracle stores the file in a slightly different location. In Win2000 systems, the script file is stored in the $ ORACLE_HOME/ora90/rdbms/Admin directory; for Unix systems, the script file is stored in the $ ORACLE_HOME/rdbms/Admin directory. The procedure is as follows: 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. The duplicate record has been deleted.