4 categories of Oracle Index scans

When you are learning Oracle, you may experience an Oracle index scan problem, and here's how to solve the Oracle index scan problem, and share it here. There are 4 types of Oracle index scans, depending on the type of index and the WHERE constraint:

Indexed unique Scan (index unique scan)

Index range Scan (index range scan)

Index full scan (scan)

Index fast Scan (index fast full scan)

(1) Index unique Scan (indexed unique scan)

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

Examples of using uniqueness constraints:

1. sql> Explain plan for

2. Select Empno,ename from emp where empno=10;

3. Query Plan

4. SELECT STATEMENT [CHOOSE] Cost=1

5. TABLE ACCESS by ROWID EMP [analyzed]

6. INDEX UNIQUE SCAN EMP_I1

13:21:39 sql> set autotrace on;

13:21:44 sql> SELECT * from emp where empno=7788;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO

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

7788 SCOTT ANALYST 7566 19-apr-87 3000 20

Execution Plan

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

Plan Hash value:2949544139

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

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

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

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

|  1 | TABLE ACCESS by INDEX rowid|     EMP |    1 |     87 | 2 (0) | 00:00:01 |

|* 2 | INDEX UNIQUE SCAN |     Pk_emp |       1 |     | 1 (0) | 00:00:01 |

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

predicate information (identified by Operation ID):

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

2-access ("EMPNO" =7788)

Statistics

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

0 Recursive calls

0 db Block gets

2 consistent gets

0 physical Reads

0 Redo Size

732 Bytes sent via sql*net to client

374 bytes received via sql*net from client

1 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

1 rows processed

(2) Index range Scan (index range scan)

Use an index to access multiple rows of data, as 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 composite cable The access method at this time is called an index range scan. In typical cases where an index range scan is used on a unique index, the scope operators (such as >, <, <>, >=, <=, between) are used in predicates (where constraints).

Examples of using index range scans:

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]

The predicate col = 5 May return multiple rows of data on a non unique index, so an index range scan is used on a non unique index.

3 cases of using the index rang scan:

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

(b) On a composite index, queries are made using only a subset of the columns, causing multiple rows to be queried

(c) Any queries made on a non-unique indexed column.

13:21:59 sql> CREATE index emp_deptno_ind on EMP (DEPTNO);

Index created.

13:22:23 sql> SELECT * from emp where deptno=10;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO

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

7782 CLARK MANAGER 7839 09-jun-81 2450 10

7839 KING PRESIDENT 17-nov-81 5000 10

7934 MILLER Clerk 7782 23-jan-82 1300 10