Selectivity and cost examples

The following example creates a function that determines if a point is within a circle. When an SQL statement contains this function, the optimizer executes the function contains_sel() to determine the selectivity of the contains() function.

CREATE FUNCTION contains(c circle, p point)
RETURNING boolean WITH(selfunc=contains_sel)
EXTERNAL NAME "$USERFUNCDIR/circle.so" LANGUAGE C;

The following example creates two functions, each with cost and selectivity values:

CREATE FUNCTION expensive(cust int)
RETURNING boolean WITH(percall_cost=50,selconst=.1)
EXTERNAL NAME "/ix/9.4/exp_func.so" LANGUAGE c;

CREATE FUNCTION cheap(cust int)
RETURNING boolean WITH(percall_cost=1,selconst=.1)
EXTERNAL NAME "/ix/9.4/exp_func.so" LANGUAGE C; 

When both of these functions are in one SQL statement, the optimizer executes the cheap() function first because of the lower cost. The following SET EXPLAIN output, which lists cheap() first in the Filters: line, shows that indeed the optimizer did execute cheap() first:

QUERY:
------
select * from customer
where expensive(customer_num)
and cheap(customer_num)
Estimated Cost: 8
Estimated # of Rows Returned: 1
  1) informix.customer: SEQUENTIAL SCAN
        Filters: (lsuto.cheap(informix.customer.customer_num )AND
lsuto.expensive(informix.customer.customer_num ))