--partition by range
CREATE TABLE t01 (
age int not null,
city varchar not null
) PARTITION BY RANGE (age);
create table t01_r1 partition of t01 for values from (MINVALUE) to (10);
create table t01_r2 partition of t01 for values from (11) to (20);
create table t01_r3 partition of t01 for values from (21) to (30);
create table t01_r4 partition of t01 for values from (31) to (MAXVALUE);
create table public.student(
id int,
citycode char(6),
startdate timestamp
)
partition by range(startdate);
create table public.student_p1 partition of student for values from ('2007-01-01') to ('2007-03-31');
create table public.student_p2 partition of student for values from ('2007-04-01') to ('2007-06-30');
create table public.student_p3 partition of student default;
--partition by list
create table public.student(
id int,
citycode char(6)
)
partition by list (citycode);
create table public.student_p1 partition of student for values in ('110000');
create table public.student_p2 partition of student for values in ('120000');
create table public.student_p3 partition of student for values in ('130000');
create table public.student_p4 partition of student default;
--partition by hash
create table public.student(
id int,
citycode char(6),
startdate timestamp
)
partition by hash(id);
create table public.student_p1 partition of student for values with(modulus 4,remainder 0);
create table public.student_p2 partition of student for values with(modulus 4,remainder 1);
create table public.student_p3 partition of student for values with(modulus 4,remainder 2);
create table public.student_p4 partition of student for values with(modulus 4,remainder 3);
explain select * from student where id=1;
--修改参数
show constraint_exclusion;
set constraint_exclusion=partition;