Five most common access methods for B-tree indexes in Oracle

Today, we will discuss the most common B-tree indexes in Oracle databases. First, let's take a look at the structure of B-tree indexes in Oracle databases. The B-tree index in the Oracle database is like

Today, we will discuss the most common B-tree indexes in Oracle databases. First, let's take a look at the structure of B-tree indexes in Oracle databases. The B-tree index in the Oracle database is like

Today, we will discuss the most common B-tree indexes in Oracle databases. First, let's take a look at the structure of B-tree indexes in Oracle databases.

We can see that the B-tree index in the Oracle database is like an inverted-long tree, which contains two types of data blocks. One is the index branch block (L1-1, L1-2), the other is the index leaf block (L0-1, L0-2, L0-3, L0-4, L0-5, L0-6 ). The index branch contains pointers and index key-value columns pointing to the corresponding index branch blocks and leaf blocks. the index key-value column is not necessarily a complete index key value. It may only be the index key-value prefix. As long as Oracle can distinguish the corresponding index branch blocks through these prefixes, the leaf blocks will be fine, in this way, Oracle can not only save the storage space of index branch blocks, but also quickly locate the index branch blocks and leaf blocks at the lower layer. The top block of the index branch is the so-called index root node. Is the upper-level root block in the figure that contains BC. Accessing B-book indexes in Oracle must start from the root node and go through the 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 used to locate the actual physical storage location of the Data row of the index key value in the table.

For a unique B-tree index, ROWID is the row header stored in the index row. Therefore, Oracle does not need to store the rowid length.

For non-unique B-tree indexes, ROWID is stored as an additional column and an index key-value column. Therefore, Oracle needs to store both rowid and length, this means that, under the same conditions, the unique B-tree index saves the storage space of the index leaf block than the non-unique B-tree index. For non-unique indexes, the ordering of the B-tree indexes is reflected in Oracle's Union sorting by the index key value and rowid. The Oracle index leaf block is a two-way pointer linked list that connects the Left and Right index leaf blocks to each other without having to traverse the process from a root node to a branch block to a leaf block.

Because of the structure characteristics of the B-tree index, the B-tree index in Oracle database has the following advantages.

1. All the index leaf blocks are on the same layer, and their index depth is the same. This means that it takes almost the same time to access any index key value of the index leaf block.

2. Oracle can ensure that all B-tree indexes are self-balanced, and different index leaf blocks cannot be on the same layer.

3. The efficiency of accessing Row Records in the table through the B-tree index will not significantly decrease with the increasing data volume of the relevant table.

The structure of the B-tree index determines that the process of accessing data through the B-tree index is to first access the relevant B-tree index, and then according to the rowid obtained after accessing the index, then access the data Row Records corresponding to the table. If the required data can be accessed through the B-tree index, you do not need to access the table any more. IO is required for accessing B-tree indexes and tables. This means that the cost of accessing the index in oracle consists of two parts, one is the cost of accessing the B-tree index (from the root node to the branch block, then to the relevant leaf block, finally, scan the leaf blocks)
The other part is the cost of the azimuth table (obtain the ROWID Based on the B-tree index and then scan the data block corresponding to the corresponding data row in the table ).
The B-tree index has five access methods.
1. Unique index Scan
Index unique scan is a SCAN of the unique index. It is only applicable to the SQL statements with equivalent queries in the where condition, because the scanned object is a UNIQUE INDEX, therefore, only one record is returned for the index uniqueness scan result.
2. index range scanning
Index range scan is applicable to all types of B-tree indexes. When the scanned object is a unique INDEX, the SQL where condition must be between (<> ); when the scanned object is a non-unique index, there is no restriction on the SQL where condition, such as =, between, and <>. Index range scan results may return multiple records.
Under the same conditions, when the number of index rows of the target index is greater than 1, the index range scan consumes at least 1 more logical read than the corresponding index uniqueness scan.
Let's make an experiment to verify the conclusion.
SQL> create table test as select * from emp;
SQL> select count (empno) from test;
COUNT (EMPNO)
-----------------
13

The number of non-null values of the empno column in the test table is 13, which means that the B-tree index is created on the empno column in the test table. The number of index rows must be greater than 1.

Create a unique B-tree index idx_empno ON THE empno column of the test table.
SQL> create unique index idx_empno on test (empno );

Index created.
Collect the test table and idx_empno Indexes
SQL> begin
2 dbms_stats.gather_table_stats ('Scott ', 'test', estimate_percent => 100, cascade => true, method_opt =>' for all columns size 1 ');
3 end;
4/

PL/SQL procedure successfully completed.
SQL> alter system flush shared_pool;
SQL> alter system flush buffer_cache;
SQL> set autotrace traceonly
SQL> select * from test where empno = 7369;
Execution Plan
----------------------------------------------------------
Plan hash value: 3039750644

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

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | T
Ime |

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

| 0 | select statement | 1 | 37 | 1 (0) | 0
At 0:00:01 |

| 1 | table access by index rowid | TEST | 1 | 37 | 1 (0) | 0
At 0:00:01 |

| * 2 | index unique scan | IDX_EMPNO | 1 | 0 (0) | 0
At 0:00:01 |

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

Predicate Information (identified by operation id ):
---------------------------------------------------

2-access ("empno" = 7369)

Statistics
----------------------------------------------------------
1088 recursive cballs
0 db block gets
164 consistent gets
23 physical reads
0 redo size
822 bytes sent via SQL * Net to client
385 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
1 rows processed

From the above execution plan content, we can see that the execution plan uses the index uniqueness scan, and the consumed logic reads 164.
Step 2: delete the unique index idx_empno.
SQL> DROP INDEX IDX_EMPNO;
Create a non-unique B-tree index.
SQL> CREATE INDEX IDX_EMPNO ON TEST (EMPNO );
Index dropped.
Collect statistics again
SQL> begin
2 dbms_stats.gather_table_stats ('Scott ', 'test', estimate_percent => 100, cascade => true, method_opt =>' for all columns size 1 ');
3 end;
4/