Oracle Performance Analysis 5: Introduction to the index structure and scanning methods of data access

The previous article covered the full scan, which will describe the structure and scanning of the index, and will begin to describe each of these scanning methods later.
An index scan is performed when Oracle retrieves specific column values for one or more columns by index. Let's start by looking at the data that the index node contains.

The data that the index node contains

Indexes can be created on a single column or on multiple columns of a table, and the indexes contain values, ROWID, and other information for those columns, and we care only about column values and ROWID. Because the index has column values, so if your SQL statement involves only the indexed columns, Oracle retrieves the column values only from the index itself, without needing to access the table. If the query involves columns other than the indexed columns, Oracle needs to use ROWID to access the table.
Here is an example of a rowid:

Aaan0+aabaaapiqabj

The rowid contains the file number, block number, and line number, and with the following SQL we can decompose the rowid into readable and specific information using the previously created table T2:

Select T.rowid,       (select file_name from          dba_data_files         where file_id =               Dbms_rowid.rowid_to_absolute_ FNO (t.rowid, user, ' T2 ')) file_name,       dbms_rowid.rowid_block_number (T.rowid) bokc_no,       Dbms_rowid.rowid_row_ Number (T.rowid) row_no from  T2 t

Results are obtained after execution:

Rowidfile_namebokc_norow_noaaan0+aabaaapiqaaae:\oracle\oradata\ly\system01. Dbf619940aaan0+aabaaapiqaabe:\oracle\oradata\ly\system01. Dbf619941aaan0+aabaaapiqaace:\oracle\oradata\ly\system01. DBF619942 ... Aaan0+aabaaapiqajde:\oracle\oradata\ly\system01. Dbf61994605aaan0+aabaaapiqajee:\oracle\oradata\ly\system01. Dbf61994606aaan0+aabaaapiqajfe:\oracle\oradata\ly\system01. Dbf61994607aaan0+aabaaapiqajge:\oracle\oradata\ly\system01. Dbf61994608aaan0+aabaaapiqajhe:\oracle\oradata\ly\system01. Dbf61994609aaan0+aabaaapiqajie:\oracle\oradata\ly\system01. Dbf61994610aaan0+aabaaapiqajje:\oracle\oradata\ly\system01. Dbf61994611aaan0+aabaaapiraaae:\oracle\oradata\ly\system01. Dbf619950aaan0+aabaaapiraabe:\oracle\oradata\ly\system01. Dbf619951aaan0+aabaaapiraace:\oracle\oradata\ly\system01. Dbf619952aaan0+aabaaapiraade:\oracle\oradata\ly\system01. Dbf619953aaan0+aabaaapiraaee:\oracle\oradata\ly\system01. DBF619954 ... Aaan0+aabaaapirajve:\oracle\oradata\ly\system01. Dbf61995597aaan0+aabaaapirajwe:\oracle\oradaTa\ly\system01. Dbf61995598aaan0+aabaaapirajxe:\oracle\oradata\ly\system01. Dbf61995599aaan0+aabaaapirajye:\oracle\oradata\ly\system01. Dbf61995600aaan0+aabaaapirajze:\oracle\oradata\ly\system01. Dbf61995601aaan0+aabaaapirajae:\oracle\oradata\ly\system01. Dbf61995602aaan0+aabaaapisaaae:\oracle\oradata\ly\system01. Dbf619960aaan0+aabaaapisaabe:\oracle\oradata\ly\system01. Dbf619961aaan0+aabaaapisaace:\oracle\oradata\ly\system01. DBF619962 ...

This will give you the exact location of all the rows in the table T2, including the file, data block number, and line number within the block. We can see the distribution of the data.
It is important to note that the DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO function is defined as follows:

function Dbms_rowid.rowid_to_absolute_fno (rowID in rowid,schema_name with Varchar2,object_name in VARCHAR2) return number

1) Rowid:rowid;
2) schema_name: User name, this is the current user
3) object_name: Object name, here is T2

So, with the above description we can get the steps to find the data through an index scan:
1) Get indexed data block, get index column and ROWID;
2) If the query involves only indexed columns, the query ends;
3) Otherwise locate the data block by ROWID and navigate to the data by line number.

Introduction to index structure and index scan types

Only the B-tree index is discussed here, and the tree index is a tree structure. When a table is created as a blank, the corresponding index will only have one root node, the index height is 1, and the index has a blevel statistic to represent the number of branch levels in an index, which is 0, which can be obtained by the following query:

Select Index_name,blevel from user_indexes where index_name =upper (' index_name ');

As new data is inserted into the table, the new index entry is added to the block until the block is full, and Oracle allocates two new index blocks and adds the index entry to the two new leaf blocks, and the previous index block becomes a pointer to the new index block. This pointer contains the relative block address of the new index block (Relative block Address,rba) and the lowest index value in the associated leaf block. By this point, the height of the index will change to a 2,blevel value of 1.
As the data in the table continues to grow, the index blocks are further split, the heights continue to grow, and eventually a tree structure is formed:

Understanding the structure of the index makes it easy to understand index scans, which have many different types, but all have to traverse the index structure to search for matching leaf nodes. First, the root block of the index is obtained by a single block of reads, and then the block of the path node is obtained through multiple chunks of read, until the block where the leaf node is located (the matching block), the rowid of the data from the matching leaf node, and a row of data is obtained by using a single block read through ROWID, so if the index structure Querying a row of data requires reading 5 blocks, 4 index blocks, and one table data block.
Index scan types include: Index range scan, index unique scan, index full scan, index hop scan, and index fast full scan. The features and application scope of each scanning mode are described in detail later.

Oracle Performance Analysis 5: Introduction to the index structure and scanning methods of data access