Overview
range_index_dive_limit specifies the maximum number of ranges allowed for row estimation at the storage layer by the optimizer.
Privilege requirements
Query variables
Global level
Both the
systenant and all user tenants can use theSHOW VARIABLESstatement or theinformation_schema.GLOBAL_VARIABLESview to view the values of global system variables.Session level
Both the
systenant and all user tenants can use theSHOW VARIABLESstatement or theinformation_schema.SESSION_VARIABLESview to view the values of session system variables.
Modify variables
Global effective
The
systenant can directly modify the values of global system variables.The user tenant must have the
SUPERorALTER SYSTEMprivilege to modify the value of a global system variable.
Session expiration
The
systenant and all user tenants can directly modify the values of session system variables for their own tenants.
Attributes
Attribute |
Description |
|---|---|
| Parameter type | Int |
| Default Value | 10 |
| Value range | [0, INT64_MAX]
Note
|
| Effective Scope |
|
| Modifiable | Yes, you can use the following methods:SETStatement modification. |
Usage instructions
Modify this variable to control the maximum number of ranges allowed when the optimizer estimates rows for storage layer queries:
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_limitranges 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. This behavior is the same as when the MySQL system variableeq_range_index_dive_limitis set to0, with no restrictions.When both
range_index_dive_limitandpartition_index_dive_limitare negative, the storage-layer row estimation strategy reverts to the old version's behavior. It performs storage-layer row estimation only within the first 10 ranges of a single partition and does not sample beyond that.To disable storage layer cardinality estimation, you can use the system variable
enable_storage_cardinality_estimation.
The differences from MySQL are as follows:
MySQL controls the number of equal-range indexes for index dive through the system variable
eq_range_index_dive_limit, with a default value of200. OceanBase AI Database considers both the number of equal-range and non-equal-range indexes.When the number of ranges is greater than or equal to the value of this variable, MySQL stops using index dive. In contrast, OceanBase AI Database randomly selects up to the specified number of ranges for sampling and row estimation in the storage layer.
Configuration example
Create table
t1and insert data into it.obclient [test]> CREATE TABLE t1(c1 int, c2 int, index i1(c1)); Query OK, 0 rows affected (0.433 sec) obclient [test]> INSERT INTO t1 SELECT 1,1 FROM table(generator(10)); Query OK, 10 rows affected (0.026 sec) Records: 10 Duplicates: 0 Warnings: 0 obclient [test]> INSERT INTO t1 SELECT 2,2 FROM table(generator(20)); Query OK, 20 rows affected (0.002 sec) Records: 20 Duplicates: 0 Warnings: 0 obclient [test]> INSERT INTO t1 SELECT 3,3 FROM table(generator(30)); Query OK, 30 rows affected (0.002 sec) Records: 30 Duplicates: 0 Warnings: 0View the execution plan. By default, storage-layer row estimation is used for all ranges that contain no more than 10 rows.
obclient [test]> EXPLAIN SELECT c1 FROM t1 WHERE c1 IN (1,2,3);The query result is as follows:
+------------------------------------------------------------------------------------------+ | 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.049 sec)Modify the variables to select only one range for row estimation at the storage layer.
obclient [test]> SET range_index_dive_limit = 1; Query OK, 0 rows affected (0.002 sec)Check the execution plan again and find that only one range is selected for storage-layer row estimation, with an estimated row count of 10 * 3 = 30.
obclient [test]> EXPLAIN SELECT c1 FROM t1 WHERE c1 IN (1,2,3);The query result is as follows:
+------------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------------+ | =============================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ----------------------------------------------- | | |0 |TABLE RANGE SCAN|t1(i1)|30 |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.006 sec)