title: Why oracle CHOOSE INDEX RANGE SCAN over FAST FULL INDEX SCAN
body: <p>I have read some documentation about indexes, I did some examples and now I have some doubts.</p>
  
  <p>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)</p>
  
  <pre><code>SELECT COUNT(*) FROM DEMO_FULL_INDEX_SCAN;
  =1000
  </code></pre>
  
  
  
  <pre><code>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
  ...
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>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      |       |       |       |
  --------------------------------------------------------------------
  </code></pre>
  
  <p>I have to specify a hint to optimizer choose INDEX FAST FULL SCAN (but i dont know why i have to specify it)</p>
  
  <pre><code>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 |
  --------------------------------------------------------------------
  </code></pre>
  
  <p>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</p>
  
  <pre><code>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                |       |       |
  --------------------------------------------------------------------------------
  </code></pre>
  
  <p>My question is ,in the first example why Oracle CHOOSE INDEX RANGE SCAN over FAST FULL INDEX SCAN.</p>