Using the CARDINALITY clause to improve the performance of queries with user-defined table function references

The cardinality of a user-defined table function is the number of rows that are returned when the function is invoked. Db2 uses this number to estimate the cost of executing a query that invokes a user-defined table function.

Begin program-specific programming interface information. The cost of executing a query is one of the factors that Db2 uses when it calculates the access path. Therefore, if you give Db2 an accurate estimate of a user-defined table function's cardinality, Db2 can better calculate the best access path.

You can specify a cardinality value for a user-defined table function by using the CARDINALITY clause of the SQL CREATE FUNCTION or ALTER FUNCTION statement. However, this value applies to all invocations of the function, whereas a user-defined table function might return different numbers of rows, depending on the query in which it is referenced.

To give Db2 a better estimate of the cardinality of a user-defined table function for a particular query, you can use the CARDINALITY or CARDINALITY MULTIPLIER clause in that query. Db2 uses those clauses at bind time when it calculates the access cost of the user-defined table function. Using this clause is recommended only for programs that run on Db2 for z/OS® because the clause is not supported on earlier versions of Db2.

Example of using the CARDINALITY clause to specify the cardinality of a user-defined table function invocation

Suppose that when you created user-defined table function TUDF1, you set a cardinality value of 5, but in the following query, you expect TUDF1 to return 30 rows:

SELECT *
 FROM TABLE(TUDF1(3)) AS X;

Add the CARDINALITY 30 clause to tell Db2 that, for this query, TUDF1 should return 30 rows:

SELECT *
 FROM TABLE(TUDF1(3) CARDINALITY 30) AS X;

Example of using the CARDINALITY MULTIPLIER clause to specify the cardinality of a user-defined table function invocation

Suppose that when you created user-defined table function TUDF2, you set a cardinality value of 5, but in the following query, you expect TUDF2 to return 30 times that many rows:

SELECT *
 FROM TABLE(TUDF2(10)) AS X;

Add the CARDINALITY MULTIPLIER 30 clause to tell Db2 that, for this query, TUDF1 should return 5*30, or 150, rows:

SELECT *
 FROM TABLE(TUDF2(10) CARDINALITY MULTIPLIER 30) AS X;

End program-specific programming interface information.