Analysis of four categories of Oracle index Scanning

When learning Oracle, you may encounter Oracle index scanning problems. Here we will introduce the solutions to Oracle index scanning problems. Here we will share with you. There are four types of Oracle index scans, depending on the index type and where restriction conditions:
◆ Unique index scan)
◆ Index range scan)
◆ Full index scan)
◆ Index fast full scan)

(1) unique index scan)

A single ROWID is often returned for finding a value through a unique index. If the unique Index consists of multiple columns (composite index), at least the bootstrap column of the composite index must be included in the query. For example, create an index: create index idx_test on emp (ename, deptno, loc ). The select ename from emp where ename = 'jack' and deptno = 'dev' statement can use this index. If this statement returns only one row, the access method is called unique index scan. The select ename from emp where deptno = 'dev' statement does not use this index, because the where clause does not have a bootstrap column. 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 range scan)

Use one index to access multiple rows of data. The same as above, if the index is a combination index, as shown in (1, in addition, the select ename from emp where ename = 'jack' and deptno = 'dev' statement returns multiple rows of data, although the statement still uses the combined index for query, the access method is called index range scan. A typical use of index range scanning on a unique index is a predicate (where restriction) range operators (such as >,<, >,>=, <=, and between) are used)

Example of using index range scan:

 
 

  
  
SQL> explain plan for select empno,ename from emp  
  
  
where empno > 7876 order by empno;  
  
  
Query Plan  
  
  
SELECT STATEMENT [CHOOSE] Cost=1 
  
  
TABLE ACCESS BY ROWID EMP [ANALYZED]  
  
  
INDEX RANGE SCAN EMP_I1 [ANALYZED] 
 
 

In 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) Use the range operator on the unique index column (><>>=<=)
(B) on the composite index, only some columns are used for query, resulting in multiple rows being queried.
(C) any queries on non-unique index columns.

(3) full index scan)

The full Oracle index scan corresponds to the full table scan. In some cases, full Oracle index scanning may be performed instead of range scanning. Note that full Oracle index scanning is only effective in CBO mode. The CBO performs a full Oracle index scan only when it finds that the full Oracle index scan is more effective than the full table scan. In this case, the queried data must be directly obtained from the index.

Example of a full Oracle index scan:

 
 

  
  
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 [ANALYZED] 
 
 

(4) index fast full scan)

Scanning all data blocks in an index is similar to full scan, but it does not sort the queried data, that is, the data is not returned in the order of sorting. 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:

 
 

  
  
SQL> explain plan for select empno,ename from big_emp;  
  
  
Query Plan  
  
  
SELECT STATEMENT [CHOOSE] Cost=1 
  
  
INDEX FAST FULL SCAN BE_IX [ANALYZED]  
  
  
SQL> explain plan for select ename from big_emp;  
  
  
Query Plan  
  
  
SELECT STATEMENT [CHOOSE] Cost=1 
  
  
INDEX FAST FULL SCAN BE_IX [ANALYZED] 
 
 

1. Oracle management tool-Overview of Oracle SQL Handler Functions
2. Master talk about Oracle index Scanning
3. Six-minute Oracle full table Scan
4. Code Description Oracle Selectivity
5. About the sequence of Oracle execution plans