# `VERSION::6.5.0` The Dynamic and Static Modes + **`tidb_partition_prune_mode`** + **`static`**: TiDB accesses each partition separately using multiple operators, and then merges the results using `Union`. + **`dynamic`** (default): Each operator supports direct access to multiple partitions, so TiDB no longer uses `Union`. Also allows execution plans with IndexJoin which cannot be used in `static` mode. ------------------------------------------------------------------------------ TiDB accesses partitioned tables using either `dynamic` or `static` modes, with `dynamic` mode being the default setting since version 6.3.0. In `static` mode, TiDB accesses each partition individually through multiple operators and subsequently combines the results using `Union`. The following example illustrates a straightforward read operation in which TiDB consolidates the results of two corresponding partitions through `Union`: ``` tidb> CREATE TABLE User ( -> fname VARCHAR(50) NOT NULL, -> lname VARCHAR(50) NOT NULL, -> city VARCHAR(50) NOT NULL, -> login DATE NOT NULL -> ) -> -> PARTITION BY RANGE COLUMNS (login) -> ( -> PARTITION `p0` VALUES LESS THAN ('2023-01-01 00:00:00'), -> PARTITION `p1` VALUES LESS THAN ('2024-01-01 00:00:00'), -> PARTITION `p2` VALUES LESS THAN ('2025-01-01 00:00:00') -> ); Query OK, 0 rows affected (0.53 sec) tidb> SET SESSION tidb_partition_prune_mode = static; Query OK, 0 rows affected (0.00 sec) tidb> EXPLAIN SELECT * FROM User WHERE city IN ('Seattle', 'Tokyo') AND login > '2023-09-11'; +----------------------+----------+-----------+--------------------------+-----------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +----------------------+----------+-----------+--------------------------+-----------------------------------------------------------------------------------------+ | PartitionUnion_8 | 13.33 | root | | | | ├─TableReader_11 | 6.67 | root | | data:Selection_10 | | │ └─Selection_10 | 6.67 | cop[tikv] | | gt(test.user.login, 2023-09-11 00:00:00.000000), in(test.user.city, "Seattle", "Tokyo") | | │ └─TableFullScan_9 | 10000.00 | cop[tikv] | table:User, partition:p1 | keep order:false, stats:pseudo | | └─TableReader_14 | 6.67 | root | | data:Selection_13 | | └─Selection_13 | 6.67 | cop[tikv] | | gt(test.user.login, 2023-09-11 00:00:00.000000), in(test.user.city, "Seattle", "Tokyo") | | └─TableFullScan_12 | 10000.00 | cop[tikv] | table:User, partition:p2 | keep order:false, stats:pseudo | +----------------------+----------+-----------+--------------------------+-----------------------------------------------------------------------------------------+ 7 rows in set (0.00 sec) ``` In `dynamic` mode, each operator is capable of directly accessing multiple partitions, eliminating the need for TiDB to use `Union`. ``` tidb> SET @@session.tidb_partition_prune_mode = 'dynamic'; Query OK, 0 rows affected, 2 warnings (0.00 sec) tidb> EXPLAIN SELECT * FROM User WHERE city IN ('Seattle', 'Tokyo') AND login > '2023-09-11'; +--------------------+----------+-----------+-----------------+-----------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +--------------------+----------+-----------+-----------------+-----------------------------------------------------------------------------------------+ | TableReader_7 | 6.67 | root | partition:p1,p2 | data:Selection_6 | | └─Selection_6 | 6.67 | cop[tikv] | | gt(test.user.login, 2023-09-11 00:00:00.000000), in(test.user.city, "Seattle", "Tokyo") | | └─TableFullScan_5 | 10000.00 | cop[tikv] | table:User | keep order:false, stats:pseudo | +--------------------+----------+-----------+-----------------+-----------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) ``` From the query results above, it is evident that the `Union` operator is absent from the execution plan, yet partition pruning remains effective, with the execution plan accessing only partitions p1 and p2. The `dynamic` mode simplifies and clarifies execution plans. By omitting the `Union` operation, it enhances execution efficiency and circumvents issues related to the concurrent execution of `Union`. Furthermore, `dynamic` mode supports execution plans involving `IndexJoin`, which are not feasible in static mode (refer to the examples below). ``` tidb> CREATE TABLE emp (id INT, name VARCHAR(32), KEY(id)) PARTITION BY RANGE(id)( -> PARTITION p0 VALUES LESS THAN (200), -> PARTITION p1 VALUES LESS THAN (400), -> PARTITION p2 VALUES LESS THAN (600), -> PARTITION p3 VALUES LESS THAN (800), -> PARTITION p4 VALUES LESS THAN (1000)); Query OK, 0 rows affected (0.53 sec) tidb> CREATE TABLE mgr(id INT, title VARCHAR(32)); Query OK, 0 rows affected (0.53 sec) tidb> SET @@tidb_partition_prune_mode = 'static'; Query OK, 0 rows affected (0.00 sec) tidb> EXPLAIN SELECT /*+ TIDB_INLJ(emp, mgr) */ emp.* FROM emp, mgr WHERE mgr.title = 'Director' AND mgr.id = emp.id; +----------------------------+----------+-----------+-------------------------+----------------------------------------------------------+ | id | estRows | task | access object | operator info | +----------------------------+----------+-----------+-------------------------+----------------------------------------------------------+ | HashJoin_18 | 12.49 | root | | inner join, equal:[eq(test.mgr.id, test.emp.id)] | | ├─TableReader_21(Build) | 9.99 | root | | data:Selection_20 | | │ └─Selection_20 | 9.99 | cop[tikv] | | eq(test.mgr.title, "Director"), not(isnull(test.mgr.id)) | | │ └─TableFullScan_19 | 10000.00 | cop[tikv] | table:mgr | keep order:false, stats:pseudo | | └─PartitionUnion_22(Probe) | 49950.00 | root | | | | ├─TableReader_25 | 9990.00 | root | | data:Selection_24 | | │ └─Selection_24 | 9990.00 | cop[tikv] | | not(isnull(test.emp.id)) | | │ └─TableFullScan_23 | 10000.00 | cop[tikv] | table:emp, partition:p0 | keep order:false, stats:pseudo | | ├─TableReader_31 | 9990.00 | root | | data:Selection_30 | | │ └─Selection_30 | 9990.00 | cop[tikv] | | not(isnull(test.emp.id)) | | │ └─TableFullScan_29 | 10000.00 | cop[tikv] | table:emp, partition:p1 | keep order:false, stats:pseudo | | ├─TableReader_37 | 9990.00 | root | | data:Selection_36 | | │ └─Selection_36 | 9990.00 | cop[tikv] | | not(isnull(test.emp.id)) | | │ └─TableFullScan_35 | 10000.00 | cop[tikv] | table:emp, partition:p2 | keep order:false, stats:pseudo | | ├─TableReader_43 | 9990.00 | root | | data:Selection_42 | | │ └─Selection_42 | 9990.00 | cop[tikv] | | not(isnull(test.emp.id)) | | │ └─TableFullScan_41 | 10000.00 | cop[tikv] | table:emp, partition:p3 | keep order:false, stats:pseudo | | └─TableReader_49 | 9990.00 | root | | data:Selection_48 | | └─Selection_48 | 9990.00 | cop[tikv] | | not(isnull(test.emp.id)) | | └─TableFullScan_47 | 10000.00 | cop[tikv] | table:emp, partition:p4 | keep order:false, stats:pseudo | +----------------------------+----------+-----------+-------------------------+----------------------------------------------------------+ 20 rows in set, 1 warning (0.00 sec) tidb> SHOW WARNINGS +---------+------+----------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------+ | Warning | 1815 | Optimizer Hint /*+ INL_JOIN(emp, mgr) */ or /*+ TIDB_INLJ(emp, mgr) */ is inapplicable | +---------+------+----------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ``` As observed, even when the `TIDB_INLJ` hint is applied, the execution plan with `IndexJoin` cannot be selected for queries on the partitioned table. ``` tidb> SET @@tidb_partition_prune_mode = 'dynamic'; Query OK, 0 rows affected (0.00 sec) tidb> EXPLAIN SELECT /*+ TIDB_INLJ(emp, mgr) */ emp.* FROM emp, mgr WHERE mgr.title = 'Director' AND mgr.id = emp.id; +-----------------------------+----------+-----------+-------------------------+-------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +-----------------------------+----------+-----------+-------------------------+-------------------------------------------------------------------------------------------------------------------------+ | IndexJoin_14 | 12.49 | root | | inner join, inner:IndexLookUp_13, outer key:test.mgr.id, inner key:test.emp.id, equal cond:eq(test.mgr.id, test.emp.id) | | ├─TableReader_19(Build) | 9.99 | root | | data:Selection_18 | | │ └─Selection_18 | 9.99 | cop[tikv] | | eq(test.mgr.title, "Director"), not(isnull(test.mgr.id)) | | │ └─TableFullScan_17 | 10000.00 | cop[tikv] | table:mgr | keep order:false, stats:pseudo | | └─IndexLookUp_13(Probe) | 12.49 | root | partition:all | | | ├─Selection_12(Build) | 12.49 | cop[tikv] | | not(isnull(test.emp.id)) | | │ └─IndexRangeScan_10 | 12.50 | cop[tikv] | table:emp, index:id(id) | range: decided by [eq(test.emp.id, test.mgr.id)], keep order:false, stats:pseudo | | └─TableRowIDScan_11(Probe) | 12.49 | cop[tikv] | table:emp | keep order:false, stats:pseudo | +-----------------------------+----------+-----------+-------------------------+-------------------------------------------------------------------------------------------------------------------------+ 8 rows in set (0.00 sec) ``` You can see that in `dynamic` mode, the execution plan incorporating `IndexJoin` is selected when executing the query.