How to use rowid in oracle

In Oracle, rowid is used to access data. It is a pseudo column that uniquely marks the rows in the table. Each row of data in the table has a unique identifier. The syntax is "select rowid..." ;rowid is the internal address of the row data in the physical table. One of them points to the address of the data file stored in the block containing the row in the data table, and the other is the address of this row in the data block that can directly locate the data row itself.

The operating environment of this tutorial: Windows 10 system, Oracle version 12c, Dell G3 computer.

rowid in oracle

rowid is a pseudo column used to uniquely mark rows in the table. It is the internal address of the row data in the physical table. It contains two addresses. One is the address of the data file stored in the block containing the row in the data table, and the other is the row that can directly locate the data row itself in the data block. address in .

Each row of data in the Oracle database table has a unique identifier, or rowid, which is usually used to access data within Oracle. rowid requires 10 bytes of storage space and uses 18 characters to display. This value indicates the specific physical location of the row in the Oracle database. Rowid can be used in a query to indicate that the value is included in the query results.

AAAR1yAAHAAAAFkAAA as an example

Here AAAR1y is the database object number, AAH is the file label, AAAAFk is the block number, and the last three digits AAA are the row number.

Use select * from DEPT; You cannot see the rowid column in the output result. This is because this column is only used internally in the database, and rowid is usually called a pseudo column.

If you want to select the data of

scott.emp and then modify it manually, you must write

select rowid,t.* from scott.emp t;

instead of ## directly. #

select * from emp;

select rowid,t.* from scott.emp t;
select * from scott.emp for update;

Using ROWID to locate records in ORACLE is the fastest, faster than indexing, so if you first use SELECT ROWID to select the rows to be updated, put them in COLLECTION, and then use FORALL UPDATE to batch update, it can improve speed. From this point of view, it is better than other methods

SELECT FOR UPDATE will lock the record before updating, which is necessary in complex parallel query update programs, such as requiring data consistency, when filtering data Others are not allowed to modify the data and will be locked with FOR UPDATE or SET TRANSACTION READ ONLY. In addition, the WHERE CURRENT OF CURSOR statement in CURSOR requires that FOR UPDATE must be added to SELECT.

Recommended tutorial: "

Oracle Video Tutorial"

The above is the detailed content of How to use rowid in oracle. For more information, please follow other related articles on the PHP Chinese website!