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.

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

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

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

  1. Write the code for the user-defined darkness() function that operates on the data type and returns a decimal value.
  2. 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.

  3. 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 Informix® Administrator's Guide.

Copyright© 2019 HCL Technologies Limited