Ultra-time work may encounter when trying to create a unique index in a column or several columns in the library table, the system prompts ORA-01452: The unique index cannot be created, and the repeated record is discovered. Here, several ways to find and delete repeated records (Table CZ as an example): Table CZ structure is as follows: SQL> DESC CZ NAME NULL? TYPE -------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------- -
C1 Number (10) C10 Number (5) C20 VARCHAR2 (3) Delete Repeated Record Method Principles: (1). In Oracle, each record has a RowID, and RowID is unique in the entire database, RowID is determined. Each record is in which data file, block, and line in Oracle. (2). In a repeated record, all columns may be the same, but the RowID will not be the same, so as long as it is determined that the maximum ROWID has the maximum ROWID, the rest is removed. The standards for repeated recording judgments are: C1, C10, and C20, the values of the three columns of C1, C10, and C20 are the same as repeated records. View Table CZ has a total of 20 records: SQL> Set PageSize 100Sql> 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. See several ways to repeat the record: (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 DFF2. Several methods of deleting repeated records: (1). Applicable to a large number of repetitive records (with indexing on C1, C10 and C20 columns, with the following The statement efficiency 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) Suitable for situations with a small amount of repeated records (note that there is a lot of repeated records, the following statement is very low): 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). Suitable for situations in a small amount of repeated record (temporary table): SQL> Create Table Test AS SELECT DISTINCT * from CZ; (built a temporary table test to store repetitive records)
SQL> Truncate Table Cz; (Clear the data of the CZ table, but retain the structure of the CZ table)
SQL> INSERT INTO CZ SELECT * from test; (again inserting the content in the temporary table test) (4). Applicable to a large number of repetitive records (Exception INTO]: EXCEPTION INTO in the alter Table command The clause can also determine the repeated record in the outlet table. This method is slightly troublesome, in order to use the "Exceion INTO" clause, you must first create an Exceptions table. Create the SQL script file for this table to UTLEXCPT.SQL. For Win2000 systems and UNIX systems, Oracle stores the location of this file is slightly different. Under Win2000 systems, this script file is stored in the $ ORACLE_HOME / ORA90 / RDBMS / Admin directory; for UNIX systems, this script file is stored in $ Oracle_home / RDBMS / Admin directory. The specific steps are as follows: SQL> @? / Rdbms / admin / utlexcpt.sqltable 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: can not validate ( Test.cz_unique) - DUPLICATE Keys Foundsql> Create Table Dups As SELECT * From CZ Where Rowid In (SELECT ROW_ID FROM EXCEPTION); 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 / AAIAAAADSAAAAAAHD / AAIAAAADSAABAAAHD / AAIAAAADSAACAAAHD / AAIAAAADSAAFAAAHD / AAIAAAADSAAHAAAHD / AAIAAAADSAAIAAAHD / AAIAAAADSAAGAAAHD / AAIAAAADSAADAAAHD / AAIAAAADSAAEAAAHD / Aaiaaaaaaaaaaaahd / Aaaiaaadsaalaaaahd / Aaaiaaadsaaaaahd / Aaiaaaadsaaaaahd / Aaiaaaadsaap
16 rows selected.sql> delete from cz where rowid in (select row_id from exception);
16 rows deleded.
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. From the results you can see the repeated record has been deleted.
