There are 5 methods of index lookup

index unique scan
index range scan
index full scan
index fast full scan
index skip scan

Index unique scan
~~~~~~~~~~~~~~~~~
Method for looking up a single key value via a unique index.
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.

[@more@]

example explain plan:

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

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.
Can be used for range operations (e.g. > < <> >= <= between)
e.g.

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]

A non-unique index may return multiple values for the predicate
col1 = 5 and will use an index range scan

SQL> explain plan for select mgr from emp where mgr = 5

Query plan
--------------------
SELECT STATEMENT [CHOOSE] Cost=1
INDEX RANGE SCAN EMP_I2 [ANALYZED]

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 only available in the CBO as otherwise we are
unable to determine whether a full scan would be a good idea or not.
We choose an index Full Scan when we have statistics that indicate that it is
going to be more efficient than a Full table scan and a sort.

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.

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]

Index Fast Full Scan
~~~~~~~~~~~~~~~~~~~~

Scans all the block in the index
Rows are not returned in sorted order
Introduced in 7.3 and requires V733_PLANS_ENABLED=TRUE and CBO
may be hinted using INDEX_FFS hint
uses multiblock i/o
can be executed in parallel
can be used to access second column of concatenated indexes. This is because
we are selecting all of the index.

Note that INDEX FAST FULL SCAN is the mechinism behind fast index create
and recreate.

e.g.
Index BE_IX is a concatenated index on 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]

Selecting the 2nd column of concatenated index:

SQL> explain plan for select ename from big_emp;

Query Plan
------------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
INDEX FAST FULL SCAN BE_IX [ANALYZED]

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.

create index i_emp on emp(empno, ename);
select /*+ index_ss(emp i_emp)*/ job from emp where ename='SMITH';

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 61 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 12 | 61 |
|* 2 | INDEX SKIP SCAN | I_EMP | 1 | | 11 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMP"."ENAME"='SMITH')
filter("EMP"."ENAME"='SMITH')