partition_index_dive_limit

Overview

partition_index_dive_limit specifies the maximum number of partitions allowed when the optimizer uses storage-layer row estimation.

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 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 partitions allowed when the optimizer estimates rows for storage layer access:

• When the number of partitions is less than this value, all partitions are always selected for row estimation in the storage layer.
• When the number of partitions is greater than or equal to this variable, randomly select partition_index_dive_limit partitions for row estimation at the storage layer and scale the estimation results proportionally.
• When the variable is 0, all partitions 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 a version earlier than V4.2.5. In this mode, storage-layer row estimation is performed only within the first 10 ranges of a single partition; samples are not taken beyond this limit.

Configuration example

1. Create a table and insert data into it.

   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
   

2. View the execution plan. By default, storage-layer row estimation is used for partitions that do not exceed 10.

   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)
   

3. Modify the variable to select only one partition for storage layer row estimation.

   obclient [test]> SET partition_index_dive_limit = 1;
   Query OK, 0 rows affected (0.000 sec)
   

4. Examine the execution plan and note that only one partition is selected for storage layer row estimation. The estimated row count 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