cardinality_estimation_model

Description

cardinality_estimation_model specifies the correlation assumption used by the optimizer for cardinality estimation.

Privilege requirements

• Query variables

  • Global level

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

  • Session level

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

• Modify variables

  • Global level

    • sys tenants can 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 tenants and all user tenants can modify the value of a session system variable.

Attributes

Examples

The cardinality estimation results of the three correlation assumptions are 10, 31, and 98, respectively, after the base table t1 is filtered by predicates.

1. Create the t1 table.

   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 statistics and cost-based optimization