Oracle execution plan explanation-Index

Oracle execution plan explanation-Index according to the index type and where restrictions, there are 5 types of Index scans: unique index scan) www.2cto.com index range scan index full scan index fast full scan index skip scan (index skip scan) (1) the unique index scan (index unique scan) uses the unique index to find a value and often returns a single ROWID. if the UNIQUE or primary key constraint exists (it ensures that the statement only accesses a single row), Oracle often implements a UNIQUE scan. Examples of using uniqueness constraints: SQL> explain plan for select empno, ename from emp where empno = 10; query Plan ---------------------------------- select statement [CHOOSE] Cost = 1 table access by rowid emp [ANALYZED] index unique scan EMP_I1 (2) index scan range (index scan range) A single index is used to access multiple rows of data. A typical use of index range scanning on a unique index is a predicate (where condition) the range operator (such as >,<, <>, >=, <=, and between) is used for index range scanning. Example: SQL> explain plan for select empno, ename from emp where e Mpno> 7876 order by empno; Query Plan distinct select statement [CHOOSE] Cost = 1 table access by rowid emp [ANALYZED] index range scan EMP_I1 [ANALYZED] on a non-unique INDEX, the predicate col = 5 may return multiple rows of data, so index range scanning is used for non-unique indexes. Three cases of using index rang scan: (a) using the range operator (><>=<= between) (B) on the unique index, only some columns are used for query. As a result, multiple rows (c) are queried for any queries on non-unique index columns. Www.2cto.com (3) full index scan corresponds to full table scan, and full index scan also exists. In addition, the data queried must be directly obtained from the index. Full Index scan example: An Index full scan will not perform single block I/o's and so it may prove to be inefficient. e.g. index BE_IX is a concatenated index on big_emp (empno, ename) SQL> explain plan for select empno, ename from big_emp order by empno, ename; query Plan -------------------------------------------------------------------------------- select statement [CHOOSE] Cost = 26 index full scan BE_IX [ANAL YZED] (4) index fast full scan scans all data blocks in the index, similar to index full scan, however, a significant difference is that it does not sort the queried data, that is, the data is not returned in the sorting order. In this access method, you can use the multi-block READ function or parallel read to obtain the maximum throughput and shorten the execution time. Example of fast index scanning: www.2cto.com BE_IX index is a multi-column index: big_emp (empno, ename) SQL> explain plan for select empno, ename from big_emp; query Plan ---------------------------------------- select statement [CHOOSE] Cost = 1 index fast full scan BE_IX [ANALYZED] only select the 2nd columns of Multi-column indexes: SQL> explain plan for SELECT ename from big_emp; query Plan ---------------------------------------- select statement [CHOOSE] Cost = 1 INDEX FAST FU Ll scan BE_IX [ANALYZED] (5) index skip scan (index skip SCAN) when a query can obtain results by combining indexes, and few results are returned, when the where condition does not contain an index Bootstrap column, the index skip scan may be executed: 1. it must be a composite index. 2. The bootstrap column does not appear in the where condition.