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

Source: Internet
Author: User

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.

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/

PL/SQL procedure successfully completed.
Clear the buffer cache and shared pool again, and never execute them in the production environment.
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
SQL> select * from test where empno = 7369;

Execution Plan
----------------------------------------------------------
Plan hash value: 1320605699

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

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

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

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

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

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

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


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

2-access ("empno" = 7369)


Statistics
----------------------------------------------------------
813 recursive cballs
0 db block gets
165 consistent gets
27 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
9 sorts (memory)
0 sorts (disk)
1 rows processed

From the execution plan content, the SQL Execution Plan changes from the previous unique index scan to the index range scan, and the logical read value is changed from 164 to 165, one more scan than the original.

3. Full index Scan
Full index scan applies to all B-tree indexes. Full index scan scans all indexes of all leaf blocks. By default, full index scan scans all the indexes of all leaf blocks from left to right sequentially. The results of full index scan are also ordered.
SQL> select empno from test
2;

EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876

EMPNO
----------
7900
7902
7934

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 654388723

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
------------------------------------------------------------------------------
| 0 | select statement | 14 | 56 | 1 (0) | 00:00:01 |
| 1 | index full scan | IDX_EMPNO | 14 | 56 | 1 (0) | 00:00:01 |
------------------------------------------------------------------------------


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

From the execution plan, we can see that the values of "sorts (memory)" and "sorts (disk)" in the Statistics Section are both 0. the SQL Execution result is sorted by the column empno.
 
4. Fast and full index Scanning
Index fast full scan is very similar to index full scan. It applies to all B-tree indexes.
To quickly scan indexes, scan all the index rows of all leaf blocks.
Their differences are as follows ::
1) fast and full index scan is only applicable to CBO
2) You can use multiple reads or execute indexes in parallel for fast and full indexing.
3) The results of fast and Full Indexing are not necessarily ordered. This is because oracle scans indexes in the physical storage order of the index rows on the disk, rather than in the logical order of the index rows.
5. Index skip Scanning
Index skip scan is applicable to all Compound B-tree indexes. 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. This is like skipping its leading column when scanning an index and scanning it directly from its non-leading column.
Let's look at an example:
SQL> create table test1 (name varchar2 (10), id number not null );
Create a composite B-Tree Index
SQL> create index idx_naid on test1 (name, id );
Insert 10000 records, 5000 name columns as test, and 5000 name columns as prot
Begin
For I in 1 .. 5000 loop
Insert into test1 values ('test', I );
End loop;
Commit;
End;
/
Begin
For I in 1 .. 5000 loop
Insert into test1 values ('not', I );
End loop;
Commit;
End;
/
SQL> SET AUTOTRACE TRACEONLY
SQL> exec dbms_stats.gather_table_stats ('Scott ', 'test1', estimate_percent => 100, cascade => true, method_opt =>' for all columns size 1 ');
SQL & gt; select * from test1 where id = 200;
Execution Plan
----------------------------------------------------------
Plan hash value: 4123651466

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-----------------------------------------------------------------------------
| 0 | select statement | 2 | 16 | 3 (0) | 00:00:01 |
| * 1 | index skip scan | IDX_NAID | 2 | 16 | 3 (0) | 00:00:01 |
-----------------------------------------------------------------------------

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

1-access ("ID" = 200)
Filter ("ID" = 200)


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

In this example, the where condition is id = 200. Only the query condition is specified for the second column id of the composite B-tree index, and no query condition is specified for the name of the leading column of the index. If no leading column is specified, the index can be used because oracle automatically traverses all the distinct values of the leading column of the index.

From the analysis process in the example, the oracle index skip scan is only applicable to the situations where the number of distinct values of the target index's leading columns is small, and the non-leading columns are optional and very good in the future. The execution efficiency of the index skip scan will decrease with the increasing number of distinct values in the leading column of the target index.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.