Note
This variable was introduced in OceanBase Database V4.2.4.
Description
cardinality_estimation_model specifies the correlation assumption used by the optimizer for cardinality estimation.
Notice
Run a full test on this variable before you use it in your production environment.
Limitations
Query the variable
In the
systenant or a user tenant, you can execute theSHOW VARIABLESstatement to query the variable at the session level or query theSYS.TENANT_VIRTUAL_SESSION_VARIABLEview (in Oracle mode) orinformation_schema.SESSION_VARIABLESview (in MySQL mode) for the variable at the session level.Modify the variable
In the
systenant or a user tenant, you can directly change the value of the variable at the session level for the current tenant.
Attributes
Attribute |
Description |
|---|---|
| Type | ENUM |
| Default value | PARTIAL |
| Value range | INDEPENDENT: assumes that predicates are completely independent.PARTIAL: assumes that predicates are partially correlated.FULL: assumes that predicates are completely correlated.NoteIn most scenarios, the cardinality estimation results based on the three correlation assumptions sequentially increase: |
| Effective scope | |
| Plan generation affected | Yes |
| Modifiable | Yes. You can use the SET statement to modify the variable. |
Examples
Run cardinality estimation on a table named t1 based on the three correlation assumptions. The results are 10, 31, and 98, respectively.
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
INDEPENDENTfor 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
PARTIALfor 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
FULLfor 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)