function_based index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: function_based index

Hybrid View

  1. #1
    Join Date
    Jun 2000
    Posts
    315

    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!

  2. #2
    Join Date
    Mar 2002
    Posts
    534
    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...

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  4. #4
    Join Date
    Jun 2000
    Posts
    315
    If I take off the Floor function as you said, the result would be different.

  5. #5
    Join Date
    Mar 2002
    Posts
    534
    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
  •  



Click Here to Expand Forum to Full Width