cardinality_estimation_model

Description

The cardinality_estimation_model view is used to set the correlation assumption used by the optimizer during cardinality estimation.

Privilege requirements

• Query variables

  • Global level

    Users in the sys tenant and all user tenants can execute the SHOW VARIABLES statement or view SYS.TENANT_VIRTUAL_GLOBAL_VARIABLE (Oracle-compatible mode) or information_schema.GLOBAL_VARIABLES (MySQL-compatible mode) to query the values of global system variables.

  • Session level

    Users in the sys tenant and all user tenants can execute the SHOW VARIABLES statement or view SYS.TENANT_VIRTUAL_SESSION_VARIABLE (Oracle-compatible mode) or information_schema.SESSION_VARIABLES (MySQL-compatible mode) to query the values of session system variables.

• Modify variables

  • Set the variable at the global level

    • Users in the sys tenant can directly modify the values of global system variables.

    • MySQL users in a user tenant need the SUPER or ALTER SYSTEM privilege to modify the values of global system variables.

    • For V4.x, Oracle users in a user tenant need the ALTER SYSTEM privilege to modify the values of global system variables starting from V4.2.0.

  • Set the variable at the session level

    Users in the sys tenant and all user tenants can directly modify the values of session system variables in their respective tenants.

Attributes

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.

1. Create a table named t1.

   obclient [test]> create table t1(c1 int, c2 int);
   Query OK, 0 rows affected (0.103 sec)
   

2. Insert data into the t1 table.

   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: 0
   

3. Analyze the t1 table.

   obclient [test]> analyze table t1;
   Query OK, 0 rows affected (0.073 sec)
   

4. Set the correlation assumption to INDEPENDENT and execute cardinality estimation.

   obclient [test]> set cardinality_estimation_model = 'independent';
   Query OK, 0 rows affected (0.001 sec)
   

5. 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)
   

6. Set the correlation assumption to PARTIAL and execute cardinality estimation.

   obclient [test]> set cardinality_estimation_model = 'partial';
   Query OK, 0 rows affected (0.001 sec)
   

7. 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)
   

8. Set the correlation assumption to FULL and execute cardinality estimation.

   obclient [test]> set cardinality_estimation_model = 'full';
   Query OK, 0 rows affected (0.000 sec)
   

9. 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)
   

References

Use statistical information and row estimation