Shulou(Shulou.com)06/01 Report--

First of all, I would like to make it clear that the following is the most commonly used B-tree index in oracle database. Other types of indexes of oracle data are not considered for the time being. B-tree index is like an inverted tree, which contains two types of data blocks, one is index branch block, the other is index leaf block.

The index branch block contains a pointer to the response index branch block / leaf block and an index key value column (here the pointer refers to the block address RDBA of the relevant branch block / leaf block, each index branch block has two types of pointers, one is lmc, the other is the pointer to the index row record of the index branch block. Lmc is the abbreviation of left Most Child. Each index branch block has only one lmc. The maximum value of all index key value columns in the branch block / leaf block pointed to by this Imc must be lower than the minimum value in the index key value column of the index branch block in which the lmc is located. The minimum value of all index key value columns of the branch block / leaf block recorded by the index row record of the index branch block must be greater than or equal to the value of the index key value column of the row record.) the operation of accessing the B-number index in oracle must start from the root node, that is, it will 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 used to locate the actual physical storage location of the data row where the index key value is located in the table. For a unique B-tree index, the rowid is the header stored in the index row, so there is no need to store the length of the rowid at this time. For the non-unique B-tree index, rowid is stored as an extra column together with the indexed key-value column, so oracle should store both the rowid and its length, which 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 orderliness of B-tree indexes is reflected in that oracle will jointly sort according to the indexed key value and the corresponding rowid. The index leaf blocks in oracle are interconnected left and right, which means that there is a two-way pointer linked list that connects these index leaf blocks to each other.

Because of the above characteristics, the B-tree index in the oracle database has the following advantages:

(1) all index leaf blocks are on the same layer, that is, they are at the same depth from the index root node, which means that it takes almost the same time to access any index key value of the index leaf block.

(2) oracle ensures that all B-tree indexes are self-balanced, that is, it is impossible for different index leaf blocks to be in the same layer.

(3) the efficiency of accessing the row records of the table through the B-tree index does not decrease significantly with the increase of the amount of data in the related table, that is, the time of accessing the data through the index is controllable and basically stable, which is the biggest difference between walking the index and scanning the whole table. The biggest disadvantage of full table scanning is that its access time is uncontrollable and unstable, that is, the time spent on full table scanning will increase with the increase of the amount of data in the target table.

The above structure of the B-tree index determines that the process of accessing data through the B-tree index in oracle is to access the relevant B-tree index first, and then go back to the table to access the corresponding data row records according to the rowid obtained after accessing the index (of course, if the data accessed by the target sql can be obtained by accessing the relevant B-tree index, then there is no need to return to the table). The cost of accessing related B-tree indexes and returning tables consumes Ihand O, which means that the cost of accessing indexes in oracle consists of two parts: one is the cost of accessing related B-tree indexes (from the root node to the relevant branch blocks, then locating the relevant leaf blocks, and finally performing scanning operations on these leaf blocks); the other part is the cost of returning to the table (according to the resulting rowid to scan the data blocks of the corresponding data rows)

Here are some common methods to access the B-tree index in oracle

(1) Index unique scan

Index uniqueness scan (INDEX UNIQUE SCAN) is a scan for a unique index (UNIQUE INDEX), which is only applicable to the target sql that is an equivalent query in the where condition. Because the scanned object is a unique index, the result of the index uniqueness scan will return at most one record.

(2) Index range scanning

Index range scan (INDEX RANGE SCAN) is suitable for all types of B-tree indexes. When the scanned object is a unique index, the where condition of the target sql must be a range query (predicate condition is between,); when the scanned object is a non-unique index, there is no limit to the where condition of the target sql (can be equivalent query or time range query). The result of the index range scan may return multiple records, which is the essential meaning of the word "range" in the index range scan.

It should be noted that even for the same sql under the same conditions, when the number of index rows of the target index is greater than 1, the logical reads consumed by the index range scan will be more than the logical reads consumed by the index unique scan. This is because all unique scan results return at most one record, so oracle clearly knows that you only need to visit the relevant leaf block once to return directly. But for the index range scan, because the scan result may return multiple records, and because the number of index rows of the index is greater than 1, in order to determine the end point of the index range scan, the oracle has to visit the relevant leaf blocks one more time, so under the same conditions, when the number of index rows of the target index is greater than 1. Index range scanning consumes at least 1. 5% more logical reads than the corresponding index unique logical reads.

Experimental verification:

SQL > create table emp_temp as select * from emp

Table created.

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

SQL > create unique index idx_emp_temp on emp_temp (empno)

Index created.

Then collect statistics for table emp_temp and index idx_emp_temp:

SQL > exec dbms_stats.gather_table_stats (ownname= > 'SCOTT',tabname= >' EMP_TEMP',estimate_percent= > 100 true,method_opt= cascade100 > true,method_opt= > 'for all columns size 1')

PL/SQL procedure successfully completed.

In order to avoid the influence of buffer cache and data dictionary cache (DATA Dictionary Cache) on the results of logical read statistics. We clear the buffer cache and data dictionary caches

