Selectivity and cost functions

You can use the CREATE FUNCTION statement to create a UDR. Then, you can use routine modifiers to change the cost or selectivity that is specified in the statement.

After you create a UDR, you can place it in an SQL statement.

The following example shows how you can place a UDR in an SQL statement: 
SELECT * FROM image 
WHERE get_x1(image.im2) and get_x2(image.im1)

The optimizer cannot accurately evaluate the cost of executing a UDR without additional information. 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. For more information about selectivity, see Filters with user-defined routines.

In the previous example, the optimizer cannot determine which function to execute first, the get_x1 function or the get_x2 function. If a function is expensive to execute, the DBA can assign the function a larger cost or selectivity, which can influence the optimizer to change the query plan for better performance. In the previous example, if get_x1 costs more to execute, the DBA can assign a higher cost to the function, which can cause the optimizer to execute the get_x2 function first.

You can add the following routine modifiers to the CREATE FUNCTION statement to change the cost or selectivity that the optimizer assigns to the function:
  • selfunc=function_name
  • selconst=integer
  • costfunc=function_name
  • percall_cost=integer

For more information about cost or selectivity modifiers, see the HCL OneDB™ User-Defined Routines and Data Types Developer's Guide.