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
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
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 | Session |
| Modifiable | Yes, you can use theSETStatement 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 row estimation at the storage layer 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 estimation row strategy reverts to the old version's behavior. It performs storage-layer estimation only within the first 10 ranges of a single partition and does not sample beyond that.To disable row cardinality estimation at the storage layer, you can use the system variable
enable_storage_cardinality_estimation.
The differences from MySQL are as follows:
MySQL controls the number of equality ranges for index dive via the system variable
eq_range_index_dive_limit, with a default value of200. OceanBase AI Database considers both the number of equality ranges and inequality ranges.When the number of ranges is greater than or equal to the value of this variable, MySQL stops using index dive. However, OceanBase AI Database randomly selects up to the specified number of ranges for sampling and row estimation in the storage layer.
Configuration example
Set the number of ranges for storage-layer row estimation in the current session to 1, which means only one range is randomly selected for storage-layer row estimation.
obclient [test]> SET range_index_dive_limit = 1;
Query OK, 0 rows affected (0.002 sec)
For a complete example of observing the estimated row count through an execution plan, see range_index_dive_limit.