Filters with user-defined routines

You can improve the selectivity of query filters that include user-defined routines (UDRs).

You can improve the selectivity if the UDRs have the following features:
  • Functional indexes

    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 about indexing user-defined functions, see Using a functional index.

  • User-defined selectivity functions

    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 about how to write and register user-defined selectivity functions, see HCL OneDB™ User-Defined Routines and Data Types Developer's Guide.