Selectivity and Cost Functions

The CREATE FUNCTION command allows users to create UDRs. You can place a UDR in an SQL statement, as in the following example:

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 on 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 on cost or selectivity modifiers, see IBM Informix: User-Defined Routines and Data Types Developer's Guide.