Function based index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Function based index

  1. #1
    Join Date
    Apr 2007
    Location
    USA
    Posts
    110

    Function based index

    all,
    the exmaples I have seen for FBIs are mostly of type

    CREATE INDEX area_index ON rivers (area(geo));

    but what if I have an expression like below?
    mine is failing..
    SQL> create index fb_idx_test on MONAGA (SMCID(<>0));
    create index fb_idx_test on MANAGA (SMCID(<>0))

    ERROR at line 1:
    ORA-00936: missing expression

    this is for a query that ends thus
    where SMCID <> 0; => need to add the FBI here.
    thanks
    Looking for the greatest evil in the world? Look in the mirror.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    The single most important prerequisite to create a Function Based Index is to have a function.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Apr 2007
    Location
    USA
    Posts
    110
    If you had an index on SMCID and you did where SMCID <> 0; that index would not be used. So I thought a FBI could help...
    Looking for the greatest evil in the world? Look in the mirror.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by Tuma View Post
    I thought a FBI could help...
    A function is a piece of code that returns a value.
    "SMCID <> 0" is a comparison between a column name and a literal.
    As implied in its name, a function based index requires a function to be used.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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