Why isn't index used for this query?

Question

I had a query where an index was not used when I thought it could be, so I reproduced it out of curiosity:

Create a test_table with 1.000.000 rows (10 distinct values in col, 500 bytes of data in some_data).

CREATE TABLE test_table AS (
  SELECT MOD(ROWNUM,10) col, LPAD('x', 500, 'x') some_data
  FROM dual
  CONNECT BY ROWNUM <= 1000000
);

Create an index and gather table stats:

CREATE INDEX test_index ON test_table ( col );

EXEC dbms_stats.gather_table_stats( 'MY_SCHEMA', 'TEST_TABLE' );

Try to get distinct values of col and the COUNT:

EXPLAIN PLAN FOR
  SELECT col, COUNT(*)
  FROM test_table
  GROUP BY col;

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |    10 |    30 | 15816   (1)| 00:03:10 
|   1 |  HASH GROUP BY     |            |    10 |    30 | 15816   (1)| 00:03:10 
|   2 |   TABLE ACCESS FULL| TEST_TABLE |   994K|  2914K| 15755   (1)| 00:03:10 
--------------------------------------------------------------------------------- 

The index is not used, providing the hint does not change this.

I guess, the index can't be used in this case, but why?

Answer 1

UPDATE: Try making the col column NOT NULL. That is the reason it's not using the index. When it's not null, here's the plan.

SELECT STATEMENT, GOAL = ALL_ROWS           69  10  30
                    HASH GROUP BY           69  10  30
 INDEX FAST FULL SCAN   SANDBOX TEST_INDEX  56  98072   294216

If the optimizer determines that it's more efficient NOT to use the index (maybe due to rewriting the query), then it won't. Optimizer hints are just that, namely, hints to tell Oracle an index you'd like it to use. You can think of them as suggestions. But if the optimizer determines that it's better not to use the index (again, as result of query rewrite for example), then it's not going to.

Refer to this link: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm "Specifying one of these hints causes the optimizer to choose the specified access path only if the access path is available based on the existence of an index or cluster and on the syntactic constructs of the SQL statement. If a hint specifies an unavailable access path, then the optimizer ignores it."

Since you are running a count(*) operation, the optimizer has determined that it's more efficient to just scan the whole table and hash instead of using your index.

Here's another handy link on hints: http://www.dba-oracle.com/t_hint_ignored.htm