-
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
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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
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
|