Query filters can include user-defined routines. You can improve the selectivity of filters that include UDRs with the following features:
You can create a functional index on the resulting values of a user-defined routine or a built-in function that operates on one or more columns. When you create a functional index, the database server computes the return values of the function and stores them in the index. The database server can locate the return value of the function in an appropriate index without executing the function for each qualifying column.
For more information on indexing user-defined functions, see Using a Functional Index.
You can write a function that calculates the expected fraction of rows that qualify for the function. For a brief description of user-defined selectivity functions, see Selectivity and Cost Functions. For more information on how to write and register user-defined selectivity functions, see IBM Informix: User-Defined Routines and Data Types Developer's Guide.