548 lines
19 KiB
Plaintext
548 lines
19 KiB
Plaintext
set @@session.tidb_partition_prune_mode = DEFAULT;
|
|
show warnings;
|
|
Level Code Message
|
|
set @@global.tidb_partition_prune_mode = DEFAULT;
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1105 Please analyze all partition tables again for consistency between partition and global stats
|
|
select @@global.tidb_partition_prune_mode;
|
|
@@global.tidb_partition_prune_mode
|
|
dynamic
|
|
select @@session.tidb_partition_prune_mode;
|
|
@@session.tidb_partition_prune_mode
|
|
dynamic
|
|
set @@session.tidb_partition_prune_mode = "static";
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1681 static prune mode is deprecated and will be removed in the future release.
|
|
set @@global.tidb_partition_prune_mode = "static";
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1681 static prune mode is deprecated and will be removed in the future release.
|
|
select @@session.tidb_partition_prune_mode;
|
|
@@session.tidb_partition_prune_mode
|
|
static
|
|
show warnings;
|
|
Level Code Message
|
|
select @@global.tidb_partition_prune_mode;
|
|
@@global.tidb_partition_prune_mode
|
|
static
|
|
set @@session.tidb_partition_prune_mode = "dynamic";
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1105 Please analyze all partition tables again for consistency between partition and global stats
|
|
Warning 1105 Please avoid setting partition prune mode to dynamic at session level and set partition prune mode to dynamic at global level
|
|
set @@global.tidb_partition_prune_mode = "dynamic";
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1105 Please analyze all partition tables again for consistency between partition and global stats
|
|
select @@global.tidb_partition_prune_mode;
|
|
@@global.tidb_partition_prune_mode
|
|
dynamic
|
|
select @@session.tidb_partition_prune_mode;
|
|
@@session.tidb_partition_prune_mode
|
|
dynamic
|
|
set @@session.tidb_partition_prune_mode = DEFAULT;
|
|
set @@global.tidb_partition_prune_mode = DEFAULT;
|
|
drop table if exists pt;
|
|
create table pt (id int, c int, key i_id(id), key i_c(c)) partition by range (c) (
|
|
partition p0 values less than (4),
|
|
partition p1 values less than (7),
|
|
partition p2 values less than (10));
|
|
analyze table pt;
|
|
insert into pt values (0, 0), (2, 2), (4, 4), (6, 6), (7, 7), (9, 9), (null, null);
|
|
select * from pt;
|
|
id c
|
|
NULL NULL
|
|
0 0
|
|
2 2
|
|
4 4
|
|
6 6
|
|
7 7
|
|
9 9
|
|
select * from pt where c > 10;
|
|
id c
|
|
select * from pt where c > 8;
|
|
id c
|
|
9 9
|
|
select * from pt where c < 2 or c >= 9;
|
|
id c
|
|
0 0
|
|
9 9
|
|
select c from pt;
|
|
c
|
|
NULL
|
|
0
|
|
2
|
|
4
|
|
6
|
|
7
|
|
9
|
|
select c from pt where c > 10;
|
|
c
|
|
select c from pt where c > 8;
|
|
c
|
|
9
|
|
select c from pt where c < 2 or c >= 9;
|
|
c
|
|
0
|
|
9
|
|
select /*+ use_index(pt, i_id) */ * from pt;
|
|
id c
|
|
NULL NULL
|
|
0 0
|
|
2 2
|
|
4 4
|
|
6 6
|
|
7 7
|
|
9 9
|
|
select /*+ use_index(pt, i_id) */ * from pt where id < 4 and c > 10;
|
|
id c
|
|
select /*+ use_index(pt, i_id) */ * from pt where id < 10 and c > 8;
|
|
id c
|
|
9 9
|
|
select /*+ use_index(pt, i_id) */ * from pt where id < 10 and c < 2 or c >= 9;
|
|
id c
|
|
0 0
|
|
9 9
|
|
set @@tidb_enable_index_merge = 1;
|
|
select /*+ use_index(i_c, i_id) */ * from pt where id = 4 or c < 7;
|
|
id c
|
|
0 0
|
|
2 2
|
|
4 4
|
|
6 6
|
|
set @@tidb_enable_index_merge = DEFAULT;
|
|
drop table if exists p, t;
|
|
create table p (id int, c int, key i_id(id), key i_c(c)) partition by range (c) (
|
|
partition p0 values less than (4),
|
|
partition p1 values less than (7),
|
|
partition p2 values less than (10));
|
|
create table t (id int);
|
|
insert into p values (3,3), (4,4), (6,6), (9,9);
|
|
insert into t values (4), (9);
|
|
select /*+ INL_JOIN(p) */ * from p, t where p.id = t.id;
|
|
id c id
|
|
4 4 4
|
|
9 9 9
|
|
select /*+ INL_JOIN(p) */ p.id from p, t where p.id = t.id;
|
|
id
|
|
4
|
|
9
|
|
drop table if exists p, t;
|
|
create table p (id int, c int, key i_id(id), key i_c(c)) partition by list (c) (
|
|
partition p0 values in (1,2,3,4),
|
|
partition p1 values in (5,6,7),
|
|
partition p2 values in (8, 9,10));
|
|
create table t (id int);
|
|
insert into p values (3,3), (4,4), (6,6), (9,9);
|
|
insert into t values (4), (9);
|
|
select /*+ INL_JOIN(p) */ * from p, t where p.id = t.id;
|
|
id c id
|
|
4 4 4
|
|
9 9 9
|
|
select /*+ INL_JOIN(p) */ p.id from p, t where p.id = t.id;
|
|
id
|
|
4
|
|
9
|
|
drop table if exists p, t;
|
|
create table p (id int, c int, key i_id(id), key i_c(c)) partition by hash(c) partitions 5;
|
|
create table t (id int);
|
|
insert into p values (3,3), (4,4), (6,6), (9,9);
|
|
insert into t values (4), (9);
|
|
select /*+ INL_JOIN(p) */ * from p, t where p.id = t.id;
|
|
id c id
|
|
4 4 4
|
|
9 9 9
|
|
select /*+ INL_JOIN(p) */ p.id from p, t where p.id = t.id;
|
|
id
|
|
4
|
|
9
|
|
drop table if exists t1, t2;
|
|
create table t1 (c_int int, c_str varchar(40), primary key (c_int)) partition by range (c_int) ( partition p0 values less than (10), partition p1 values less than maxvalue);
|
|
create table t2 (c_int int, c_str varchar(40), primary key (c_int, c_str)) partition by hash (c_int) partitions 4;
|
|
insert into t1 values (10, 'interesting neumann');
|
|
insert into t2 select * from t1;
|
|
begin;
|
|
insert into t2 values (11, 'hopeful hoover');
|
|
select /*+ INL_JOIN(t1,t2) */ * from t1 join t2 on t1.c_int = t2.c_int and t1.c_str = t2.c_str where t1.c_int in (10, 11);
|
|
c_int c_str c_int c_str
|
|
10 interesting neumann 10 interesting neumann
|
|
select /*+ INL_HASH_JOIN(t1,t2) */ * from t1 join t2 on t1.c_int = t2.c_int and t1.c_str = t2.c_str where t1.c_int in (10, 11);
|
|
c_int c_str c_int c_str
|
|
10 interesting neumann 10 interesting neumann
|
|
commit;
|
|
drop table if exists t;
|
|
create table t(c_int int);
|
|
insert into t values(1), (2), (3), (4), (5), (6), (7), (8), (9);
|
|
DROP TABLE IF EXISTS `t1`;
|
|
CREATE TABLE t1 (
|
|
c_int int NOT NULL,
|
|
c_str varchar(40) NOT NULL,
|
|
c_datetime datetime NOT NULL,
|
|
c_timestamp timestamp NULL DEFAULT NULL,
|
|
c_double double DEFAULT NULL,
|
|
c_decimal decimal(12,6) DEFAULT NULL,
|
|
PRIMARY KEY (c_int,c_str,c_datetime)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
PARTITION BY RANGE (c_int)
|
|
(PARTITION p0 VALUES LESS THAN (2) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN (4) ENGINE = InnoDB,
|
|
PARTITION p2 VALUES LESS THAN (6) ENGINE = InnoDB,
|
|
PARTITION p3 VALUES LESS THAN (8) ENGINE = InnoDB,
|
|
PARTITION p4 VALUES LESS THAN (10) ENGINE = InnoDB,
|
|
PARTITION p5 VALUES LESS THAN (20) ENGINE = InnoDB,
|
|
PARTITION p6 VALUES LESS THAN (50) ENGINE = InnoDB,
|
|
PARTITION p7 VALUES LESS THAN (1000000000) ENGINE = InnoDB);
|
|
INSERT INTO `t1` VALUES (19,'nifty feistel','2020-02-28 04:01:28','2020-02-04 06:11:57',32.430079,1.284000),(20,'objective snyder','2020-04-15 17:55:04','2020-05-30 22:04:13',37.690874,9.372000);
|
|
begin;
|
|
insert into t1 values (22, 'wizardly saha', '2020-05-03 16:35:22', '2020-05-03 02:18:42', 96.534810, 0.088);
|
|
select c_int from t where (select min(t1.c_int) from t1 where t1.c_int > t.c_int) > (select count(*) from t1 where t1.c_int > t.c_int) order by c_int;
|
|
c_int
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
6
|
|
7
|
|
8
|
|
9
|
|
rollback;
|
|
drop table if exists t1, t2;
|
|
create table t1 (c_int int, c_str varchar(40), c_decimal decimal(12, 6), primary key (c_int));
|
|
create table t2 (c_int int, c_str varchar(40), c_decimal decimal(12, 6), primary key (c_int)) partition by hash (c_int) partitions 4;
|
|
insert into t1 values (1, 'romantic robinson', 4.436), (2, 'stoic chaplygin', 9.826), (3, 'vibrant shamir', 6.300), (4, 'hungry wilson', 4.900), (5, 'naughty swartz', 9.524);
|
|
insert into t2 select * from t1;
|
|
select * from t1 where c_decimal in (select c_decimal from t2 where t1.c_int = t2.c_int or t1.c_int = t2.c_int and t1.c_str > t2.c_str);
|
|
c_int c_str c_decimal
|
|
1 romantic robinson 4.436000
|
|
2 stoic chaplygin 9.826000
|
|
3 vibrant shamir 6.300000
|
|
4 hungry wilson 4.900000
|
|
5 naughty swartz 9.524000
|
|
set @@tidb_partition_prune_mode='static';
|
|
select * from t1 where c_decimal in (select c_decimal from t2 where t1.c_int = t2.c_int or t1.c_int = t2.c_int and t1.c_str > t2.c_str);
|
|
c_int c_str c_decimal
|
|
1 romantic robinson 4.436000
|
|
2 stoic chaplygin 9.826000
|
|
3 vibrant shamir 6.300000
|
|
4 hungry wilson 4.900000
|
|
5 naughty swartz 9.524000
|
|
set @@tidb_partition_prune_mode=default;
|
|
drop table if exists coverage_rr, coverage_dt;
|
|
create table coverage_rr (
|
|
pk1 varchar(35) NOT NULL,
|
|
pk2 int NOT NULL,
|
|
c int,
|
|
PRIMARY KEY (pk1,pk2)) partition by hash(pk2) partitions 4;
|
|
create table coverage_dt (pk1 varchar(35), pk2 int);
|
|
insert into coverage_rr values ('ios', 3, 2),('android', 4, 7),('linux',5,1);
|
|
insert into coverage_dt values ('apple',3),('ios',3),('linux',5);
|
|
set @@tidb_partition_prune_mode = 'dynamic';
|
|
select /*+ INL_JOIN(dt, rr) */ * from coverage_dt dt join coverage_rr rr on (dt.pk1 = rr.pk1 and dt.pk2 = rr.pk2);
|
|
pk1 pk2 pk1 pk2 c
|
|
ios 3 ios 3 2
|
|
linux 5 linux 5 1
|
|
select /*+ INL_MERGE_JOIN(dt, rr) */ * from coverage_dt dt join coverage_rr rr on (dt.pk1 = rr.pk1 and dt.pk2 = rr.pk2);
|
|
pk1 pk2 pk1 pk2 c
|
|
ios 3 ios 3 2
|
|
linux 5 linux 5 1
|
|
set @@tidb_partition_prune_mode = default;
|
|
drop table if exists tunsigned_hash;
|
|
create table tunsigned_hash(a bigint unsigned primary key) partition by hash(a) partitions 6;
|
|
insert into tunsigned_hash values(25), (9279808998424041135);
|
|
select min(a) from tunsigned_hash;
|
|
min(a)
|
|
25
|
|
select max(a) from tunsigned_hash;
|
|
max(a)
|
|
9279808998424041135
|
|
drop table if exists t, t1;
|
|
create table t (id int not null, store_id int not null )partition by range (store_id)(partition p0 values less than (6),partition p1 values less than (11),partition p2 values less than (16),partition p3 values less than (21));
|
|
create table t1(id int not null, store_id int not null);
|
|
insert into t values (1, 1);
|
|
insert into t values (2, 17);
|
|
insert into t1 values (0, 18);
|
|
alter table t exchange partition p3 with table t1;
|
|
alter table t add index idx(id);
|
|
analyze table t;
|
|
select *,_tidb_rowid from t use index(idx) order by id limit 2;
|
|
id store_id _tidb_rowid
|
|
0 18 1
|
|
1 1 1
|
|
drop table t, t1;
|
|
create table t (a int, b int, c int, key `idx_ac`(a, c), key `idx_bc`(b, c))partition by range (b)(partition p0 values less than (6),partition p1 values less than (11),partition p2 values less than (16),partition p3 values less than (21));
|
|
create table t1 (a int, b int, c int, key `idx_ac`(a, c), key `idx_bc`(b, c));
|
|
insert into t values (1,2,3), (2,3,4), (3,4,5);
|
|
insert into t1 values (1,18,3);
|
|
alter table t exchange partition p3 with table t1;
|
|
analyze table t;
|
|
select * from t where a = 1 or b = 5 order by c limit 2;
|
|
a b c
|
|
1 18 3
|
|
1 2 3
|
|
drop table if exists t;
|
|
CREATE TABLE `t`(`a` int(11) NOT NULL,`b` int(11) DEFAULT NULL,`c` int(11) DEFAULT NULL,KEY `idx_b` (`b`)) PARTITION BY HASH (`a`) PARTITIONS 2;
|
|
insert into t values (2,-1,3), (3,2,2), (1,1,1);
|
|
select * from t use index(idx_b) order by b, _tidb_rowid limit 10;
|
|
a b c
|
|
2 -1 3
|
|
1 1 1
|
|
3 2 2
|
|
analyze table t;
|
|
select * from t use index(idx_b) order by b, _tidb_rowid limit 10;
|
|
a b c
|
|
2 -1 3
|
|
1 1 1
|
|
3 2 2
|
|
drop table if exists t;
|
|
CREATE TABLE `t`(`a` int(11) NOT NULL,`b` int(11) DEFAULT NULL,`c` int(11) DEFAULT NULL,primary key(`a`),KEY `idx_b` (`b`)) PARTITION BY HASH (`a`) PARTITIONS 2;
|
|
insert into t values (2,-1,3), (3,2,2), (1,1,1);
|
|
select * from t use index(idx_b) order by b, a limit 10;
|
|
a b c
|
|
2 -1 3
|
|
1 1 1
|
|
3 2 2
|
|
analyze table t;
|
|
select * from t use index(idx_b) order by b, a limit 10;
|
|
a b c
|
|
2 -1 3
|
|
1 1 1
|
|
3 2 2
|
|
drop table if exists t;
|
|
CREATE TABLE `t`(`a` int(11) NOT NULL,`b` int(11) DEFAULT NULL,`c` int(11) DEFAULT NULL,KEY `idx_b` (`b`),KEY `idx_c` (`c`)) PARTITION BY HASH (`a`) PARTITIONS 2;
|
|
insert into t values (2,-1,3), (3,2,2), (1,1,1);
|
|
select * from t use index(idx_b, idx_c) where b = 1 or c = 2 order by _tidb_rowid limit 10;
|
|
a b c
|
|
3 2 2
|
|
1 1 1
|
|
analyze table t;
|
|
select * from t use index(idx_b, idx_c) where b = 1 or c = 2 order by _tidb_rowid limit 10;
|
|
a b c
|
|
3 2 2
|
|
1 1 1
|
|
drop table if exists t;
|
|
CREATE TABLE `t`(`a` int(11) NOT NULL,`b` int(11) DEFAULT NULL,`c` int(11) DEFAULT NULL,KEY `idx_b` (`b`),KEY `idx_c` (`c`),PRIMARY KEY (`a`)) PARTITION BY HASH (`a`) PARTITIONS 2;
|
|
insert into t values (2,-1,3), (3,2,2), (1,1,1);
|
|
select * from t use index(idx_b, idx_c) where b = 1 or c = 2 order by a limit 10;
|
|
a b c
|
|
1 1 1
|
|
3 2 2
|
|
analyze table t;
|
|
select * from t use index(idx_b, idx_c) where b = 1 or c = 2 order by a limit 10;
|
|
a b c
|
|
1 1 1
|
|
3 2 2
|
|
drop table if exists trange, thash;
|
|
create table trange(a int, b int, primary key(a) clustered, index idx_b(b)) partition by range(a) (
|
|
partition p0 values less than(300),
|
|
partition p1 values less than(500),
|
|
partition p2 values less than(1100));
|
|
create table thash(a int, b int, primary key(a) clustered, index idx_b(b)) partition by hash(a) partitions 4;
|
|
analyze table thash, trange;
|
|
explain format='plan_tree' select * from trange where a>400;
|
|
id task access object operator info
|
|
TableReader root partition:p1,p2 data:TableRangeScan
|
|
└─TableRangeScan cop[tikv] table:trange range:(400,+inf], keep order:false, stats:pseudo
|
|
explain format='plan_tree' select * from thash where a>=100;
|
|
id task access object operator info
|
|
TableReader root partition:all data:TableRangeScan
|
|
└─TableRangeScan cop[tikv] table:thash range:[100,+inf], keep order:false, stats:pseudo
|
|
drop table if exists t;
|
|
set @@tidb_partition_prune_mode = 'dynamic';
|
|
create table t(a int) partition by range(a) (
|
|
partition p0 values less than (5),
|
|
partition p1 values less than (10),
|
|
partition p2 values less than (15));
|
|
insert into t values (2), (7), (12);
|
|
analyze table t all columns;
|
|
explain format='plan_tree' select * from t where a < 3;
|
|
id task access object operator info
|
|
TableReader root partition:p0 data:Selection
|
|
└─Selection cop[tikv] lt(executor__partition__table.t.a, 3)
|
|
└─TableFullScan cop[tikv] table:t keep order:false
|
|
select * from t where a < 3;
|
|
a
|
|
2
|
|
explain format='plan_tree' select * from t where a < 8;
|
|
id task access object operator info
|
|
TableReader root partition:p0,p1 data:Selection
|
|
└─Selection cop[tikv] lt(executor__partition__table.t.a, 8)
|
|
└─TableFullScan cop[tikv] table:t keep order:false
|
|
select * from t where a < 8;
|
|
a
|
|
2
|
|
7
|
|
explain format='plan_tree' select * from t where a < 20;
|
|
id task access object operator info
|
|
TableReader root partition:all data:Selection
|
|
└─Selection cop[tikv] lt(executor__partition__table.t.a, 20)
|
|
└─TableFullScan cop[tikv] table:t keep order:false
|
|
select * from t where a < 20;
|
|
a
|
|
12
|
|
2
|
|
7
|
|
alter table t drop partition p0;
|
|
explain format='plan_tree' select * from t where a < 3;
|
|
id task access object operator info
|
|
TableReader root partition:p1 data:Selection
|
|
└─Selection cop[tikv] lt(executor__partition__table.t.a, 3)
|
|
└─TableFullScan cop[tikv] table:t keep order:false
|
|
select * from t where a < 3;
|
|
a
|
|
explain format='plan_tree' select * from t where a < 8;
|
|
id task access object operator info
|
|
TableReader root partition:p1 data:Selection
|
|
└─Selection cop[tikv] lt(executor__partition__table.t.a, 8)
|
|
└─TableFullScan cop[tikv] table:t keep order:false
|
|
select * from t where a < 8;
|
|
a
|
|
7
|
|
explain format='plan_tree' select * from t where a < 20;
|
|
id task access object operator info
|
|
TableReader root partition:all data:Selection
|
|
└─Selection cop[tikv] lt(executor__partition__table.t.a, 20)
|
|
└─TableFullScan cop[tikv] table:t keep order:false
|
|
select * from t where a < 20;
|
|
a
|
|
12
|
|
7
|
|
alter table t add partition (partition p3 values less than (20));
|
|
alter table t add partition (partition p4 values less than (40));
|
|
insert into t values (15), (25);
|
|
explain format='plan_tree' select * from t where a < 3;
|
|
id task access object operator info
|
|
TableReader root partition:p1 data:Selection
|
|
└─Selection cop[tikv] lt(executor__partition__table.t.a, 3)
|
|
└─TableFullScan cop[tikv] table:t keep order:false
|
|
select * from t where a < 3;
|
|
a
|
|
explain format='plan_tree' select * from t where a < 8;
|
|
id task access object operator info
|
|
TableReader root partition:p1 data:Selection
|
|
└─Selection cop[tikv] lt(executor__partition__table.t.a, 8)
|
|
└─TableFullScan cop[tikv] table:t keep order:false
|
|
select * from t where a < 8;
|
|
a
|
|
7
|
|
explain format='plan_tree' select * from t where a < 20;
|
|
id task access object operator info
|
|
TableReader root partition:p1,p2,p3 data:Selection
|
|
└─Selection cop[tikv] lt(executor__partition__table.t.a, 20)
|
|
└─TableFullScan cop[tikv] table:t keep order:false
|
|
select * from t where a < 20;
|
|
a
|
|
12
|
|
15
|
|
7
|
|
drop table if exists t;
|
|
create table t(a int, b int) partition by range(a) (partition p0 values less than(3), partition p1 values less than (5), partition p2 values less than(11));
|
|
analyze table t;
|
|
set @@tidb_partition_prune_mode = 'static';
|
|
begin;
|
|
explain format='plan_tree' select * from t;
|
|
id task access object operator info
|
|
PartitionUnion root
|
|
├─TableReader root data:TableFullScan
|
|
│ └─TableFullScan cop[tikv] table:t, partition:p0 keep order:false, stats:pseudo
|
|
├─TableReader root data:TableFullScan
|
|
│ └─TableFullScan cop[tikv] table:t, partition:p1 keep order:false, stats:pseudo
|
|
└─TableReader root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t, partition:p2 keep order:false, stats:pseudo
|
|
select * from t;
|
|
a b
|
|
explain format='plan_tree' select * from t where a > 3;
|
|
id task access object operator info
|
|
PartitionUnion root
|
|
├─TableReader root data:Selection
|
|
│ └─Selection cop[tikv] gt(executor__partition__table.t.a, 3)
|
|
│ └─TableFullScan cop[tikv] table:t, partition:p1 keep order:false, stats:pseudo
|
|
└─TableReader root data:Selection
|
|
└─Selection cop[tikv] gt(executor__partition__table.t.a, 3)
|
|
└─TableFullScan cop[tikv] table:t, partition:p2 keep order:false, stats:pseudo
|
|
select * from t where a > 3;
|
|
a b
|
|
explain format='plan_tree' select * from t where a > 7;
|
|
id task access object operator info
|
|
TableReader root data:Selection
|
|
└─Selection cop[tikv] gt(executor__partition__table.t.a, 7)
|
|
└─TableFullScan cop[tikv] table:t, partition:p2 keep order:false, stats:pseudo
|
|
select * from t where a > 7;
|
|
a b
|
|
rollback;
|
|
set @@tidb_partition_prune_mode = 'dynamic';
|
|
begin;
|
|
explain format='plan_tree' select * from t;
|
|
id task access object operator info
|
|
TableReader root partition:all data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t keep order:false, stats:pseudo
|
|
select * from t;
|
|
a b
|
|
explain format='plan_tree' select * from t where a > 3;
|
|
id task access object operator info
|
|
TableReader root partition:p1,p2 data:Selection
|
|
└─Selection cop[tikv] gt(executor__partition__table.t.a, 3)
|
|
└─TableFullScan cop[tikv] table:t keep order:false, stats:pseudo
|
|
select * from t where a > 3;
|
|
a b
|
|
explain format='plan_tree' select * from t where a > 7;
|
|
id task access object operator info
|
|
TableReader root partition:p2 data:Selection
|
|
└─Selection cop[tikv] gt(executor__partition__table.t.a, 7)
|
|
└─TableFullScan cop[tikv] table:t keep order:false, stats:pseudo
|
|
select * from t where a > 7;
|
|
a b
|
|
rollback;
|
|
set @@tidb_partition_prune_mode = default;
|
|
drop table if exists tt1, tt2;
|
|
set global tidb_partition_prune_mode='dynamic';
|
|
set session tidb_partition_prune_mode='dynamic';
|
|
CREATE TABLE tt1 (
|
|
id INT NOT NULL,
|
|
listid INT,
|
|
name varchar(10),
|
|
primary key (listid) clustered
|
|
)
|
|
PARTITION BY LIST (listid) (
|
|
PARTITION p1 VALUES IN (1),
|
|
PARTITION p2 VALUES IN (2),
|
|
PARTITION p3 VALUES IN (3),
|
|
PARTITION p4 VALUES IN (4)
|
|
);
|
|
CREATE TABLE tt2 (
|
|
id INT NOT NULL,
|
|
listid INT
|
|
);
|
|
create index idx_listid on tt1(id,listid);
|
|
create index idx_listid on tt2(listid);
|
|
insert into tt1 values(1,1,1);
|
|
insert into tt1 values(2,2,2);
|
|
insert into tt1 values(3,3,3);
|
|
insert into tt1 values(4,4,4);
|
|
insert into tt2 values(1,1);
|
|
insert into tt2 values(2,2);
|
|
insert into tt2 values(3,3);
|
|
insert into tt2 values(4,4);
|
|
insert into tt2 values(5,5);
|
|
analyze table tt1;
|
|
analyze table tt2;
|
|
select /*+ inl_join(tt1)*/ count(*) from tt2
|
|
left join tt1 on tt1.listid=tt2.listid and tt1.id=tt2.id;
|
|
count(*)
|
|
5
|
|
select /*+ inl_join(tt1)*/ count(*) from tt2
|
|
left join tt1 on tt1.listid=tt2.listid;
|
|
count(*)
|
|
5
|
|
explain format = 'plan_tree' select /*+ inl_join(tt1)*/ count(*) from tt2
|
|
left join tt1 on tt1.listid=tt2.listid;
|
|
id task access object operator info
|
|
StreamAgg root funcs:count(Column)->Column
|
|
└─IndexReader root index:StreamAgg
|
|
└─StreamAgg cop[tikv] funcs:count(1)->Column
|
|
└─IndexFullScan cop[tikv] table:tt2, index:idx_listid(listid) keep order:false
|
|
set global tidb_partition_prune_mode=default;
|
|
set session tidb_partition_prune_mode=default;
|