Creating a functional index

You can build a functional index on a user-defined function. The user-defined function can be either an external function or an SPL function.

About this task

Procedure

To build a functional index on a user-defined function:

Write the code for the user-defined function if it is an external function.
Register the user-defined function in the database with the CREATE FUNCTION statement.
Build the functional index with the CREATE INDEX statement.

Results

For example, to create a functional index on the darkness() function:

Write the code for the user-defined darkness() function that operates on the data type and returns a decimal value.
Register the user-defined function in the database with the CREATE FUNCTION statement:
```
CREATE FUNCTION darkness(im image)
RETURNS decimal 
EXTERNAL NAME '/lib/image.so' 
LANGUAGE C NOT VARIANT
```
In this example, you can use the default operator class for the functional index because the return value of the darkness() function is a built-in data type, DECIMAL.
Build the functional index with the CREATE INDEX statement.
```
CREATE TABLE photos
(
    name char(20), 
    picture image 
...
);
CREATE INDEX dark_ix ON photos (darkness(picture));
```
In this example, assume that the user-defined data type of image has already been defined in the database.

The optimizer can now consider the functional index when you specify the darkness() function as a filter in the query:

SELECT count(*) FROM photos WHERE 
darkness(picture) > 0.5

You can also create a composite index with user-defined functions. For more information, see Use composite indexes.

Warning: Do not create a functional index using either the DECRYPT_BINARY() or the DECRYPT_CHAR() function. These functions store plain text data in the database, defeating the purpose of encryption. For more information about encryption, see the HCL OneDB™ Administrator's Guide.