Oracle series: pseudo-column rowid

Previous Article: Oracle series: Oracle Data Types
Link: http://blog.csdn.net/qfs_v/archive/2008/05/12/2435213.aspx

Oracle series: pseudo-column rowid

1. What is a pseudo-column rowid?
1. First, it is a data type that uniquely identifies an ID of a record's physical location and is displayed based on 64-bit encoding with 18 characters.
2. Data is not stored in a table and can be queried from the table, but cannot be inserted, updated, or deleted.
 
Ii. Usage of rowid
1. It should be used frequently during development, especially in some update statements. Therefore, the rowid field is added to most views in Oracle ERP.
Rowid is also required for some cursor definitions. However, during the development process, many connected tables, coupled with program replication, sometimes ignore the rowid corresponding to the table, so sometimes the process is wrong,
We often send a lot of time to check for errors. At last, we find that the rowid in Update Is Not The rowid of this table. Therefore, when we find many errors, we pay attention to rowid, in development, you must pay attention to rowid matching.
2. You can access a row in the table in a quick way.
3. shows how rows are stored in a table.
4. It is the unique identifier of the table.

3. Composition of rowid
Rowid determines the data object in Oracle for each record, data files, blocks, and rows.
The rowid format is as follows:
Data Object no. File No. Block No. Row No.
Oooooo fff bbbbbb RRR

It is composed of data_object_id # + rfile # + block # + row # And occupies 10 bytes of space,
32bit data_object_id #,
10-bit rfile #,
22bit block #,
16-bit row #.
Therefore, each tablespace cannot have more than 1023 data files.
For more information about rowid composition, see: http://www.beelink.com/20071108/2430451.shtml

4. Application of rowid
1. Search for and delete duplicate records
When you try to create a unique index for one or more columns in the database table,
The system prompts ORA-01452: A unique index cannot be created and duplicate records are found.

/* Conn Scott/Tiger
Create Table EMPA as select * from EMP;
Insert duplicate records
Insert in to EMPA select * from EMP where empno = 7369;
Insert in to EMPA select * from EMP where empno = 7839;
Insert in to EMPA select * from EMP where empno = 7934;
*/
Several Methods for searching duplicate records:
Search for a large number of Repeated Records
Select empno from EMPA group by empno having count (*)> 1;
Select * from EMPA where rowid not in (select Min (rowid) from EMPA group by empno );
Find a small number of Repeated Records
Select * from empa a where rowid <> (select max (rowid) from EMPA where empno = A. empno );

Several Methods to delete duplicate records:
(1). applicable to the case of a large number of Repeated Records (when an index is built on a column, the efficiency of using the following statement will be high ):
Delete EMPA where empno in (select empno from EMPA group by empno having count (*)> 1)
And rowid not in (select Min (rowid) from EMPA group by empno having count (*)> 1 );

Delete EMPA where rowid not in (select Min (rowid) from EMPA group by empno );

(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 ):
Delete empa a where rowid <> (select max (rowid) from EMPA where empno = A. empno );

2. Restore data in the Oracle database
To be continued.

Next article: Oracle series: Record

If you have any questions or are correct, please contact:Qfs_v@qq.comThank you!
Reprinted, please indicate the source and author!