ORACLE Data Access Method

The data to be accessed by the target SQL statement is generally stored in the table, while the data in the ORACLE access table can be accessed in two ways: one is to access the table directly, and the other is to access the index first, in the back table (of course, if the data to be accessed by the target SQL statement can be obtained only by accessing the relevant index, you do not need to go back to the table ).

ORACLE table access method: full table scan and ROWID scan.

Full table scan means that ORACLE scans the table from the first block in the first partition (extent) occupied by the table when accessing the table, the table's HWM (hich water mark) is always scanned. All data blocks in this range must be read by ORACLE. Of course, ORACLE will apply the filtering conditions specified in the WHRER condition of the target SQL statement to all data read during this period, and finally return data that only meets the conditions. Full table scan can use multiple read attributes.

During ROWID scanning, ORACLE directly locates and accesses the data in the target table through the ROWID of the data. ROWID indicates the physical storage address where the data Row Distance in ORACLE is located. That is to say, ROWID actually corresponds to the row records in the data block in ORACLE. Strictly speaking, ROWID scanning in ORACLE has two meanings: one is that the ROWID value entered by the user in the SQL statement directly accesses the corresponding data row record; the other is to first access the relevant index, and then access the corresponding data row records based on the rowid of the accessed index.

1. ROWID Scanning

ROWID is a pseudo column in the ORACLE database and does not exist in the actual table block. Next we will look at an instance that uses the ROWID pseudo column and DBMS_ROWID package.

SQL> set linesize 180
SQL> select employee_id, first_name, rowid, dbms_rowid.rowid_relative_fno (rowid) | '_' |
Dbms_rowid.rowid_block_number (rowid) | '_' | dbms_rowid.rowid_row_number (rowid)
Location from emp_temp where manager_id = 100; 2 3

EMPLOYEE_ID FIRST_NAME ROWID LOCATION
----------- -------------------- ------------------ Success ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
201 Michael AAAM0YAAEAAAAGEAAD 4_388_3
101 Neena AAAM0YAAEAAAAGEAAK 4_388_10
102 Lex AAAM0YAAEAAAAGEAAL 4_388_11
114 Den AAAM0YAAEAAAAGEAAX 4_388_23
120 Matthew AAAM0YAAEAAAAGEAAd 4_388_29
121 Adam AAAM0YAAEAAAAGEAAe 4_388_30
122 Payam AAAM0YAAEAAAAGEAAf 4_388_31
123 Shanta AAAM0YAAEAAAAGEAAg 4_388_32
124 Kevin AAAM0YAAEAAAAGEAAh 4_388_33
145 John AAAM0YAAEAAAAGEAA2 4_388_54
146 Karen AAAM0YAAEAAAAGEAA3 4_388_55
147 Alberta to AAAM0YAAEAAAAGEAA4 4_388_56
148 Gerald AAAM0YAAEAAAAGEAA5 4_388_57
149 Eleni AAAM0YAAEAAAAGEAA6 4_388_58

14 rows selected.

As shown in the preceding figure, the value of the ROWID pseudo-column corresponding to the row record with EMPLOYEE_ID = 201 is "too many rows without AAAM0YAAEAAAAGEAAD ", the translated value of this pseudo column using the DBMS_ROWID package is "4_388_3", which indicates that the actual physical storage address of the row record with the value of EMPLOYEE_ID 201 is located in the 388th rows of the 3rd blocks of file 4. (Note that the row record in the data block starts from the record number 0 ).

The value of the preceding ROWID pseudo column can be directly used in the WHERE clause of the SQL statement as follows:

SQL> select employee_id, first_name, rowid from emp_temp where rowid = 'aaam0yaaeaaaageaad ';

EMPLOYEE_ID FIRST_NAME ROWID
-------------------------------------------------
201 Michael AAAM0YAAEAAAAGEAAD

2. How to access the index:

Note that the index mentioned here refers to the number B Index. Other ORACLE indexes are not considered for the moment.

All the operations to access B-tree indexes in ORACLE must start from the root node, that is, they all go through a process from the root node to the branch block and then to the leaf block. The index leaf block contains the indexed key value and the ROWID of the Data row used to locate the index key value in the table's actual physical storage location. The structure of the B-tree index determines that the process of accessing data through the B-tree index in ORACLE is to first access the relevant B-tree index, then, based on the ROWID obtained after accessing the index, return to the table to access the corresponding data Row Records (of course, if the data to be accessed by the target SQL statement is obtained only by accessing the relevant index, you do not need to return to the table ).

1. index unique scan is a SCAN of the unique index. It is only applicable to the target SQL statement that is equivalent to query in the WHERE condition. Because the scanned object has a unique index, only one record is returned for the result of the unique scan.

2. INDEX RANGE SCAN)

3. full index scan is applicable to all types of B-tree indexes. It is used to SCAN all the INDEX rows of all the leaf blocks of the target INDEX. It should be noted that the index scans all the leaf blocks of the target index, but does not mean to scan all the branch blocks of the index. The results of full index scan are ordered. By default, the order of the index scan results determines that the index scan cannot be performed in parallel. In general, the index scan uses a single read.

4. INDEX FAST FULL SCAN)

It is similar to full index scanning, and there are three differences between them:

A. Quick and full index scan is only applicable to CBO.

B. You can use multiple reads or execute the index in parallel for fast and full scanning.

C. The results of fast and Full Indexing are not necessarily ordered. This is because ORACLE scans the index rows in the physical storage order of the disk, rather than scanning the index rows in the logical order.

D. INDEX SKIP SCAN)

It allows the target SQL statements that do not specify the query conditions for the leading columns of the target index but specify the query conditions for the non-leading columns of the index in the WHERE condition to still use the index.