basic introduction
[From "
Oracle China User Discussion Group]
In a problem, you need to repeat a record in a table, and the table has a unique main key.
Baojianjun
How to delete repeat records in the table
Method principle:
1, Oracle, each record has a RowID, and RowID is unique in the entire database.
RowID determines which data file, block, and rows of each record are in Oracle.
2. In the repeated record, it may be the same content, but the RowID will not be the same, so as long as you determine the repeated record
Those who have the largest ROWID can be removed, and the rest is removed.
3, the following statement is used to three skills: RowID, child query, alias.
Implementation:
SQL> CREATE TABLE A
2 BM Char (4), - Code
3 mc varchar2 (20) - Name
4)
5 /
The table has been established.
SQL> INSERT INTO A VALUES ('1111', '1111');
SQL> INSERT INTO A VALUES ('1112', '1111');
SQL> INSERT INTO A VALUES ('1113', '1111');
SQL> INSERT INTO A VALUES ('1114', '1111');
SQL> INSERT INTO A SELECT * FROM A;
Insert 4 records.
SQL> commit;
Completely submit.
SQL> SELECT ROWID, BM, MC from A;
Rowid BM MC
---------------------------
000000D5.0000.0002 1111 1111
000000D5.0001.0002 1112 1111
000000D5.0002.0002 1113 1111
000000D5.0003.0002 1114 1111
000000D5.0004.0002 1111 1111
000000D5.0005.0002 1112 1111
000000D5.0006.0002 1113 1111
000000D5.0007.0002 1114 1111
Query 8 records.
Isolated repeated record
SQL> SELECT ROWID, BM, MC from a where a.rowid! = (SELECT MAX (ROWID) from a b where a.bm = b.bm and a.mc = b.mc);
Rowid BM MC
----------------------------------------
000000D5.0000.0002 1111 1111
000000D5.0001.0002 1112 1111
000000D5.0002.0002 1113 1111
000000D5.0003.0002 1114 1111
Delete repeat
SQL> Delete from a a a where a.rowid! = (Select max (rowid) from a b where a.bm = b.bm and a.mc = b.mc);
Delete 4 records.
SQL> SELECT ROWID, BM, MC from A;
Rowid BM MC
----------------------------------------
000000D5.0004.0002 1111 1111
000000D5.0005.0002 1112 1111000000D5.0006.0002 1113 1111
000000D5.0007.0002 1114 1111
In-depth discussion [from www.fanqiang.com]
ROWID structure of oracle8: the ominous [article from: www.fanqiang.com]
1. Why use RowID Oracle to mark the unique indication of the ROW as a B-tree and its internal algorithm. In Oracle8 Previous versions, Rowid marked File, Block, Row Number, using only one digit represents the File number. In Oracle8, a DataFile has two numbers represent: an absolute value is unique to the entire database. You can see the file_id in DBA_DATA_FILES. A relative value, is unique in TableSpace, you can see the Relative_ FNO in DBA_DATA_FILES. The new ROWID uses the relative value, so the sign of segment must be stored, otherwise it will be confused. So the ORAC LE8 adds the Segment number of the object to the ROWID to indicate Table or Partition. 2, RowID structure uses Base-64 code, including A-Z, A-Z, 0-9, , A total of 18 bits. 1-6: Represents Object 7-9: File Relative Value 10-15: Block 16-18: Block 16-18: Block's SLOT Value 3, TABLESPACE-Relative Addressing Method, Multiple files can have the same relative value because they belong to different TableSpace, so they cannot get absolute addresses from new ROWID, but this is no problem, because when I want to handle an Object, it is already determined which TableSapce it belongs. In Tables PACE, the file relative value is unique, so ROWID can still be uniquely marked an Object. Table Space-Relative Addressing Mode is a key technique that supports oversized databases in Oracle8. 4, Data Object Number Data Object Number is used to indicate Segment, all segments have Data Object Number, stored in each Data Block and does not repeat. The very beginning, DBA_OBJECTS.OBJECT_ID = DBA_OBJECTS.DATA-OBJECT_ID, but will increase the DATA-OBJECT_ID DATA OBJECT NUMBER TRUNCATE TABLE MOVE PARTITION ORACLE ROWID in checks and BLOCK DATA OBJECT NUMBER in the case where in the above case, Ensure that the versions between them are consistent. Oracle also uses Data Object Number to make sure that Rollback's records and the latest Segment records.
It is important to note that Data Object Number is not Object's logo 5. The ROWID format of Restricted Rowid Oracle7 is 1-8 bits: Block Number 9-12 Bits: Row Number 13-16 Bits: File Number Oracle8 supports short, old format RowID , the role of the INDEX ENTRY NOPARTITION TABLE LOCAL INDEX ENTRY ROW Piece CHain pointer limited to PARTITION TABLE is stored inside ROWID 6BYTE, 4BYTE = DATA BLOCK NUMBER 2BYTE = ROW NUMBER that is to say, INDEX ENTRY 6BYTE used to store the ROWID, This is enough for most Index. But this short ROWID cannot be used on the Global INDEX of PATITION TABLE, because Partition may cross TableSpace. Show this ROWID is still 18-bit 6, extended RowID Oracle is stored internally 10 Byte, including (Data Object Number, Data Block Nu Mber, Row Number) Oracle8 Using Extension Rowid: Partition Table Global Index Server Algorithm The extended RowID is still 18-bit display when SELECT is stored in the ROWID field. 7. When using oracle7's RowId query Oracle7's RowID from Oracle8's DB, ROWID returns to Oracle7 format, or in the WHERE statement. When Query Oracle8's RowId is queried from Oracle7's DB, RouwID returns to Oracle8's format, or in the WHERE statement, but cannot be stored in the RowID field. But you have to explain with a dbms_rowid package. If the extended Oracle8 RowID, this cannot be used in Oracle8's data import to Oracle7. From Oracle 7, IMPORT can be imported into Oracle8. 8. The transplantation of the application of Application should have no problem. Only in the case of the migration problem: Application uses the RowID Table including the RowID type field If the program has the following case, you must use the dbms_rowid package: Brainless RowID ourselves ROWID If it is only to pass RowID to variables, or just as a Overall use, you can not be affected. 9. Data Migration Problem No matter the use of Export / Import, the RowID field in Oracle7 is automatically expanded in Oracle8. If you contain ROWID in a field content, you must use the DBMS_ROWID package to convert. 10, DBMS_ROWID package is created by $ oracle_home / rdbms / admin / dbmsutil.sql, in fact, in CatProc.sql. Provide some functions to process RowID.
