The EXTFNAPIV4_DESCRIBE_COL_DISTINCT_VALUES attribute describes the distinct values for a column. Used in a describe_column_get scenario.
a_v4_extfn_estimate
The estimated number of distinct values for a column. This property is valid only for table arguments.
If a UDF gets this property, it returns the estimated number of distinct values for a column.
On success, returns the sizeof(a_v4_extfn_estimate), if it returns a value, or:
Valid in:
Consider this procedure definition and code fragment in the _describe_extfn API function:
CREATE PROCEDURE my_tpf( col_char char(10), col_table TABLE( c1 INT, c2 INT ) ) RESULTS ( r1 INT, r2 INT, r3 INT ) EXTERNAL ‘my_tpf_proc@mylibrary’;
CREATE TABLE T( x INT, y INT, z INT );
select * from my_tpf( 'test', TABLE( select x,y from T ) )
This example shows how a TPF gets the number of distinct values for column one of the input table. A TPF may want to get this value, if it is beneficial for choosing an appropriate processing algorithm.
my_tpf_describe(a_v4_extfn_proc_context *cntxt) { if( cntxt->current_state == EXTFNAPIV4_STATE_PLAN_BUILDING ) { a_v4_extfn_estimate num_distinct; a_sql_int32 ret = 0; // Get the number of distinct values expected from the first column // of the table input parameter 'col_table' ret = cntxt->describe_column_get( cntxt, 2, 1 EXTFNAPIV4_DESCRIBE_COL_DISTINCT_VALUES, &num_distinct, sizeof(a_v4_extfn_estimate) ); // default algorithm is 1 _algorithm = 1; if( ret > 0 ) { // choose the best algorithm for sample size. if ( num_distinct.value < 100 ) { // use faster algorithm for small distinct values. _algorithm = 2; } } else { if ( ret < 0 ) { // Handle the error // or continue with default algorithm } else { // Attribute was unavailable // We will use the default algorithm. } } } }