Using the return value of a function as an index key

A functional index is indexed on the value that a specified function returns from a column-value argument, rather than on the value of a column.

For example, the following statement creates a functional index on table zones using the value that the user-defined function Area( ) returns as the key: 
CREATE INDEX zone_func_ind ON zones (Area(length,width));

You can create the function on which to define a functional index within an SPL routine. You can also create an index on a nonvariant user-defined function that does not return a large object.

The functional index can be a B-tree index, an R-tree index, or a user-defined secondary-access method.

The value returned by the function can be the index key, as in the example above, or it can be part of a composite index whose other key parts are the values of columns, the values of partial columns, or the return values of other functional indexes. (For more information, see the topic Creating Composite Indexes).

Important: The database server imposes the following restrictions on the user-defined routines (UDRs) on which a functional index is defined:
  • The arguments cannot be the names of column of a collection data type (LIST, MULTISET, or SET).
  • The function cannot return a large object of the data types BLOB, BYTE, CLOB, and TEXT.
  • The function cannot be a VARIANT function.
  • The function cannot include any DML statement of SQL.
  • The ONLINE keyword is not valid when you create a functional index; see The ONLINE keyword of CREATE INDEX.
  • The function must be a user-defined function. You cannot create a functional index on any built-in function of SQL.
  • The function must be a user-defined function, or, to create an index on a field in a BSON column, the BSON_GET function or a BSON value function. You cannot create a functional index on any other built-in function of SQL.

Despite the last restriction above, however, you can create a functional index on a user-defined function that calls a non-variant built-in SQL function, so that the value returned by the built-in function is the index key of a functional index. (That is, create an SPL wrapper that calls and returns the value from a built-in function of SQL, and then define a functional index on this user-defined SPL function.)