Index lookup
Data is accessed by looking up key values in an index and returning rowids. A rowid uniquely identifies an individual row in a particular data block. This block is read via single block i/o.
In this example an index is used to find the relevant row(s) and then the table is accessed to look up the ename column (which is not included in the index):
</pre>
SQL> select empno,ename from emp where empno=10;
Execution Plan
----------------------------------------------------------
Plan hash value: 4066871323
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 10 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=10)
<pre>
Notice the ‘TABLE ACCESS BY ROWID‘ section. This indicates that the table data is not being accessed via a FTS operation but rather by a rowid lookup. In this case the rowid has been produced by looking up values in the index first. The index name in this case is PK_EMP and the index is being accessed by an ‘INDEX UNIQUE SCAN’ operation (explained below). If all the required data resides in the index then a table lookup may be unnecessary and all you will see is an index access with no table access. In the following example all the columns (empno) are in the index. Notice that no table access takes place:
</pre>
SQL> select empno from emp where empno=10;
Execution Plan
----------------------------------------------------------
Plan hash value: 4008335093
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 0 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| PK_EMP | 1 | 4 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMPNO"=10)
<pre>
Indexes are pre-sorted, so sorting may be unnecessary if the sort order required is the same as the index. For example:
</pre>
select empno,ename from emp where empno > 7876 order by empno;
Execution Plan
----------------------------------------------------------
Plan hash value: 2449469783
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 10 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO">7876)
<pre>
Because the data in the index is already stored in sorted order, the rows are returned in sorted order of the index hence a sort is unnecessary assuming that the columns in the index are the same as in the order by. If we force an access path that does not use the index (e.g. FTS) then we will have to sort the data. For example:
</pre>
select /*+ Full(emp) */ empno,ename from emp where empno > 7876 order by empno;
Execution Plan
----------------------------------------------------------
Plan hash value: 4060621227
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 3 (0)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 10 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 10 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO">7876)
<pre>
Because we have forced a FTS the data is unsorted and so we must sort the data after it has been retrieved, hence the SORT ORDER BY step in the plan. The following methods of index lookup are available:
Index unique scan This is a method for looking up a single key value via a unique index and it always returns a single value. You must supply AT LEAST the leading column of the index to access data via the index (however this may return > 1 row as the uniqueness will not be guaranteed). For example:
</pre>
SQL> select empno,ename from emp where empno=10;
Execution Plan
----------------------------------------------------------
Plan hash value: 4066871323
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 10 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=10)
<pre>
Index range scan Index range scan is a method for accessing a range values of a particular column. AT LEAST the leading column of the index must be supplied to access data via the index. It can be used for range operations (e.g. > < >= <= between ) or where the data to be returned is not unique. For example:
</pre>
SQL> select empno,ename from emp where empno > 7876 order by empno;
Execution Plan
----------------------------------------------------------
Plan hash value: 2449469783
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 10 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO">7876)
<pre>
A non-unique index may return multiple values for the predicate mgr = 5 and will use an index range scan. For example:
</pre>
SQL> create index emp_mgr on emp(mgr);
SQL> select mgr from emp where mgr = 5;
Execution Plan
----------------------------------------------------------
Plan hash value: 1542557660
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| EMP_MGR | 1 | 4 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("MGR"=5)
<pre>
Index Full Scan In certain circumstances it is possible for the whole index to be scanned as opposed to a range scan (i.e. where no constraining predicates are provided for a table). Full index scans are chosen when statistics indicate that it is going to be more efficient than a Full table scan and a sort. The index blocks are scanned one by one, not using multi-block I/O (like a FTS or Index FFS). For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order. The optimizer may decide that selecting all the information from the index and not sorting is more efficient than doing a FTS or a Fast Full Index Scan and then sorting. An Index full scan will perform single block i/o’s and so it may prove to be inefficient. In the following example, Index BE_IX is a concatenated index on emp (empno,ename). A select with no predicates results in an index full scan since it can satisfy the whole query without need to visit the table:
</pre> SQL> create index E_CIX on emp (empno,ename); Index created. SQL> select empno,ename from emp order by empno,ename; Execution Plan ---------------------------------------------------------- Plan hash value: 2418964722 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 140 | 1 (0)| 00:00:01 | | 1 | INDEX FULL SCAN | E_CIX | 14 | 140 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------- <pre>
Index Full Scan can also be used to access second column of concatenated indexes because the whole index is being retrieved as compared to a range scan which may not retrieve all the blocks.
</pre> SQL> select ename from emp; Execution Plan ---------------------------------------------------------- Plan hash value: 2418964722 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 84 | 1 (0)| 00:00:01 | | 1 | INDEX FULL SCAN | E_CIX | 14 | 84 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------- <pre>
Index Fast Full Scan (Index FFS) An Index Fast Full Scan (Index FFS) scans all the blocks in the index using multiblock I/O. This means that the rows are not necessarily returned in sorted order. Index FFS may be hinted using INDEX_FFS hint and can be executed in parallel. It can also be used to access second column of concatenated indexes because the whole index is being retrieved as compared to a range scan which may not retrieve all the blocks. Note that INDEX FAST FULL SCAN is the mechanism behind fast index create and recreate. We can use the E_CIX concatenated index to illustrate an Index FFS:
</pre> SQL> select /*+ Index_FFS(emp) */ empno,ename from emp; Execution Plan ---------------------------------------------------------- Plan hash value: 2100043038 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 14 | 140 | 2 (0)| 00:00:01 | | 1 | INDEX FAST FULL SCAN| E_CIX | 14 | 140 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------ <pre>
Index FFS can also be used to access second column of concatenated indexes because the whole index is being retrieved as compared to a range scan which may not retrieve all the blocks.
</pre> SQL> select /*+ Index_FFS(emp) */ ename from emp; Execution Plan ---------------------------------------------------------- Plan hash value: 2100043038 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 14 | 84 | 2 (0)| 00:00:01 | | 1 | INDEX FAST FULL SCAN| E_CIX | 14 | 84 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------ <pre>
Index skip scan Index skip scan finds rows even if the column is not the leading column of a concatenated index. It skips the first column(s) during the search. The next example checks ename=’SMITH’ for each index key even though ename is not the leading column of the index. The leading column (empno) is skipped.
</pre>
SQL> select /*+ index_ss(emp i_emp)*/ job from emp where ename='SMITH';
Execution Plan
----------------------------------------------------------
Plan hash value: 112616935
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 1 | 14 | 2 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | E_CIX | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ENAME"='SMITH')
filter("ENAME"='SMITH')
<pre>
Oracle Database Internal Mechanism 