range_index_dive_limit

Description

range_index_dive_limit specifies the number of ranges for row estimation by the optimizer based on the storage layer.

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

Considerations

You can modify this variable to specify the number of ranges for row estimation by the optimizer based on the storage layer.

• When the number of ranges is less than the specified value, all ranges are used for row estimation based on the storage layer.
• When the number of ranges is greater than or equal to the specified value, ranges of a quantity equal to the specified value are randomly selected for row estimation based on the storage layer, and the row estimation result is amplified proportionally.
• When the value of this variable is 0, all ranges are used for row estimation based on the storage layer.
• When the values of the range_index_dive_limit and partition_index_dive_limit variables are both negative, the strategy for row estimation based on the storage layer is rolled back to that in an earlier version. In this case, no more than 10 ranges in a single partition are used for row estimation based on the storage layer.

Examples

1. Create a table and insert data into the table.

   obclient> CREATE TABLE t1(c1 int, c2 int, index i1(c1));
   Query OK, 0 rows affected (0.173 sec)
   
   obclient> INSERT INTO t1 SELECT 1,1 FROM table(generator(10));
   Query OK, 10 rows affected (0.003 sec)
   Records: 10  Duplicates: 0  Warnings: 0
   
   obclient> INSERT INTO t1 SELECT 2,2 FROM table(generator(20));
   Query OK, 20 rows affected (0.001 sec)
   Records: 20  Duplicates: 0  Warnings: 0
   
   obclient> INSERT INTO t1 SELECT 3,3 FROM table(generator(30));
   Query OK, 30 rows affected (0.001 sec)
   Records: 30  Duplicates: 0  Warnings: 0
   

2. Query the execution plan. By default, if the number of ranges does not exceed 10, all ranges are used for row estimation based on the storage layer.

   obclient> EXPLAIN SELECT c1 FROM t1 WHERE c1 in (1,2,3);
   +---------------------------------------------------------------------------------------------------------+
   | Query Plan                                                                                              |
   +---------------------------------------------------------------------------------------------------------+
   | ==================================================                                                      |
   | |ID|OPERATOR        |NAME  |EST.ROWS|EST.TIME(us)|                                                      |
   | --------------------------------------------------                                                      |
   | |0 |TABLE RANGE SCAN|t1(i1)|60      |11          |                                                      |
   | ==================================================                                                      |
   | Outputs & filters:                                                                                      |
   | -------------------------------------                                                                   |
   |   0 - output([t1.c1]), filter(nil), rowset=256                                                          |
   |       access([t1.c1]), partitions(p0)                                                                   |
   |       is_index_back=false, is_global_index=false,                                                       |
   |       range_key([t1.c1], [t1.__pk_increment]), range(1,MIN ; 1,MAX), (2,MIN ; 2,MAX), (3,MIN ; 3,MAX),  |
   |       range_cond([t1.c1 IN (1, 2, 3)])                                                                  |
   +---------------------------------------------------------------------------------------------------------+
   12 rows in set (0.008 sec)
   

3. Change the value of this variable to 1 to select only one range for row estimation based on the storage layer.

   obclient> SET range_index_dive_limit = 1;
   Query OK, 0 rows affected (0.001 sec)
   

4. Query the execution plan. The query result shows that only the third range (3, MIN; 3, MAX) is used for row estimation based on the storage layer and the row estimation result is 30 × 3 = 90.

   obclient> EXPLAIN SELECT c1 FROM t1 WHERE c1 in (1,2,3);
   +---------------------------------------------------------------------------------------------------------+
   | Query Plan                                                                                              |
   +---------------------------------------------------------------------------------------------------------+
   | ==================================================                                                      |
   | |ID|OPERATOR        |NAME  |EST.ROWS|EST.TIME(us)|                                                      |
   | --------------------------------------------------                                                      |
   | |0 |TABLE RANGE SCAN|t1(i1)|90      |12          |                                                      |
   | ==================================================                                                      |
   | Outputs & filters:                                                                                      |
   | -------------------------------------                                                                   |
   |   0 - output([t1.c1]), filter(nil), rowset=256                                                          |
   |       access([t1.c1]), partitions(p0)                                                                   |
   |       is_index_back=false, is_global_index=false,                                                       |
   |       range_key([t1.c1], [t1.__pk_increment]), range(1,MIN ; 1,MAX), (2,MIN ; 2,MAX), (3,MIN ; 3,MAX),  |
   |       range_cond([t1.c1 IN (1, 2, 3)])                                                                  |
   +---------------------------------------------------------------------------------------------------------+
   12 rows in set (0.003 sec)
   

References

partition_index_dive_limit