How to use RowID to find and delete repetition records in Oracle
Transfer from: 9CBS
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: (1) In Oracle, each record has a RowID, and RowID is unique in the entire database, RowID determines 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 Total 16 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 repetitive records: (1). Applicable to have a lot of repetition The case of records (when there is an index on the C1, C10 and C20 columns, the following statement is 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 where a small amount of repeated records (note, for a large number of repetitive records,
Use the following statement efficiency will be 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 "from ca ma where c1 = a.c1 and c10 = a.c10 and c20 = a.c20); (3). Suitable for a small amount of repeated record (Temporary Table): SQL> CREATE TABLE TEST AS SELECT DISTINCT * FROM CZ; (built a temporary table test) SQL> TRUNCATE TABLE CZ; (Clear the data of the CZ table, but retain the CZ table Structure) SQL> INSERT INTO CZ SELECT * from Test; (again inserting the content in the temporary table test) (4). Suitable for a large number of repetitive records (Exception INTO]: using the alter table command The Exception INTO clause can also determine the repeated record in the warehouse. 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.sql table created.sql> desc eXceptions name null? Type ----------------------- ------------------ -------- -------------- Row_ID Rowid Owner varcha2 (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 self * 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 DS F 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 / AAIAAAADSAAJAAAHD / AAIAAAADSAAKAAAHD / AAIAAAADSAALAAAHD / AAIAAAADSAAMAAAHD / AAIAAAADSAANAAAHD / AAIAAAADSAAOAAAHD / AAIAAAADSAAP 16 rows selected.SQL>
