Specifying Cost and Selectivity

You can provide the cost and selectivity of the function to the optimizer. The database server uses cost and selectivity together to determine the best path.

To provide the cost and selectivity for a function, include modifiers in the CREATE FUNCTION statement. You can include the cost and selectivity values in the CREATE FUNCTION statement or calculate the values with functions called during the optimization phase.

If you do not specify your own cost and selectivity values for a function, the database server uses a default selectivity of 0.1 and a default cost of 0. Because the default cost and selectivity are low, the database server considers a UDR with default cost and selectivity inexpensive to execute and will most likely execute that UDR before other UDRs in the WHERE clause.

The database server assigns a cost of 0 to all built-in functions, such as SIN and DATE.

Constant Cost and Selectivity Values

The following modifiers specify a cost or selectivity value when you execute the CREATE FUNCTION statement. The cost or selectivity value does not change for each invocation of the function:

• percall_cost=integer

  The percall_cost modifier specifies the cost of executing the function once. The integer value is a number.

• selconst=float

  The selconst modifier specifies the selectivity of a function. The float value is a floating-point number between 0 and 1 that represents the fraction of the rows for which you expect the routine to return TRUE.

Dynamic Cost and Selectivity Values

In some cases, the cost and selectivity of a function can vary significantly, depending upon the input to the function. If the input can change the optimization, use the following modifiers, which specify a function that computes the cost and selectivity at runtime:

• costfunc=CostFunction

  This modifier specifies the name of a function, CostFunction, that the optimizer executes to find the cost of executing your function one time.

• selfunc=SelectivityFunction

  This modifier specifies the name of a function, SelectivityFunction, that the optimizer executes to find the selectivity of your function.

You write these cost and selectivity functions to provide the optimizer with enough information about your function to create the best query plan.

The selectivity functions for a UDT might need statistics about the nature of the data in the UDT column. The database server does not generate distributions or maximum and minimum value statistics for a UDT. You need to write and register user-defined statistics functions to generate and store statistics for a UDT in the system catalog tables, in the same locations as statistics stored for built-in data types. For more information about user-defined statistics, refer to Extending UPDATE STATISTICS. For information about writing these functions, refer to the IBM Informix: DataBlade API Programmer's Guide.