-
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!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|