I have a simple query it's taking to long to execute:
select Floor(a11.BUILDMONTH/100) BUILD_YEAR,
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?
Click Here to Expand Forum to Full Width