Analysis of four types of Oracle index scanning

The CBO shows that a full Oracle index scan is more efficient than a full-table scan, based on statistical values, and that the data queried at this point must be directly available from the index.

When you are learning Oracle, you may encounter Oracle index scanning issues, here is a solution to the Oracle index scanning problem, here to share with you. There are 4 types of Oracle index scans, depending on the type of the index and where restriction conditions:

Index unique scanning (index unique scan)

Index range scanning (index ranges scan)

Index full scan

Index fast Scan (index fast full scan)

(1) Index unique Scan (indexed unique scan)

Finding a numeric value from a unique index often returns a single rowid. If the unique index has more than one column (that is, a composite index), then at least a guided column with a combined index participates in the query, such as creating an index: Create index idx_test on EMP (ename, Deptno, loc). Then select ename from emp where ename = ' JACK ' and deptno = ' DEV ' statement can use this index. If the statement returns only one row, the access method is called an indexed unique scan. The select ename from emp where deptno = ' DEV ' statement does not use the index because the WHERE clause does not have a boot column. If there is a unique or primary KEY constraint (which guarantees that the statement only accesses a single line), Oracle often implements a unique scan.

Examples of using uniqueness constraints:

1. SQL > explain plan for
2. Select Empno,ename from emp where empno = Ten ;
3. Query Plan
4. SELECT STATEMENT [CHOOSE] Cost = 1
5. TABLE ACCESS by ROWID EMP [ANALYZED]
6. INDEX UNIQUE SCAN EMP_I1

(2) Index range scan

Use an index to access multiple rows of data, as in the above, if the index is a composite index, as shown in (1), and the select ename from emp where ename = ' JACK ' and deptno = ' DEV ' statements return multiple rows of data, although the statement still uses the combined cable The access method at this point is called an index range scan. Typical use of index range scanning on a unique index is the use of range operators (such as >, <, <>, >=, <=, between) in predicates (where constraints)

Examples of using index range scanning:

1. SQL > explain plan for select Empno,ename from EMP
2. where Empno > 7876 ORDER by empno;
3. Query Plan
4. SELECT STATEMENT [CHOOSE] Cost = 1
5. TABLE ACCESS by ROWID EMP [ANALYZED]
6. INDEX RANGE SCAN emp_i1 [ANALYZED]

On non-unique indexes, the predicate col = 5 May return multiple rows of data, so the index range scan is used on non-unique indexes.

3 scenarios using the index rang scan:

(a) The range operator is used on a unique index column (> < <> >= <= between)

(b) On a composite index, queries are made using only a subset of the columns, resulting in multiple rows being queried

(c) Any query that is made on a non-unique indexed column.

(3) Index full scan

Corresponding to the full-table scan, there is also a corresponding full Oracle Index scan. In some cases, a full Oracle index scan, rather than a range scan, is possible, and it is important to note that full Oracle index scans are only valid in CBO mode. The CBO makes a full Oracle index scan based on statistical values when full-Oracle index scans are more efficient than full-table scans, and the data queried at this point must be directly available from the index.

Examples of full Oracle index scans:

1. An Index full scan would not perform a single block I/O ' s and so it could prove to be inefficient.
2. e.g.
3. Index Be_ix is a concatenated index on big_emp (empno, ename)
4. SQL > explain plan for select Empno, ename from Big_emp order by Empno,ename;
5. Query Plan
6. SELECT STATEMENT [CHOOSE] Cost = -
7. INDEX full SCAN Be_ix [ANALYZED]

(4) Index fast Scan (index fast full scan)

Scanning all data blocks in an index is similar to the index full scan, but one notable difference is that it does not sort the queried data, that is, the data is not returned in a sort order. In this access method, you can use the multi-block read function, or you can use parallel read-in to achieve maximum throughput and reduce execution time.

Examples of fast index scans:

2. SQL > explain plan for select Empno,ename from Big_emp;
4. Query Plan
6. SELECT STATEMENT [CHOOSE] Cost = 1
8. INDEX FAST full SCAN be_ix [ANALYZED]
10. SQL > explain plan for select ename from Big_emp;
12. Query Plan
14. SELECT STATEMENT [CHOOSE] Cost = 1
16. INDEX FAST full SCAN be_ix [ANALYZED]