Specify 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.