range_index_dive_limit

Overview

range_index_dive_limit specifies the maximum number of ranges allowed for row estimation at the storage layer.

Privilege requirements

• Query variables
  • Global level 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), can be used by the sys tenant and all user tenants to view the values of global system variables.
  • Session level 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), can be used by both the sys tenant and all user tenants to view the values of session system variables.
• Modify variables
  • Global effective
    • 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.
    • For V4.x versions, starting from V4.2.0, an Oracle user tenant must have the ALTER SYSTEM privilege to modify the values of global system variables.
  • Session expiration 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 in the storage layer by the optimizer:

• 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 row estimation at the storage layer and scale the estimation results proportionally.
• When the variable is 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 row estimation strategy reverts to an older version, performing storage layer row 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 row estimation at the storage layer.

   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)