What is a functional index?

A functional index can be a B-tree index, an R-tree index, or a user-defined index type that a DataBlade® module provides.

When you create a functional index, the database server computes the values of the user-defined function and stores them as key values in the index. When a change in the table data causes a change in one of the values of an index key, the database server automatically updates the functional index.

You can use a functional index for functions that return values of both user-defined data types (opaque and distinct) and built-in data types. However, you cannot define a functional index if the function returns a simple-large-object data type (TEXT or BYTE).

For more information about the types of indexes, see Defining indexes for user-defined data types. For information about space requirements for functional indexes, see Estimating index pages.