Ways to access indexes

First of all, the following refers to the Oracle database is the most common B-tree index, Oracle data Other types of indexes are not considered, B-tree index is like a long tree, it contains two types of data blocks, one is the index branch block, the other is indexed leaf block.

The index branch block contains pointers to the response Index branch/leaf block and the index key value column (the pointer here refers to the block address of the related branch/leaf block RDBA, each index branch block has two types of pointers, one is LMC, and the other is a pointer to the index row record of the index branch block. LMC is the abbreviation for the left most child, each index branch has only one LMC, and the maximum value of all index key columns in the branch/leaf block that the IMC points to must be the minimum value in the index key column of the index branch block where the LMC is located. While the index row record of the index branch block records the pointer to the branch/leaf block, the minimum value of all index key column values must be greater than or equal to the value of the index key column of the row record. The operation to access the B-number index in Oracle must start at the root node, That will go through a process from the root node to the branch block to the leaf block.

The index leaf block contains the index key value and the ROWID that is used to locate the actual physical storage location of the data row where the index key value resides in the table. For a unique B-tree index, ROWID is a wardrobe stored in an index row, so there is no need to store the ROWID length at this time. For non-unique B-Number indexes, ROWID is stored as an additional column with the indexed key-value column, so that at this point, Oracle stores both the ROWID and its length, which means that under the same conditions, the unique B-Tree index saves the index-block storage space compared to the non-unique B-Tree index. For non-unique indexes, the ordering of the B-tree index is manifested in that Oracle will sort by the indexed key value and the corresponding rowid. The index leaf blocks in Oracle are interconnected by the left and right, which is equivalent to having a two-way pointer linked list that connects the indexed leaf blocks to each other.

Because of these features, the B-tree index in the Oracle database has the following advantages:

(1) All index leaf blocks are at the same level, that is, they are the same depth from the index root node, which also means that accessing any index key value of the index leaf block takes almost the same amount of time.

(2) Oracle will ensure that all B-tree indexes are self-balancing, that is, it is not possible to have different index leaf blocks not on the same layer.

(3) The efficiency of accessing the table's Row records through the B-tree index does not decrease significantly with the data volume of the related table, that is, the time to access the data through the index is controllable and basically stable, which is the biggest difference between the index and the full table scan. The biggest disadvantage of full-table scanning is that its access time is not controllable and unstable, that is, the time spent in full-table scanning increases as the amount of data in the target table increases.

The above structure of the B-tree index determines that the process of accessing data through the B-tree index in Oracle accesses the relevant B-tree index first, and then accesses the corresponding data row records based on the rowid that are obtained after accessing the index (if, of course, the data accessed by the target SQL can be obtained by accessing the relevant B-tree index , then it is no longer necessary to return the table). Accessing the relevant B-tree index and the back table requires I/O, which means that the cost of accessing the index in Oracle consists of two parts: the cost of accessing the relevant B-tree index (from the root node to the related branch block, the associated leaf block, and the last scan of the leaf blocks) The other part is the return table cost (according to the resulting rowid and then back to the table to scan the data block where the corresponding data row resides)

Here are some common ways to access B-Tree indexes in Oracle

(1) Index unique scan

The index unique scan, which is a scan of a unique index, applies only to the target SQL that is the equivalent query in the WHERE condition. Because the scanned object is a unique index, the results of the index uniqueness scan will return at most one record

(2) Index range scan

The index range scan is applied to all types of B-tree indexes, and when the scanned object is a uniqueness index, the where condition of the target SQL must be a range query (predicate condition is between,<,>), and when the scanned object is a non-unique index, There is no limit to the where condition of the target SQL (it can be an equivalent query or a time range query). The result of an index range scan may return multiple records, which is essentially what the range "scope" means in the index range scan.

It is important to note that, even for the same SQL under the same conditions, when the number of index rows for the target index is greater than 1 o'clock, the index range scan consumes more logical reads than the index unique scan consumes. This is because all unique scan results return only one record at most, so Oracle knows for sure that only the relevant leaf blocks need to be accessed at a time to return directly, but for an indexed range scan, because its scan results may return multiple records, and because the index row number is greater than 1, In order to determine the end of the index range scan, Oracle had to access the associated leaf block more than once, so under the same conditions, when the number of index rows in the target index was greater than 1 o'clock, the logical reads consumed by the index range scan would be at least 1 more logical than the corresponding index uniqueness.

Experimental validation:

Sql> CREATE TABLE Emp_temp as SELECT * from EMP;

Table created.

The number of non-null values for column empno in Emp_temp now is 13 (this means that if a B-tree index of a single-key value is built on the column empno of the table emp_temp, then the index row for that index must be greater than 1)

Sql> Create unique index idx_emp_temp on emp_temp (empno);

Index created.

Then collect the statistics for the table emp_temp and index idx_emp_temp:

sql> exec dbms_stats.gather_table_stats (ownname=> ' SCOTT ',tabname=> ' emp_temp ', estimate_percent=>10 0, Cascade=>true,method_opt=> ' For all columns size 1 ');

PL/SQL procedure successfully completed.

To avoid the effect of the buffer cache and data dictionary cache (Dictionary cached) on the logical read statistic results. We emptied buffer cache and data dictionary caches

Sql> alter system flush Shared_pool;

System altered.

Sql> alter system flush Buffer_cache;

System altered.

Execution Plan:

Sql> SELECT * from Emp_temp where empno=7369;

Execution Plan

----------------------------------------------------------

Plan Hash value:3451700904

--------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

--------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT |   | 1 | 38 | 1 (0) | 00:00:01 |

|  1 | TABLE ACCESS by INDEX rowid| Emp_temp | 1 | 38 | 1 (0) | 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | Idx_emp_temp | 1 |   | 0 (0) | 00:00:01 |

-------------------------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

2-access ("EMPNO" =7369)

Statistics

----------------------------------------------------------

Recursive calls

0 db Block gets

Consistent gets

Physical Reads

0 Redo Size

889 Bytes sent via sql*net to client

Bytes received via sql*net from client

1 sql*net roundtrips To/from Client

6 Sorts (memory)

0 Sorts (disk)

1 rows processed

As you can see from the above display, the SQL execution plan goes through an index-unique scan, which consumes a logical read of 73.

Now we drop the unique index

sql> DROP Index idx_emp_temp;

Index dropped.

Then create a single-key value on the column empno of table emp_temp with the same name as the B-Tree index idx_emp_temp:

Sql> CREATE index idx_emp_temp on emp_temp (empno);

Index created.

Collect statistics:

sql> exec dbms_stats.gather_table_stats (ownname=> ' SCOTT ',tabname=> ' emp_temp ', estimate_percent=>10 0, Cascade=>true,method_opt=> ' For all columns size 1 ');

PL/SQL procedure successfully completed.

Sql> Set Autot traceonly

Sql> alter system flush Shared_pool;

System altered.

Sql> alter system flush Buffer_cache;

System altered.

Sql> SELECT * from Emp_temp where empno=7369;

Execution Plan

----------------------------------------------------------

Plan Hash value:351331621

--------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

---------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT |   | 1 | 38 | 2 (0) | 00:00:01 |

|  1 | TABLE ACCESS by INDEX rowid| Emp_temp | 1 | 38 | 2 (0) | 00:00:01 |

|* 2 | INDEX RANGE SCAN | Idx_emp_temp | 1 |   | 1 (0) | 00:00:01 |

---------------------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

2-access ("EMPNO" =7369)

Statistics

----------------------------------------------------------

Recursive calls

0 db Block gets

Consistent gets

Physical Reads

0 Redo Size

1025 Bytes sent via sql*net to client

523 Bytes received via sql*net from client

2 sql*net roundtrips To/from Client

6 Sorts (memory)

0 Sorts (disk)

1 rows processed

The result shows that this SQL execution plan has changed from the previous index unique scan to the current index range scan, the cost of the logical read from the previous 73 increments to 74, which shows that under the same conditions, when the target index of the index row number is greater than 1 o'clock, The logical read knowledge consumed by index range scanning is 1 more than the corresponding index unique scan.

Ways to access indexes