Oracle Full Hint

If i understand the documentation correctly; the full hint should force a full table scan. In the below scenario it is not performing the same;

Num as in index created on it.

SQL> desc test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NUM                                       NOT NULL NUMBER
 NUM2                                               NUMBER(10)
 NUM3                                               NUMBER

Query:

select num from test;

Result:

       NUM
----------
         1
         2

Execution Plan

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 |
-------------------------------------------------------------------------

Query:

select /* +full(test) */ num from test;

Result:

       NUM
----------
         1
         2

Execution Plan

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 |
-------------------------------------------------------------------------

I understand i am selecting a value which is stored in the index. Add开发者_运维问答ition 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 ?

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?

I haven't tested this, but the correct syntax for using a hint is:

select /*+ full(test) */ num from test;

It's slash-star-plus-space.

"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?"

Probably merits a question in its own right (it's certainly more pertinent than your main question :-D ).

Oracle does not automatically maintain 100% statistics for all our objects. Prior to 10g we had to explicitly schedule background jobs to do this, using DBMS_STATS.GATHER_%_STATISTICS.

Since 11g Oracle has changed the default behaviour. It monitors DML issued against a schema and runs a job to gather statistics for objects when the existing statistics become stale. Even then it only computes stats for a percentage of rows. This is usually Good Enough, especially for large tables when it would be expensive to examine all the rows.

This default behaviour itself is usually Good Enough. If you have problems with bad query plans it might be worthwhile gather fresh statistics, perhaps against a larger sample of rows. but don't feel compelled to routinely gather full stats against your whole schema, as many places still do. Most of the time you're probably just wasting CPU cycles, and you run the risk of destabilising some of you existing plans.

Database statistics is a big topic. The Performance Tuning Guide has a whole chapter on it. Find out more. Also, read the PL/SQL Packages guide for more on DBMS_STATS itself.