cardinality_estimation_model

Description

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

Limitations

• Query the variable

  • Global-level operation

    In the sys tenant or a user tenant, you can execute the SHOW VARIABLES statement to query the variable at the global level or query the SYS.TENANT_VIRTUAL_GLOBAL_VARIABLE view (in Oracle mode) or information_schema.GLOBAL_VARIABLES view (in MySQL mode) for the variable at the global level.

  • Session-level operation

    In the sys tenant or a user tenant, you can execute the SHOW VARIABLES statement to query the variable at the session level or query the SYS.TENANT_VIRTUAL_SESSION_VARIABLE view (in Oracle mode) or information_schema.SESSION_VARIABLES view (in MySQL mode) for the variable at the session level.

• Modify the variable

  • Global-level operation

    • In the sys tenant, you can directly change the value of the variable at the global level.

    • In a MySQL user tenant, you must have the SUPER or ALTER SYSTEM privilege to change the value of the variable at the global level.

    • In OceanBase Database V4.2.0 and later V4.x versions, you must have the ALTER SYSTEM privilege to change the value of the variable at the global level in an Oracle user tenant.

  • Session-level operation

    In the sys tenant or a user tenant, you can directly change the value of the variable at the session level for the current tenant.

Attributes

Examples

Run cardinality estimation on a table named t1 based on the three correlation assumptions. The results are 10, 31, and 98, respectively.

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 for 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 for 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 for 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