Note
- Starting from V4.3.3, the `cardinality_estimation_model` view is introduced in V4.3.x.
- Starting from V4.2.4, the `cardinality_estimation_model` view is introduced in V4.2.x.
Description
The cardinality_estimation_model view is used to set the correlation assumption used by the optimizer during cardinality estimation.
Notice
We recommend that you thoroughly test this option before you use it in a production environment.
Privilege requirements
Query variables
Global level
Users in the
systenant and all user tenants can execute theSHOW VARIABLESstatement or viewSYS.TENANT_VIRTUAL_GLOBAL_VARIABLE(Oracle mode) orinformation_schema.GLOBAL_VARIABLES(MySQL mode) to query the values of global system variables.Session level
Users in the
systenant and all user tenants can execute theSHOW VARIABLESstatement or viewSYS.TENANT_VIRTUAL_SESSION_VARIABLE(Oracle mode) orinformation_schema.SESSION_VARIABLES(MySQL mode) to query the values of session system variables.
Modify variables
Set the variable at the global level
Users in the
systenant can directly modify the values of global system variables.MySQL users in a user tenant need the
SUPERorALTER SYSTEMprivilege to modify the values of global system variables.Oracle users in a user tenant need the
ALTER SYSTEMprivilege to modify the values of global system variables.
Set the variable at the session level
Users in the
systenant and all user tenants can directly modify the values of session system variables in their respective tenants.
Attributes
| Attribute | Description |
|---|---|
| Type | Enum |
| Default value | PARTIAL |
| Value range |
NoteIn most cases, the row estimation results for the three correlation assumptions are as follows: INDEPENDENT < PARTIAL < FULL. |
| Effective scope |
|
| Modifiable | Yes. You can modify this variable by using the SET statement. |
Example
The row estimation results for t1 after filtering with base table predicates are 10, 31, and 98, respectively, when the correlation assumption is set to INDEPENDENT, PARTIAL, and FULL.
Create a table named
t1.obclient [test]> create table t1(c1 int, c2 int); Query OK, 0 rows affected (0.103 sec)Insert data into the
t1table.obclient [test]> insert into t1 select row_number() over () % 10, row_number() over () % 100 from table(generator(10000)); Query OK, 10000 rows affected (0.077 sec) Records: 10000 Duplicates: 0 Warnings: 0Analyze the
t1table.obclient [test]> analyze table t1; Query OK, 0 rows affected (0.073 sec)Set the correlation assumption to
INDEPENDENTand execute cardinality estimation.obclient [test]> set cardinality_estimation_model = 'independent'; Query OK, 0 rows affected (0.001 sec)Query the cardinality estimation result.
obclient [test]> explain select * from t1 where c1 = 1 and c2 = 1;The query result is as follows:
+------------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------------+ | =============================================== | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| | | ----------------------------------------------- | | |0 |TABLE FULL SCAN|t1 |10 |734 | | | =============================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([t1.c1], [t1.c2]), filter([t1.c2 = 1], [t1.c1 = 1]), rowset=16 | | access([t1.c1], [t1.c2]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false,false], | | range_key([t1.__pk_increment]), range(MIN ; MAX)always true | +------------------------------------------------------------------------------------------+ 11 rows in set (0.011 sec)Set the correlation assumption to
PARTIALand execute cardinality estimation.obclient [test]> set cardinality_estimation_model = 'partial'; Query OK, 0 rows affected (0.001 sec)Query the cardinality estimation result.
obclient [test]> explain select * from t1 where c1 = 1 and c2 = 1;The query result is as follows:
+------------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------------+ | =============================================== | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| | | ----------------------------------------------- | | |0 |TABLE FULL SCAN|t1 |31 |735 | | | =============================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([t1.c1], [t1.c2]), filter([t1.c2 = 1], [t1.c1 = 1]), rowset=256 | | access([t1.c1], [t1.c2]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false,false], | | range_key([t1.__pk_increment]), range(MIN ; MAX)always true | +------------------------------------------------------------------------------------------+ 11 rows in set (0.004 sec)Set the correlation assumption to
FULLand execute cardinality estimation.obclient [test]> set cardinality_estimation_model = 'full'; Query OK, 0 rows affected (0.000 sec)Query the cardinality estimation result.
obclient [test]> explain select * from t1 where c1 = 1 and c2 = 1;The query result is as follows:
+------------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------------+ | =============================================== | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| | | ----------------------------------------------- | | |0 |TABLE FULL SCAN|t1 |98 |738 | | | =============================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([t1.c1], [t1.c2]), filter([t1.c2 = 1], [t1.c1 = 1]), rowset=256 | | access([t1.c1], [t1.c2]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false,false], | | range_key([t1.__pk_increment]), range(MIN ; MAX)always true | +------------------------------------------------------------------------------------------+ 11 rows in set (0.003 sec)