Why oracle CHOOSE INDEX RANGE SCAN over FAST FULL INDEX SCAN

Question

I have read some documentation about indexes, I did some examples and now I have some doubts.

I create a table and insert random values, (A column has unique values) column A NOT NULL I create an index on A, B, C. (B-TREE)

SELECT COUNT(*) FROM DEMO_FULL_INDEX_SCAN;
=1000

SELECT * FROM DEMO_FULL_INDEX_SCAN;

         A          B          C          D          E          F
---------- ---------- ---------- ---------- ---------- ----------
         1          7        109          1          1          1
         2         12         83          2          2          2
         3         21        120          3          3          3
         4         13         74          4          4          4
         5          2          1          5          5          5
...

Documentation says when all query values are in the index, the values are gathered from index (INDEX FAST FULL SCAN), but here optimizer is choosing another operation.

EXPLAIN PLAN FOR
SELECT A,B,C FROM DEMO_FULL_INDEX_SCAN WHERE A = 1;
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  INDEX RANGE SCAN    | FIS_01      |       |       |       |
--------------------------------------------------------------------

I have to specify a hint to optimizer choose INDEX FAST FULL SCAN (but i dont know why i have to specify it)

EXPLAIN PLAN FOR
SELECT /*+ INDEX_FFS(DEMO_FULL_INDEX_SCAN FIS_01) */A,B,C FROM DEMO_FULL_INDEX_SCAN WHERE A = 1;
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |    11 |     2 |
|*  1 |  INDEX FAST FULL SCAN| FIS_01      |     1 |    11 |     2 |
--------------------------------------------------------------------

By the other hand ,this examples shows what oracle documentation says. When there is a value in the query that is not in the index, this value is accessed by TABLE ACCESS BY INDEX ROWID

EXPLAIN PLAN FOR
SELECT D FROM DEMO_FULL_INDEX_SCAN WHERE A = 800;

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id  | Operation                   |  Name                 | Rows  | Bytes | Co
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                       |       |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEMO_FULL_INDEX_SCAN  |       |       |
|*  2 |   INDEX RANGE SCAN          | FIS_01                |       |       |
--------------------------------------------------------------------------------

My question is ,in the first example why Oracle CHOOSE INDEX RANGE SCAN over FAST FULL INDEX SCAN.

Answer 1

You're performing an INDEX RANGE SCAN because of the WHERE clause of your SQL statement:

select a,b,c from demo_full_index_scan where a = 1;

I'm assuming here that you don't have a unique index on A despite the uniqueness of the column, i.e. your table DDL is something like this:

create table demo_full_index_scan ( 
   a number
 , b number
 , c number
 , d number
   );

create index i_demo_full_index_scan on demo_full_index_scan (a, b, c);

As you don't have a UNIQUE index Oracle can't know with certainty that the values in A will always be unique; however, Oracle does know that A is the first column in the index and can find this value in the range of values available in the index.

If your WHERE clause were to attempt to filter based on the column C you would perform an INDEX FULL SCAN as C exists in the index, so you don't need to access the table, but it is not the first column in the index:

explain plan for select a,b,c from demo_full_index_scan where c = 1;
-------------------------------------------------------------------------------------------
| Id  | Operation        | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                        |     1 |    39 |     1   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | I_DEMO_FULL_INDEX_SCAN |     1 |    39 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------