Tuesday, July 22, 2008

Optimizer won't choose Index Fast Full Scan?

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 answer turned out to be fairly simple. Can you determine the problem?


Table with about 480K rows and about 800 Mb in size.

It has the following indexes:

SQL> @show_table_indexes
Enter value for owner: aradmin70
Enter value for table: t1478

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
IT1478 C1 1

When executing:

select C600000215, count(*)
from aradmin70.T1478
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 'T1478' (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 aradmin70.T1478 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 'T1478' (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='ARADMIN70'
and segment_name='T1478';

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

For the index:

Extents MBytes
---------- ----------
32 17

What should be checked?




...and the answer is...

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 ARADMIN70.T1478
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 aradmin70.T1478
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.

2 comments:

Asif Momen said...

This is why Oracle documentation insists on applying "NOT NULL" constraint where ever applicable.

Pravin said...

Few queries I have.
Was this is 10g DB?
Because in 10G, optimizer chosses HASH join when ever you have Group By clause and ignore indexes. If adding of constraint is not possible then you can use optimizer_feature_enable=9.2.0 at session level.
Let me know your thoughts on this.
pravintakpire@hotmail.com
regards
Pravin