1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

function-based index

Discussion in 'Server Administration and Options' started by mirza, Dec 10, 2009.

  1. mirza

    mirza Active Member

    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    80
    How to create a function-based index on the expression?
    I am using 10.2.0.4.
    Please also tell that can We create the same through Enterprise manager?


    Thanks in Advance

    Rgds,
    Mirza
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    what kind of expression you mean ?

    This link may help you.

    Yes. you can create.
     
  3. mirza

    mirza Active Member

    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    80
    EM recommending :-

    Rewrite the predicate into an equivalent form to take advantage of indices. Alternatively, create a function-based index on the expression.

    The optimizer is unable to use an index if the predicate is an inequality condition or if there is an expression or an implicit data type conversion on the indexed column.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Which doesn't answer the question. What is this problem query? Which function are you using that causes the tuning advisor to recommend a function-based index? Creating a function-based index is easy enough:

    Code (SQL):
    CREATE INDEX my_fbi
    ON mytable(to_date(mydate, 'MM/DD/RRRR'))
    /
    You really need to provide more information than you do so others can assist you.