function_based index

I have a simple query it's taking to long to execute:

select Floor(a11.BUILDMONTH/100) BUILD_YEAR,
min(a11.BUILDMONTH) WJXBFS1
from QIS2OWNER.V_QIS_ENGINE_AGGTS_BUILDMONTH all
where Floor(a11.BUILDMONTH/100) = 2004
group by Floor(a11.BUILDMONTH/100)


1. According to oracle documentation, indexes cannot be used on columns modified by functions.
But, from the explain plan, there is a bitmap index fast full scan on index on column BUILDMONTH.
Can anybody explain this to me?

2. Even with the bitmap index fast full scan, the execution is slow.
So I created a function_based index on Floor(buildmonth/100) on the base table of v_qis_engine_aggts_buildmonth. Then, I checked explain plan for the query, it didn't use the function_based index.
Can function_based index work on this kind of function? Or just transformed columns and expressions?

Thanks!

an index access is often only faster if you access less then 5% or even 1% of the data. In your case I guess that more then 5% of your data are from 2004...

How about simply changing WHERE condition so that it doesn't use functions?

Replace this:

Code:

---

where Floor(a11.BUILDMONTH/100) = 2004

---

with this:

Code:

---

where a11.BUILDMONTH between 200400 and 200499

---

If I take off the Floor function as you said, the result would be different.

and what would be the result?

at the same time you could try:

select 2004 BUILD_YEAR,
min(a11.BUILDMONTH) WJXBFS1
from QIS2OWNER.V_QIS_ENGINE_AGGTS_BUILDMONTH
where a11.BUILDMONTH between 200400 and 200499