Table must be analyzed after index is created to use function-based index

Q.  What should be done to use a function-based index?
- Published on 28 Jul 15

a. The table must be analyzed after the index is created
b. The query must be guaranteed need NULL values from the indexed expression, since NULL values are not stored in indexes
c. Both A & B
d. None of the above

ANSWER: The table must be analyzed after the index is created

Related Content

 

    Discussion

  • Nirja Shah   -Posted on 29 Sep 15
    - Function-based indexes facilitate queries that qualify a value returned by a function or expression.

    - The value of the function or expression is precomputed and stored in the index.

    - For the creation of a function-based index in your own schema, you must be granted the QUERY REWRITE system privileges.

    - To create the index in another schema or on another schema's tables, you must have the CREATE ANY INDEX and GLOBAL QUERY REWRITE privileges.

    - You must have the following initialization parameters defined to create a function-based index:

    1. QUERY_REWRITE_INTEGRITY set to TRUSTED
    2. QUERY_REWRITE_ENABLED set to TRUE
    3. COMPATIBLE set to 8.1.0.0.0 or a greater value

    - Additionally, to use a function-based index:

    1. The table must be analyzed after the index is created.

    2. The query must be guaranteed not to need any NULL values from the indexed expression, since NULL values are not stored in indexes.

➨ Post your comment / Share knowledge


Enter the code shown above:

(Note: If you cannot read the numbers in the above image, reload the page to generate a new one.)
▲