Using a functional index

You can create a column index on the actual values in one or more columns. You can also create a functional index on the values of one or more columns that a user-defined function returns from arguments.

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 column values of a collection data type.
  • The function cannot return a large object (including built-in types BLOB, BYTE, CLOB, and TEXT).
  • The function cannot be a VARIANT function.
  • The function cannot include any DML statement of SQL.
  • The function must be a UDR, rather than a built-in function. However, you can create an SPL wrapper that calls and returns the value from a built-in function of SQL.

In addition, do not create functional indexes using any routine that calls the built-in DECRYPT_BINARY( ) or DECRYPT_CHAR( ) functions, which can display encrypted data values in plain text. (Do not attempt to use data values in any encrypted column as an index key.)

To decide whether to use a column index or functional index, determine whether a column index is the right choice for the data that you want to index. An index on a column of some data types might not be useful for typical queries. For example, the following query asks how many images are dark: 
SELECT COUNT(*) FROM photos WHERE 
darkness(picture) > 0.5

An index on the picture data itself does not improve the query performance. The concepts of less than, greater than, and equal are not particularly meaningful when applied to an image data type. Instead, a functional index that uses the darkness() function can improve performance. You might also have a user-defined function that runs frequently enough that performance improves when you create an index on its values.