Selectivity functions

You can specify a selectivity function only for user-defined routines written in C.

A selectivity function estimates the percentage of rows that might be returned by your function, given a set of arguments. Define a selectivity function if you want to determine the cost of your function so that the query optimizer can determine when it is most efficient to call your function. Selectivity functions determine the cost of a function with statistics gathered about the values of the data type on which the function operates.

You can create a selectivity function for your user-defined function if your function compares or acts as a filter for two values of the following data types:
  • An opaque data type for which you have created user-defined statistics support routines.
  • A built-in data type.

The B-tree functions Equal() and NotEqual() that are overloaded for an opaque data type are good candidates for selectivity functions. Because the Equal() and NotEqual() functions are created with the Opaque Type wizard, you must add selectivity support by assigning selectivity routines on their properties pages after you create them.

Built-in data types have built-in statistics support routines, and all qualifying built-in functions (such as B-tree functions) have built-in selectivity functions. You can only create selectivity functions for functions that take built-in data types if those functions are user-defined.

A selectivity function must have the following properties:
  • A name in the form of FunctionSelectivity(), where Function is the name of the function to which it is assigned
  • A double-precision return type
  • Two arguments of type POINTER
  • All other properties as their default values