A while back, I was asked to take a look at a situation where a COUNT(*) query was using full table scan instead of an Index Fast Full Scan. The scan of the index would've been faster and used less resources but even with a hint, the full scan was occurring. The answer turned out to be fairly simple. Can you guess what the problem was (bet you can given the title of this post)?

The table has about 480K rows and is about 800 Mb in size (not that big really, but that's a bit beside the point).

It has the following indexes:

                               Column                         Column
INDEX_NAME                     Name                           Position
------------------------------ ------------------------------ --------
I1478_3_1                      C3                             1
I1478_600000214_1              C600000214                     1
I1478_600000215_1              C600000215                     1
I1478_740002011_1              C740002011                     1
I1478_740002012_1              C740002012                     1
I1478_770000000_1              C770000000                     1
I1478_840000044_1              C840000044                     1
Itbl42                         C1                             1

When executing:
select C600000215, count(*)
from admn.tbl42
group by C600000215;

It uses the following plan:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=27954 Card=563 Bytes=11823)
1 0 HASH (GROUP BY) (Cost=27954 Card=563 Bytes=11823)
2 1 TABLE ACCESS (FULL) OF 'tbl42' (TABLE) (Cost=27922 Card=481061 Bytes=10102281)

Even using a hint, the optimizer still goes for a full scan.
select /*+ INDEX_FFS(tab1) */ C600000215, count(*)
from admn.tbl42 tab1
group by C600000215;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=27954 Card=563 Bytes=11823)
1 0 HASH (GROUP BY) (Cost=27954 Card=563 Bytes=11823)
2 1 TABLE ACCESS (FULL) OF 'tbl42' (TABLE) (Cost=27922 Card=481061 Bytes=10102281)

The index should obviously be a better choice:
select count(*) "Extents",
round(sum(bytes)/(1024*1024)) "MBytes"
from dba_extents
where owner='ADMN'
and segment_name='TBL42';

Extents    MBytes
---------- ----------
172        807

For the index:
Extents    MBytes
---------- ----------
32         17

What should be checked?

You should check to see if the indexed column allows nulls. If the column allows nulls, then the index can't be used to count the nulls since rows with null values are not stored for single column indexes.

A simple describe showed that the column C600000215 which was used as the index column was a VARCHAR2(100) and it did not have a NOT NULL constraint. So, the optimizer couldn't choose the IFFS.

Here's the fix that was implemented to allow the IFFS to be chosen:
alter table admn.tbl42
modify (C600000215 NOT NULL);

This added the NOT NULL constraint to the column. Now, when the query is executed, the optimizer chooses the IFFS.
select C600000215, count(*)
from admn.tbl42
group by C600000215
order by C600000215;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1781 Card=5340 Bytes=112140)
1 0 SORT (GROUP BY) (Cost=1781 Card=5340 Bytes=112140)
2 1 INDEX (FAST FULL SCAN) OF 'I1478_600000215_1' (INDEX) (Cost=605 Card=494913 Bytes=10393173)


Of course, if the column had to be allowed to have nulls then the solution would've required some more thought. But fortunately, this one worked fine.