SQL > alter system flush shared_pool

System altered.

SQL > alter system flush buffer_cache

System altered.

Execute the 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

32 recursive calls

0 db block gets

67 consistent gets

13 physical reads

0 redo size

889 bytes sent via SQL*Net to client

512 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

6 sorts (memory)

0 sorts (disk)

1 rows processed

From the above display, it can be seen that the execution plan of the sql is an index unique scan, and the logical read cost is 73. 5%.

Now we drop off the unique index.

SQL > drop index idx_emp_temp

Index dropped.

Then create a single key value non-unique B-tree index idx_emp_temp with the same name on the column empno of table 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= > 100 true,method_opt= cascade100 > 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

32 recursive calls

0 db block gets

68 consistent gets

16 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 above results show that the execution plan of this sql has changed from the previous index unique scan to the current index range scan, and the logical read cost has also increased from 73 to 74, indicating that under the same conditions, when the number of index rows of the target index is greater than 1, the logical read knowledge consumed by the index range scan will be 1. 5% more than that of the corresponding index unique scan.

(3) full index scan

Index full scan (INDEX FULL SCAN) applies to all types of B-tree indexes (including unique and non-unique indexes). The so-called "index full scan" means to scan all index rows of all leaf blocks of the target index. It should be noted here that a full index scan requires scanning the index leaf blocks of the target index, but this does not mean that all the branch blocks of the index need to be scanned. By default, when oracle does a full scan of the index, it only needs to access the necessary branch blocks to locate the first index row of the leftmost leaf block, and then it can use the bi-directional pointer chain table between the leaf blocks of the index to scan all the index rows of all the leaf blocks of the index sequentially from left to right.

Since by default, the index full scan scans all the index rows of all the leaf blocks of the target index sequentially from left to right, and the index is ordered, so the execution result of the index full scan is also order. and it is sorted according to the index key value of the index, which also means that walking the index full scan can not only achieve the effect of sorting, but also avoid the real sorting operation on the index key value column of the index.

SQL > conn scott/scott

Connected.

SQL > set autot on

SQL > select empno from emp

EMPNO

-

7369

7499

7521

7566

7654

7698

7782

7788

7839

7844

7876

EMPNO

-

7900

7902

7934

14 rows selected.

Execution Plan

Plan hash value: 179099197

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

| | 0 | SELECT STATEMENT | | 14 | 56 | 1 (0) | 00:00:01 |

| | 1 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 (0) | 00:00:01 |

Statistics

31 recursive calls

0 db block gets

62 consistent gets

2 physical reads

0 redo size

686 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

14 rows processed

For the above sql, there is a single-key value B-tree primary key index PK_EMP on the column empno of table emp, so the attribute of column empno must be NOT NULL, and the slave query of this SQL only has empno, so oracle can scan the index of primary key index PK_EMP at this time.

From what is shown above, we can see that the execution plan of the sql does take a full scan of the primary key index pk_emp, and the execution results have been sorted according to empno. Note that the values of "sort (memory)" and "sort disk" in the "statistics" section of the above display are 0, indicating that although the execution results of the above sql have been sorted according to the empno column, in fact, oracle does not perform any sorting operation here, so walking index full scan can not only achieve the sorting results but also avoid the real sorting of the index key columns of the target index.

By default, the order of the scan results of the index full scan determines that the index full scan cannot be performed in parallel, and usually the index full scan uses single block reads.

In general, index full scan does not need to return to the table, so index full scan is suitable for cases where the query columns of the target sql are all index key value columns of the target index. We know that for the B-tree index in the oracle database, the index is not entered when all index key value columns are null (that is, when all index key value columns are NULL, these NULL values do not exist in the B-tree index), which means that the target index has only one index key column whose attribute value is NOT NULL when it is a prerequisite for full index scanning in oracle. It is obvious that if all the index key column properties of the target index are allowed null values, then if you still take the index full scan, you will miss the records in the target table whose index key value columns are all null, that is, the result of the index full scan will not be allowed at this time, and oracle will obviously not allow this to happen.

(4) Fast full scan of index

Index Fast full scan (INDEX FAST FULL SCAN) is very similar to index full scan, which is suitable for all types of B-tree indexes (including unique and non-unique indexes). Like index full scan, index fast full scan also needs to scan all index rows of all leaf blocks of the target index.

There are three differences between index fast full scan and index full scan:

a. Index fast full scan is only available for CBO

b. Index fast full scan can use multi-block reads or can be performed in parallel.

c. The results of the fast full scan of the index are not necessarily orderly, because when the index is fast and full scanned, oralce scans according to the physical storage of the index rows on disk smoothly, rather than according to the logical order of the index rows, so the scanning results are not necessarily orderly (for the index of a single index leaf block, the physical storage order is the same as the logical storage order. However, for index leaf blocks adjacent to physical storage locations, the physical storage order of index rows between blocks is not necessarily logically ordered).

Experimental verification:

