Query cost
If your UDR requires a lot of system resources (such as a large number of disk accesses or network accesses), you can define a cost-of-execution for the UDR. The query optimizer uses the cost value to determine the total cost of executing a query.
Tip: You can define a cost for either a user-defined
procedure or a user-defined function. However, user-defined functions
can appear in queries because they return a value. Because user-defined
procedures do not appear in queries, the query optimizer is not usually
concerned with their cost.
When you register a UDR, you can specify its cost with one of the
following routine modifiers.
| Cost | Routine modifier |
|---|---|
| Cost is constant for every invocation of the UDR. | PERCALL_COST = cost_value cost_value is an integer. |
| Cost varies according to some execution conditions. | COSTFUNC = cost_func cost_func is the name of a cost UDR that returns an integer to indicate the cost of the UDR. |
When the query optimizer needs to determine the cost of the UDR, it either uses the constant cost value or calls the cost function, depending whether the UDR was registered with the PERCALL_COST or COSTFUNC routine modifier.