Database access using Oracle rowid

Like rownum, rowid is a pseudo column, which is a non-user-defined column and is actually stored in the database. Each table has a rowid column, and a rowid value is used
Uniquely identifies a record in a database table. Therefore, using rowid to access data is also one of the implementation methods for accessing data from Oracle databases. Generally
Access must be based on index access or rowid specified by the user as a prerequisite, because all index access methods will eventually be converted to access data records through rowid. (Note: Index full
Except scan and index fast full scan) Because Oracle rowid can directly locate a record, it uses rowid to access data, greatly improving data access efficiency.

--> View the table rowidscott @ cnmmbo> select rowid, empno, ename from EMP where deptno = 20; rowid empno ename ---------------- ---------- aaattbaalaaaauuaaa 7369 bytes 7566 bytes 7788 bytes 7876 adamsaaattbaalaaaauuaam 7902 Ford --> the following uses rowid for access --> physical reads are ignored in the following demonstration, only logical read is considered. Scott @ cnmmbo> set autot trace; Scott @ cnmmbo> select empno, ename from EMP where rowid = 'Aaattbaalaaaauuaaa'; Execution Plan ---------------------------------------------------------- plan hash value: 1116584662 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | minute | 0 | SELECT statement | 1 | 22 | 1 (0) | 00:00:01 | 1 | table access by user rowid | EMP | 1 | 22 | 1 (0) | 00:00:01 | Statistics limit 1 recursive cballs 0 dB block gets 1 consistent gets 0 physical reads -- in this case, operation 1 in the execution plan is table access by user rowid. Note that this is user rowid, indicates that the consistent gets in the statistical information is 1, that is, data can be returned Based on rowid. When multiple rowids exist, Scott @ cnmmbo> select empno, ename from EMP where rowid in ('aaattbaalaaaauuaa', 'aaattbaalaaaauuad'); Execution Plan into plan hash value: 1106538681 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | ---------------------------------------------- ------------------------------------ | 0 | SELECT statement | 1 | 22 | 1 (0) | 00:00:01 | 1 | inlist iterator | 2 | table access by user rowid | EMP | 1 | 22 | 1 (0) | 00:00:01 | Statistics ---------------------------------------------------------- 1 recursive cballs 0 dB block gets 2 consistent gets 0 PHY Sical reads -- inlist iterator appears in the preceding execution plan, that is, inlist iteration. This operation indicates that this operation appears when its suboperations are repeated multiple times. -- Because we use the in operation and pass two rowids, therefore, an inlist iteration operation occurs. The iteration operation means that the object list in the condition is passed to the suboperation one by one iteration. In this case, the consistent gets in the statistics information is 2, A rowid is a logical operation. Let's take a look at how columns are directly used to access the table. Scott @ cnmmbo> select empno, ename from EMP where empno = 7369; Execution Plan into plan hash value: 2949544139 bytes | ID | operation | Name | Rows | bytes | cost (% CPU) | time | bytes | 0 | SELECT statement | 1 | 10 | 1 (0) | 00:00:01 | 1 | table access by index rowid | EMP | 1 | 10 | 1 (0) | 00:00:01 | * 2 | index unique scan | pk_emp | 1 | 0 (0) | 00:00:01 | reset predicate info Rmation (identified by Operation ID): --------------------------------------------------- 2-access ("empno" = 7369) statistics -------------------------------------------------------- 1 recursive cballs 0 dB block gets 2 consistent gets 0 physical reads -- when accessing a table through a predicate, index unique scan is used in this execution plan -- index unique scan is implemented first, then pass the result of the operation to the parent operation table access by index rowid to locate the record-the predicate information is also given: 2-access ("empno" = 7369) -- The value of logical read consistent gets is 2, that is, one read index, read data blocks in a table using rowid at a time. Let's take a look at the situation where columns are directly used and multiple records are accessed using in. Scott @ cnmmbo> select empno, ename from EMP where empno in (72.16,7566); Execution Plan against plan hash value: 1899965127 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | ------- ------------------------------------------------------------------------------ | 0 | SELECT statement | 2 | 20 | 2 (0) | 00:00:01 | 1 | inlist iterator | 2 | table access by index rowid | EMP | 2 | 20 | 2 (0) | 00:00:01 | * 3 | index range scan | pk_emp | 2 | 1 (0) | 00:00:01 | reset predicate Information (Identified by Operation ID): --------------------------------------------------- 3-access ("empno" = 7369 or "empno" = 7566) statistics limit 1 recursive cballs 0 dB block gets 5 consistent gets 0 physical reads -- in this case, the index scan method in the execution plan is changed to index range scan -- because of the in operation, therefore, an iterative operation occurs in step 1. What is the logical read consistent gets value 5? Why is it 5? Let's analyze Scott @ cnmmbo> alter system flush shared_pool; Scott @ cnmmbo> alter system flush buffer_cache; Scott @ cnmmbo> set serveroutput off; Scott @ cnmmbo> set autot off; scott @ cnmmbo> select/* + gather_plan_statistics */empno, ename from EMP where empno in (72.16,7566 ); empno ename ---------- 7369 Smith 7566 jonesscott @ cnmmbo> select * from table (dbms_xplan.display_cursor (null, null, 'iostats last ') ); PLAN_TABLE_OUTPUT----------------------------------------------------------------------------SQL_ID 373xnw8s521t4, Child number 0 distinct select/* + gather_plan_statistics */empno, ename from EMP where empno in (72.16,7566) plan hash value: 1899965127 bytes | ID | operatio N | Name | starts | E-rows | A-time | buffers | reads | tables | 1 | inlist iterator | 1 | 2 | 00:00:00. 01 | 5 | 2 | 2 | table access by index rowid | EMP | 2 | 2 | 00:00:00. 01 | 5 | 2 | * 3 | index range scan | pk_emp | 2 | 2 | 2 | 00:00:00. 01 | 3 | 1 | ---------------------------------- Required predicate information (identified by Operation ID): ------------------------------------------------- 3-access ("empno" = 7369 or "empno" = 7566)-from the preceding execution plan, 2 Index scans, 2 rowid scans, and 1 iteration, so the total number of consistent gets is 5. -- Similarly, the rowid-based update operation is used, and its performance is also higher than that of using columns directly for update operations, this example is not demonstrated here -- delete from EMP e where E. rowid> (select Min (X. rowid) from emp x where X. empno = E. empno); -- Author: Robinson Cheng -- Blog: http://blog.csdn.net/robinson_0612 -- Summary: Using rowid to access data can reduce the number of logical reads, because a rowid can uniquely locate a record, although rowid can greatly improve data access efficiency, it is not easy to use because it is not easy to identify (in hexadecimal format) when a large amount of data is accessed.

More references

Oracle rowid

Null Value and index (1)

Null Value and index (2)

SQL tuning steps

Efficient SQL statements

Parent cursor, child cursor, and shared cursor

Bind variables and their advantages and disadvantages

Use of the display_cursor function of dbms_xplan

Use of the display function of dbms_xplan

Description of each field module in the execution plan

Use explain plan to obtain the SQL statement execution plan

Enable autotrace

The function invalidates the index column.

Oracle variable binding

Oracle adaptive shared cursor