PERCALL_COST (C)

Use the PERCALL_COST modifier to specify the approximate CPU usage cost that a UDR incurs each time it executes.

The optimizer uses the cost you specify to determine the order in which to evaluate SQL predicates in the UDR for best performance. For example, the following query has two predicates joined by a logical AND:

SELECT * FROM tab1 WHERE func1() = 10 AND func2() = 'abc';

In this example, if one predicate returns FALSE, the optimizer need not evaluate the other predicate.

The optimizer uses the specified cost to order the predicates so that the least expensive predicate is evaluated first. The CPU usage cost must be an integer between 1 and 2³¹-1, with 1 the lowest cost and 2³¹-1 the most expensive.

To calculate an approximate cost per call, add the following two figures:

The number of lines of code executed each time the UDR is called
The number of predicates that require an I/O access

The default cost per execution is 0. When you drop the PERCALL_COST modifier, the cost per execution returns to 0.