ob_udf_selectivity

Description

ob_udf_selectivity specifies the default selectivity of UDF equality predicates.

When the optimizer estimates the cost of queries containing UDF predicates, it uses this variable as the default selectivity of UDF equality predicates (such as udf(col) = 1), which participates in the calculation of predicate reordering. You can adjust this value based on the actual filtering effect of the UDF to influence the optimizer's decision on the execution order of predicates.

Privilege requirements

• Query variables

  • Global level

    sys tenant and all user tenants can execute the SHOW VARIABLES statement or query the SYS.TENANT_VIRTUAL_GLOBAL_VARIABLE view (Oracle-compatible mode) or the information_schema.GLOBAL_VARIABLES view (MySQL-compatible mode) to obtain the value of a global system variable.

  • Session level

    sys tenant and all user tenants can execute the SHOW VARIABLES statement or query the SYS.TENANT_VIRTUAL_SESSION_VARIABLE view (Oracle-compatible mode) or the information_schema.SESSION_VARIABLES view (MySQL-compatible mode) to obtain the value of a session system variable.

• Modify variables

  • Global level

    • sys tenant can directly modify the value of a global system variable.

    • MySQL user tenants must have the SUPER or ALTER SYSTEM privilege to modify the value of a global system variable.

    • Oracle user tenants must have the ALTER SYSTEM privilege to modify the value of a global system variable.

  • Session level

    sys tenant and all user tenants can directly modify the value of a session system variable.

Attributes

Usage notes

ob_udf_selectivity and ob_udf_cost_factor jointly determine the rank value of UDF predicates in predicate reordering. The optimizer's goal is to place predicates with smaller rank values earlier in the execution order:

• The rank calculation formula is approximately (selectivity - 1) / cost_factor.
• ob_udf_selectivity affects the selectivity term: a smaller value means stronger UDF filtering capability (fewer rows retained), resulting in a smaller rank, and the UDF is more likely to be executed earlier.
• ob_udf_cost_factor affects the cost term: a larger value means higher single-row processing cost of the UDF, resulting in a larger rank (closer to 0), and the UDF is more likely to be executed later.

In general, the default values are sufficient. You can adjust this variable when you have a clear expectation of the actual filtering capability of the UDF.

Examples

Set the UDF equality predicate selectivity to 0.999 (session level).

obclient> SET ob_udf_selectivity = 0.999;

Set the UDF equality predicate selectivity to 0.999 (global level).

obclient> SET GLOBAL ob_udf_selectivity = 0.999;

References

• ob_udf_cost_factor