Tienes razón, tendrían que estar indexados tanto las columnas del WHERE como del SELECT, para omitir el acceso a la tabla, o no existir WHERE :P, como complemento al tema:
http://www.orafaq.com/tuningguide/hi...l%20index.html http://docs.oracle.com/cd/B28359_01/...s.htm#autoId31
11.5.3.6 Full Scans
A full index scan eliminates a sort operation, because the data is ordered by the index key. It reads the blocks singly. A full scan is used in any of the following situations:
An ORDER BY clause that meets the following requirements is present in the query:
All of the columns in the ORDER BY clause must be in the index.
The order of the columns in the ORDER BY clause must match the order of the leading index columns.
The ORDER BY clause can contain all of the columns in the index or a subset of the columns in the index.
The query requires a sort merge join. A full index scan can be done instead of doing a full table scan followed by a sort if the query meets the following requirements:
All of the columns referenced in the query must be in the index.
The order of the columns referenced in the query must match the order of the leading index columns.
The query can contain all of the columns in the index or a subset of the columns in the index.
A GROUP BY clause is present in the query, and the columns in the GROUP BY clause are present in the index. The columns do not need to be in the same order in the index and the GROUP BY clause. The GROUP BY clause can contain all of the columns in the index or a subset of the columns in the index.
11.5.3.7 Fast Full Index Scans
Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.
You can specify fast full index scans with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint. Fast full index scans cannot be performed against bitmap indexes.
A fast full scan is faster than a normal full index scan in that it can use multiblock I/O and can be parallelized just like a table scan.
Saludos!