SELFUNC (C)

Use the SELFUNC modifier with a C UDR to name a companion user-defined function, called a selectivity function, to the current UDR. The selectivity function provides selectivity information about the current UDR to the optimizer.

The selectivity of a UDR is an estimate of the fraction of the rows that the query will select. That is, it is an estimate of the number of times the UDR will execute.

To execute sel_func, you must have Execute privilege on it and on the UDR.

Concept of Selectivity

Selectivity refers to the number of rows that would qualify for a query that does a search based on an equality predicate. The fewer the number of rows that qualify, the more selective the query.

For example, the following query has a search condition based on the customer_num column in the customer table:

SELECT * FROM customer WHERE customer_num = 102;

Because each row in the table has a different customer number, this query is highly selective. In contrast, the following query is not selective:

SELECT * FROM customer WHERE state = 'CA';

Because most of the rows in the customer table are for customers in California, more than half of the rows in the table would be returned.

Restrictions on the SELFUNC Modifier

The selectivity function that you specify must satisfy the following criteria:

• It must take the same number of arguments as the current C UDR.
• The data type of each argument must be SELFUNC_ARG.
• It must return a value of type FLOAT between 0 and 1, which represents the percentage of selectivity of the function. (1 is highly selective; 0 is not at all selective.)
• It can be written in any language that the database server supports.

A user who invokes the C UDR must have the Execute privilege both on that UDR and on the selectivity function that the SELFUNC modifier specifies.

Both the C UDR and the selectivity function must have the same owner.

For information on how to use the mi_funcarg* functions to extract information about the arguments of a selectivity function, see the IBM Informix DataBlade API Programmer's Guide.