# Switching from Static to Dynamic Mode + When transitioning from `static` mode to `dynamic` mode, it is necessary to manually check and collect statistics. ``` tidb> SET @@tidb_partition_prune_mode = 'dynamic'; Query OK, 0 rows affected, 2 warnings (0.00 sec) tidb> SHOW STATS_META WHERE table_name LIKE 'User'; +---------+------------+----------------+---------------------+--------------+-----------+ | Db_name | Table_name | Partition_name | Update_time | Modify_count | Row_count | +---------+------------+----------------+---------------------+--------------+-----------+ | test | User | global | 2023-10-03 16:03:39 | 0 | 0 | | test | User | p0 | 2023-10-03 16:03:39 | 0 | 0 | | test | User | p1 | 2023-10-03 16:03:39 | 0 | 0 | | test | User | p2 | 2023-10-03 16:03:39 | 0 | 0 | +---------+------------+----------------+---------------------+--------------+-----------+ 4 rows in set (0.00 sec) tidb> EXPLAIN SELECT * FROM User WHERE city IN ('Seattle', 'Tokyo') AND login BETWEEN '2023-09-01' AND '2024-11-30'; +----------------------+----------+-----------+--------------------------+---------------------------------------------------------------------...-+ | id | estRows | task | access object | operator info ... | +----------------------+----------+-----------+--------------------------+---------------------------------------------------------------------...-+ | PartitionUnion_8 | 1.00 | root | | ... | | ├─TableReader_11 | 0.50 | root | | data:Selection_10 ... | | │ └─Selection_10 | 0.50 | cop[tikv] | | ge(test.user.login, 2023-09-01 00:00:00.000000), in(test.user.city, ... | | │ └─TableFullScan_9 | 10000.00 | cop[tikv] | table:User, partition:p1 | keep order:false, stats:pseudo ... | | └─TableReader_14 | 0.50 | root | | data:Selection_13 ... | | └─Selection_13 | 0.50 | cop[tikv] | | ge(test.user.login, 2023-09-01 00:00:00.000000), in(test.user.city, ... | | └─TableFullScan_12 | 10000.00 | cop[tikv] | table:User, partition:p2 | keep order:false, stats:pseudo ... | | +----------------------+----------+-----------+--------------------------+---------------------------------------------------------------------...-+ 7 rows in set, 1 warning (0.00 sec) ``` ------------------------------------------------------------------------------ When transitioning from `static` mode to `dynamic` mode, it is necessary to manually check and collect statistics. This requirement arises because, upon switching to `dynamic` mode, partitioned tables initially possess only partition-level statistics and lack comprehensive table-level statistics. The `GlobalStats` are collected only during the subsequent auto-analyze operation. In the slide, it is evident that upon switching from static to `dynamic` mode, the `GlobalStats` (full table-level statistics) appear in the first row of the output from the command `SHOW STATS_META WHERE table_name LIKE 'User';`. However, since `dynamic` mode has not yet taken effect, the execution plan still includes the `PartitionUnion` operation, as demonstrated below: ``` tidb> SET @@tidb_partition_prune_mode = 'dynamic'; Query OK, 0 rows affected (0.00 sec) tidb> SHOW STATS_META WHERE table_name LIKE 'User'; +---------+------------+----------------+---------------------+--------------+-----------+ | Db_name | Table_name | Partition_name | Update_time | Modify_count | Row_count | +---------+------------+----------------+---------------------+--------------+-----------+ | test | User | global | 2023-10-03 16:03:39 | 0 | 0 | | test | User | p0 | 2023-10-03 16:03:39 | 0 | 0 | | test | User | p1 | 2023-10-03 16:03:39 | 0 | 0 | | test | User | p2 | 2023-10-03 16:03:39 | 0 | 0 | +---------+------------+----------------+---------------------+--------------+-----------+ 4 rows in set (0.01 sec) tidb> EXPLAIN SELECT * FROM User WHERE city IN ('Seattle', 'Tokyo') AND login BETWEEN '2023-09-01' AND '2024-11-30'; +----------------------+----------+-----------+--------------------------+------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +----------------------+----------+-----------+--------------------------+------------------------------------------------------------------------------------------------------------------------------------------+ | PartitionUnion_8 | 1.00 | root | | | | ├─TableReader_11 | 0.50 | root | | data:Selection_10 | | │ └─Selection_10 | 0.50 | cop[tikv] | | ge(test.user.login, 2023-09-01 00:00:00.000000), in(test.user.city, "Seattle", "Tokyo"), le(test.user.login, 2024-11-30 00:00:00.000000) | | │ └─TableFullScan_9 | 10000.00 | cop[tikv] | table:User, partition:p1 | keep order:false, stats:pseudo | | └─TableReader_14 | 0.50 | root | | data:Selection_13 | | └─Selection_13 | 0.50 | cop[tikv] | | ge(test.user.login, 2023-09-01 00:00:00.000000), in(test.user.city, "Seattle", "Tokyo"), le(test.user.login, 2024-11-3000:00:00.000000) | | └─TableFullScan_12 | 10000.00 | cop[tikv] | table:User, partition:p2 | keep order:false, stats:pseudo | +----------------------+----------+-----------+--------------------------+------------------------------------------------------------------------------------------------------------------------------------------+ 7 rows in set, 1 warning (0.00 sec) ```