range_index_dive_limit

Overview

range_index_dive_limit specifies the maximum number of ranges that the optimizer can use for row estimation at the storage layer.

Privilege requirements

• Query variables

  • Global level

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

  • Session level

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

• Modify variables

  • Set the variable at the global level

    • The sys tenant can directly modify the values of global system variables.
    • MySQL user tenants must have the SUPER or ALTER SYSTEM privilege to modify the value of a global system variable.
    • Oracle-compatible user tenants must have the ALTER SYSTEM privilege to modify the values of global system variables.

  • Set the variable at the session level

    The sys tenant and all user tenants can directly modify the values of session system variables for their own tenants.

Attributes

Usage instructions

Modify this variable to control the maximum number of ranges allowed during row estimation for storage layer optimization:

• When the number of ranges is less than this variable, all ranges are always selected for storage-layer row estimation.
• When the number of ranges is greater than or equal to this variable, randomly select range_index_dive_limit ranges for storage-layer row estimation and scale the estimation results proportionally.
• When this variable is set to 0, all ranges are always selected for storage-layer row estimation.
• When both range_index_dive_limit and partition_index_dive_limit are negative, the storage layer estimation strategy rolls back to an older version, performing storage layer estimation only within the first 10 ranges of a single partition.

Configuration example

1. Create a table and insert data into it.

   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. View the execution plan. In typical scenarios, storage-layer row estimation is used for all ranges that do not exceed 10.

   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. Modify the variable to select only one range for storage layer row estimation.

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

4. Examine the execution plan and note that only the third range (3, MIN; 3, MAX) is selected for storage layer row estimation. The estimated row count 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