indexing - why oracle CHOOSE INDEX RANGE SCAN over FAST FULL INDEX SCAN -

I have read some documents about the index, I have given some examples and now I have some doubts.

I create a table and insert random values ​​(there are unique values ​​in a column) column A no n I create an index on A, B, C (B-tree)

  SELECT COUNT (*) to DEMO_FULL_INDEX_SCAN; = 1000   
  SELECT * FROM DEMO_FULL_INDEX_SCAN; ABCDEF ---------- ---------- ---------- ---------- --------- - ---------- 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 ...   
 The documentation says that when all the query values ​​are in the index, values ​​are gathered from the index (index fast full scan), but here the optimizer is choosing another operation.  
  Explain plan SELECT A, B, C FROM DEMO_FULL_INDEX_SCAN WHERE A = 1; -------------------------------------------------- ------------------ | ID | Operation | Name | Rows | Byte | Cost | -------------------------------------------------- ------------------ | 0 | Select Command | | | | | | * 1 | Index Range Scan | FIS_01 | | | | -------------------------------------------------- ------------------   
 I have to specify an indicator for optimizer to choose the optimized fast full scan (but I do not know Why to specify it)  
  Select / * + INDEX_FFS (DEMO_FULL_INDEX_SCAN FIS_01) Expansion Plan * / A, B, C to Demo_Full_index_scana where A = 1; -------------------------------------------------- ------------------ | ID | Operation | Name | Rows | Byte | Cost | -------------------------------------------------- ------------------ | 0 | Select Command | | 1 | 11 | 2 | | * 1 | Index Fast Full Scan | FIS_01 | 1 | 11 | 2 | -------------------------------------------------- ------------------   
 On the other hand, this example illustrates what document documents are. When the value is in the index, then the value is ROWID  
  selected from D to DOM_FUL_IDX_SCANA, AA = 800 for the Apple Plan, through table access; -------------------------------------------------- ------------------------------------------------ -------------------------------------------------- ---------- | ID | Operation | Name | Rows | Byte | With ------------------------------------------------- ------------------------------- | 0 | Select Command | | | | | 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 Choosing Index Range Scan on Fast Foreign Index Scan.  
  
 
 
 You are displaying an index range scan WHERE clause of your SQL statement:  
  from demo_full_index_scan, select a, b, c, where a = 1;   
 I assume that you do not have a unique index on A, despite the specificity of the column, that is, your table DDL is something like this:  
  Table Create 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 do not have a unique index, Oracle can not know with certainty that the value in A will always be unique; However, Oracle knows that A is the first column in the index and this value can be found in the range of values ​​available in the index.  
 If your WHERE clause was to try filtering on the column C, you would perform an index full scan because C is present in the index, so you do not have to reach the table, but it is in the index The first column is not:  
  Explain the plan for selection A, B, C to demo_full_index_scan where c = 1; -------------------------------------------------- ----------------------------------------- | ID | Operation | Name | Rows | Byte | Cost (% CPU) | Time | -------------------------------------------------- ----------------------------------------- | 0 | Select Command | | 1 | 39 | 1 (0) | 00:00:01 | | * 1 | Index Full Scan | I_DEMO_FULL_INDEX_SCAN | 1 | 39 | 1 (0) | 00:00:01 | -------------------------------------------------- -----------------------------------------   < / Div>