title: Oracle Full Hint
body: <p>If i understand the <a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/hintsref.htm#5078" rel="nofollow">documentation correctly</a>; the full hint should force a full table scan. In the below scenario it is not performing the same;</p>
  
  <p>Num as in index created on it.</p>
  
  <pre><code>SQL&gt; desc test;
   Name                                      Null?    Type
   ----------------------------------------- -------- ----------------------------
   NUM                                       NOT NULL NUMBER
   NUM2                                               NUMBER(10)
   NUM3                                               NUMBER
  </code></pre>
  
  <h3>Query:</h3>
  
  <pre><code>select num from test;
  </code></pre>
  
  <h3>Result:</h3>
  
  <pre><code>       NUM
  ----------
           1
           2
  </code></pre>
  
  <h3>Execution Plan</h3>
  
  <pre><code>Plan hash value: 410557223
  
  -------------------------------------------------------------------------
  | Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  -------------------------------------------------------------------------
  |   0 | SELECT STATEMENT |      |     2 |     4 |     1   (0)| 00:00:01 |
  |   1 |  INDEX FULL SCAN | ID   |     2 |     4 |     1   (0)| 00:00:01 |
  -------------------------------------------------------------------------
  </code></pre>
  
  <h3>Query:</h3>
  
  <pre><code>select /* +full(test) */ num from test;
  </code></pre>
  
  <h3>Result:</h3>
  
  <pre><code>       NUM
  ----------
           1
           2
  </code></pre>
  
  <h3>Execution Plan</h3>
  
  <pre><code>Plan hash value: 410557223
  
  -------------------------------------------------------------------------
  | Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  -------------------------------------------------------------------------
  |   0 | SELECT STATEMENT |      |     2 |     4 |     1   (0)| 00:00:01 |
  |   1 |  INDEX FULL SCAN | ID   |     2 |     4 |     1   (0)| 00:00:01 |
  -------------------------------------------------------------------------
  </code></pre>
  
  <p>I understand i am selecting a value which is stored in the index. Addition of any other column makes the scan as full. Hence i have to ask the obvious. Is hint a request or is it a command to the optimizer ?</p>
  
  <p>On a side note , what does computation of statistics have to do with optimization. Are the statistics of an index updated automatically or is this an explicit operation?</p>