partition_index_dive_limit

Last Updated：2025-11-19 10:08:13 Updated

Note

For OceanBase Database V4.2.x, this variable was introduced in OceanBase Database V4.2.5 and V4.2.1 BP9.

Description

partition_index_dive_limit specifies the number of partitions 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

Attribute	Description
Type	Integer
Default value	10
Value range	[0, INT64_MAX] Note `0` indicates no limitation. You can also set this variable to a negative value, which has the same meaning as `0`. > `INT64_MAX` indicates the maximum value of the 64-bit signed integer type.
Applicable scope	Global Session
Modifiable	Yes

Considerations

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

When the number of partitions is less than the specified value, all partitions are used for row estimation based on the storage layer.
When the number of partitions is greater than or equal to the specified value, partitions 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 partitions 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 a version earlier than V4.2.5. In this case, no more than 10 ranges in a single partition are used for row estimation based on the storage layer, and no sampling is performed upon timeout.

Examples

Create a table and insert data into the table.

obclient> CREATE TABLE t11(c1 int, c2 int, index i1(c2)) PARTITION BY range(c1) (partition p0 values less than(10), partition p1 values less than(20), partition p2 values less than(30));
Query OK, 0 rows affected (0.112 sec)

obclient> INSERT INTO t11 SELECT 1,1 FROM table(generator(10));
Query OK, 10 rows affected (0.007 sec)
Records: 10  Duplicates: 0  Warnings: 0

obclient> INSERT INTO t11 SELECT 11,11 FROM table(generator(20));
Query OK, 20 rows affected (0.003 sec)
Records: 20  Duplicates: 0  Warnings: 0

obclient> INSERT INTO t11 SELECT 21,21 FROM table(generator(30));
Query OK, 30 rows affected (0.003 sec)
Records: 30  Duplicates: 0  Warnings: 0

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

obclient [test]> EXPLAIN SELECT c1 FROM t11 WHERE c2 in (1,11,21);
+---------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                    |
+---------------------------------------------------------------------------------------------------------------+
| =============================================================                                                 |
| |ID|OPERATOR                 |NAME    |EST.ROWS|EST.TIME(us)|                                                 |
| -------------------------------------------------------------                                                 |
| |0 |PX COORDINATOR           |        |60      |253         |                                                 |
| |1 |└─EXCHANGE OUT DISTR     |:EX10000|60      |234         |                                                 |
| |2 |  └─PX PARTITION ITERATOR|        |60      |193         |                                                 |
| |3 |    └─TABLE RANGE SCAN   |t11(i1) |60      |193         |                                                 |
| =============================================================                                                 |
| Outputs & filters:                                                                                            |
| -------------------------------------                                                                         |
|   0 - output([INTERNAL_FUNCTION(t11.c1)]), filter(nil), rowset=256                                            |
|   1 - output([INTERNAL_FUNCTION(t11.c1)]), filter(nil), rowset=256                                            |
|       dop=1                                                                                                   |
|   2 - output([t11.c1]), filter(nil), rowset=256                                                               |
|       force partition granule                                                                                 |
|   3 - output([t11.c1]), filter(nil), rowset=256                                                               |
|       access([t11.__pk_increment], [t11.c1]), partitions(p[0-2])                                              |
|       is_index_back=true, is_global_index=false,                                                              |
|       range_key([t11.c2], [t11.__pk_increment]), range(1,MIN ; 1,MAX), (11,MIN ; 11,MAX), (21,MIN ; 21,MAX),  |
|       range_cond([t11.c2 IN (1, 11, 21)])                                                                     |
+---------------------------------------------------------------------------------------------------------------+
20 rows in set (0.007 sec)

Change the value of this variable to 1 to select only one partition for row estimation based on the storage layer.
```
obclient [test]> SET partition_index_dive_limit = 1;
Query OK, 0 rows affected (0.000 sec)
```

Query the execution plan. The query result shows that only one partition is used for row estimation based on the storage layer and the row estimation result is 10 × 3 = 30.

obclient [test]> EXPLAIN SELECT c1 FROM t11 WHERE c2 in (1,11,21);
+---------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                    |
+---------------------------------------------------------------------------------------------------------------+
| =============================================================                                                 |
| |ID|OPERATOR                 |NAME    |EST.ROWS|EST.TIME(us)|                                                 |
| -------------------------------------------------------------                                                 |
| |0 |PX COORDINATOR           |        |30      |148         |                                                 |
| |1 |└─EXCHANGE OUT DISTR     |:EX10000|30      |139         |                                                 |
| |2 |  └─PX PARTITION ITERATOR|        |30      |118         |                                                 |
| |3 |    └─TABLE RANGE SCAN   |t11(i1) |30      |118         |                                                 |
| =============================================================                                                 |
| Outputs & filters:                                                                                            |
| -------------------------------------                                                                         |
|   0 - output([INTERNAL_FUNCTION(t11.c1)]), filter(nil), rowset=256                                            |
|   1 - output([INTERNAL_FUNCTION(t11.c1)]), filter(nil), rowset=256                                            |
|       dop=1                                                                                                   |
|   2 - output([t11.c1]), filter(nil), rowset=256                                                               |
|       force partition granule                                                                                 |
|   3 - output([t11.c1]), filter(nil), rowset=256                                                               |
|       access([t11.__pk_increment], [t11.c1]), partitions(p[0-2])                                              |
|       is_index_back=true, is_global_index=false,                                                              |
|       range_key([t11.c2], [t11.__pk_increment]), range(1,MIN ; 1,MAX), (11,MIN ; 11,MAX), (21,MIN ; 21,MAX),  |
|       range_cond([t11.c2 IN (1, 11, 21)])                                                                     |
+---------------------------------------------------------------------------------------------------------------+
20 rows in set (0.003 sec)

References

range_index_dive_limit