Note
This variable is available starting from V5.0.1.
Description
ob_udf_cost_factor specifies the cost factor for UDF filter predicates.
When the optimizer estimates the cost of queries containing UDF predicates, it uses this variable to estimate the actual runtime cost of processing each record with the UDF predicate. When your UDF contains complex internal logic (such as nested SQL), you can increase this value appropriately so that the UDF predicate is placed after predicates with higher selectivity during predicate reordering, thereby avoiding calling high-cost UDFs on the full dataset.
Privilege requirements
Query variables
Global level
systenant and all user tenants can execute theSHOW VARIABLESstatement or query theSYS.TENANT_VIRTUAL_GLOBAL_VARIABLEview (Oracle-compatible mode) or theinformation_schema.GLOBAL_VARIABLESview (MySQL-compatible mode) to obtain the value of a global system variable.Session level
systenant and all user tenants can execute theSHOW VARIABLESstatement or query theSYS.TENANT_VIRTUAL_SESSION_VARIABLEview (Oracle-compatible mode) or theinformation_schema.SESSION_VARIABLESview (MySQL-compatible mode) to obtain the value of a session system variable.
Modify variables
Global level
systenant can directly modify the value of a global system variable.MySQL user tenants must have the
SUPERorALTER SYSTEMprivilege to modify the value of a global system variable.Oracle user tenants must have the
ALTER SYSTEMprivilege to modify the value of a global system variable.
Session level
systenant and all user tenants can directly modify the value of a session system variable.
Attributes
Attribute |
Description |
|---|---|
| Type | int |
| Default value | 100 |
| Value range | [1, 10000000] |
| Scope |
|
| Affects plan generation | Yes. This variable has the INFLUENCE_PLAN flag. When the variable value changes, plan eviction is triggered. |
| Modifiable | Yes. You can execute the SET or ALTER SESSION SET statement to modify the value. |
Usage notes
Assume there is a table t with 1,000,000 rows and the following query is executed:
SELECT * FROM t WHERE c1 > 0 AND udf(c2) = 1;
The optimizer uses ob_udf_cost_factor to determine the execution order of the udf(c2) = 1 predicate and the c1 > 0 predicate during estimation. The larger the value of ob_udf_cost_factor, the higher the estimated cost of the UDF predicate, and the more likely it is to be executed last. When the internal logic of the UDF is complex, it is recommended to increase this value appropriately to ensure that the UDF predicate is executed after the data has been sufficiently filtered.
Examples
Set the UDF predicate cost factor to 100000 (session level).
obclient> SET ob_udf_cost_factor = 100000;
Set the UDF predicate cost factor to 100000 (global level).
obclient> SET GLOBAL ob_udf_cost_factor = 100000;