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

Source: Internet
Author: User
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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.