SQL > create table emp_test (empno number,col1 char (2000), col2 char (2000), col3 char (2000)

Table created.

SQL > alter table emp_test add constraint pk_emp_test primary key (empno,col1,col2,col3)

Table altered.

SQL > insert into emp_test select empno,ename,job,'A' from emp

14 rows created.

SQL > insert into emp_test select empno,ename,job,'B' from emp

14 rows created.

SQL > insert into emp_test select empno,ename,job,'C' from emp

14 rows created.

SQL > insert into emp_test select empno,ename,job,'D' from emp

14 rows created.

SQL > insert into emp_test select empno,ename,job,'E' from emp

14 rows created.

SQL > insert into emp_test select empno,ename,job,'F' from emp

14 rows created.

SQL > insert into emp_test select empno,ename,job,'G' from emp

14 rows created.

SQL > select count (*) from emp_test

COUNT (*)

-

ninety-eight

SQL > exec dbms_stats.gather_table_stats (ownname= > 'SCOTT',tabname= >' EMP_TEST',estimate_percent= > 100 camera cascade> true,method_opt= > 'for all columns size 1')

PL/SQL procedure successfully completed.

The purpose of / * + index_ffs (pk_emp_test) * / is to allow oracle to quickly scan the index of the primary key index pk_emp_test.

SQL > select / * + index_ffs (pk_emp_test) * / empno from emp_test

EMPNO

-

7521

7566

7369

7499

7782

7788

7844

7876

7900

7839

7654

98 rows selected.

Execution Plan

Plan hash value: 3550420785

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

| | 0 | SELECT STATEMENT | | 98 | 392 | 28 (0) | 00:00:01 |

| | 1 | INDEX FAST FULL SCAN | PK_EMP_TEST | 98 | 392 | 28 (0) | 00:00:01 |

Statistics

0 recursive calls

0 db block gets

250 consistent gets

0 physical reads

0 redo size

2224 bytes sent via SQL*Net to client

590 bytes received via SQL*Net from client

8 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

98 rows processed

The above experiments show that the execution results of fast full scan of the index are not necessarily orderly.

(5) Index skip scan

Index skip scan (index skip scan) is used for all types of composite B-tree indexes (including unique and non-unique indexes). It makes the target sql that does not specify query conditions on the leading column of the target index in the where condition but also specifies query conditions on the non-leading column of the index can still use the index, as if scanning the index skipped its leading column Just like scanning directly from the non-leading column of the index, this is also the meaning of the word "jump" in index skip scanning.

Why the query condition is not specified for the leading column of the target index in the where condition, but oracle can still use the index? this is because oralce helps you traverse all the distinct values of the leading column of the index.

Experimental verification:

SQL > create table employee (gender varchar2 (1), employee_id number)

Table created.

SQL > alter table employee modify (employee_id not null)

Table altered.

SQL > create index idx_employee on employee (gender,employee_id)

Index created.

SQL > begin

2 for i in 1..5000 loop

3 insert into employee values ('Fairhead I)

4 end loop

5 commit

6 end

7 /

PL/SQL procedure successfully completed.

SQL > begin

2 for i in 5001..10000 loop

3 insert into employee values ('Fairhead I)

4 end loop

5 commit

6 end

7 /

PL/SQL procedure successfully completed.

SQL > set autot off

SQL > exec dbms_stats.gather_table_stats (ownname= > 'SCOTT',tabname= >' employee',estimate_percent= > 100 camera cascade> true,method_opt= > 'for all columns size 1')

PL/SQL procedure successfully completed.

SQL > set autot on

SQL > select * from employee where employee_id=100

G EMPLOYEE_ID

--

F 100

Execution Plan

Plan hash value: 461756150

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

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

| | * 1 | INDEX SKIP SCAN | IDX_EMPLOYEE | 1 | 6 | 2 (0) | 00:00:01 |

Predicate Information (identified by operation id):

1-access ("EMPLOYEE_ID" = 100)

Filter ("EMPLOYEE_ID" = 100)

Statistics

0 recursive calls

0 db block gets

24 consistent gets

0 physical reads

0 redo size

599 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

As can be seen from the above display, oracle has used the index idx_employee when executing sql, and its execution plan is to skip the index scan of the index.

The above index can still be used here without specifying a leading column, because oracle helps us traverse the index distinct value of the leading column of the index.

The so-called traversal of the index distinct value of the target index is actually equivalent to an equivalent rewrite of the original sql (adding the distinct values of all leading columns of the target index to be used). The difference value of the leading column gender of the index inde_employee has only "F" and "M" values, so the reason for using ind_employee here can be simply understood as the following sql:

Select * from employee where gender='F' and employee_id=100

Union all

Select * from employee where gender='M' and employee_id=100

From the above analysis process, we can see that the jump index scan in Oracle is only suitable for those cases where the number of distinct values of the leading column of the target index is small, and the selectivity of the subsequent non-leading column is very good, because the execution efficiency of the index jump scan must increase with the increase of the number of distinct values of the leading column of the target index.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.