If I understand the documentation correctly; A full hint should force a full table scan. In the scenario below, it does not do the same;
Num as in the 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 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 that I choose the value that is stored in the index. Adding any other column makes the scan complete. Therefore, I must ask the obvious. Does the query prompt or is this an optimizer command?
On the side of the note, the calculation of statistics is related to optimization. Are index statistics updated automatically or is this an explicit operation?
source
share