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 Extend UPDATE STATISTICS. For information about writing these functions, refer to the HCL OneDB™ DataBlade® API Programmer's Guide.