sql分区

将学员表student按所在城市使用PARTITION BY LIST

1、创建分区表。

CREATE TABLE public.student( 
sno numeric(4,0)，                
sname character varying(20 char),gender character varying(2 char),                
phone numeric(11,0),               id no character varying(18 char),                
city character varying(20 char),                
reg_date date,
job character varying(30 char),              
company character varying(30 char)            
)PARTITION BY LIST(city);

2、创建子分区。

 CREATE TABLE public.student_p1 PARTITION OF student FOR VALUES IN ('Beijing','shanghai');CREATE TABLE public.student_p2 PARTITION OF student FOR VALUES IN ('Tianjin','Guangzhou');CREATE TABLE public.student_p3 PARTITION OF student FOR VALUES IN ('chongging','chengdu');CREATE TABLE public.student default p PARTITION OF Student DEFAULT;

3、查看分区表。

 \d+ studentSELECT partitioning_type,partition_count FROM user_part_tables WHEREtable name ="STUDENT";

4、插入测试数据、执行数据查询、查看SQL执行计划。

 INSERT INTO public.student SELECT * FROM exam.student;EXPLAIN SELECT * FROM public.student;EXPLAIN SELECT * FROM public.student WHERE city='chongqing';

将学员表student按报名时间使用PARTITION BY RANGE

1、创建 student 分区表。

 CREATE TABLE public.student(sno numeric(4,0),sname character varying(20 char),gender character varying(2 char),phone numeric(11,0)，id no character varying(18 char),city character varying(20 char),reg_date date,
job character varying(30 char),
company character varying(30 char)
)PARTITION BY RANGE(reg_date);

2、创建子分区。

 CREATE TABLE Student_p1 PARTITION OF Student FOR VALUES FROM ('2021-01-01') TO ('2021-03-31');CREATE TABLE Student_p2 PARTITION OF Student FOR VALUES FROM ('2021-04-01') TO ('2021-06-30');CREATE TABLE Student_p3 PARTITION OF Student FOR VALUES FROM ('2021-07-01') TO ('2021-09-30');CREATE TABLE Student_p4 PARTITION OF Student FOR VALUES FROM ('2021-10-01') TO ('2021-12-31');CREATE TABLE student_default_p PARTITION OF Student DEFAULT;

3、查看分区表。

\d+ student

4、插入测试数据、执行数据查询、查看SQL执行计划。

 INSERT INTO public.student SELECT * FROM exam.student;EXPLAIN SELECT * FROM public.student;EXPLAIN SELECT * FROM public.student WHERE reg_date between '2021-02-01'and '2021-02-28';

将学员表student按学员编号使用PARTITION BY HASH

1、创建分区表

CREATE TABLE public.student(
sno numeric(4,0)，
sname character varying(20 char),
gender character varying(2 char),
phone numeric(11,0),
id no character varying(18 char),
city character varying(20 char),reg_date date,job character varying(30 char),
company character varying(30 char)
)PARTITION BY HASH(sno);

2、创建子分区。

CREATE TABLE Student_p1 PARTITION OF Student FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE Student_p2 PARTITION OF Student FOR VALUES WITH (MODULUS 4, REMAINDER 1);CREATE TABLE Student_p3 PARTITION OF Student FOR VALUES WITH (MODULUS 4, REMAINDER 2);CREATE TABLE Student_p4 PARTITION OF Student FOR VALUES WITH (MODULUS 4, REMAINDER 3);

3、查看分区表。

 \d+ student

4、插入测试数据、执行数据查询、查看SQL执行计划，

 INSERT INTO public.student SELECT * FROM exam.student;EXPLAIN SELECT * FROM public.student;EXPLAIN SELECT* FROM public.student where sno=5;

通过表继承和触发器创建分区表

1、创建父表

CREATE TABLE student(sid int,name text,reg_date date not null);

2、创建子表

 CREATE TABLE student_2019(CHECK(reg_date>='2019-01-01' and reg_date<'2020-01-01'))  INHERITS(student);
CREATE TABLE student_2020(CHECK(reg_date>='2020-01-01' and reg_date<'2021-01-01')) INHERITS(student);CREATE TABLE student_2021(CHECK(reg date>='2021-01-01' and reg_date<'2022-01-01'))  INHERITS(student);

3、创建触发器函数

CREATE OR REPLACE FUNCTION fun_students_insert()RETURNS TRIGGER AS $$BEGINIF(NEW.reg_date>='2019-01-01" AND NEW.reg_date<'2020-01-01')THEN INSERT INTO student_2019 VALUES(NEW.*);ELSIF (NEW.reg_date>='2020-01-01' AND NEW.reg_date<'2021-01-01')THEN INSERT INTO studen_2020 VALUES (NEW.*);ELSE
tests#        INSERT INTO student_2021 VALUES(NEW.*);END IF:
tests#  RETURN NULL;END;$$LANGUAGE pIsql;\df fun_students_insert

4、创建触发器

 CREATE TRIGGER tri_students_insertBEFORE INSERT ON studentFOR EACH ROW EXECUTE PROCEDURE fun_students_insert();

5、插入测试数据

 INSERT INTO student VALUES( 1001, 'LiMing','2019-01-03');INSERT INTO student VALUES( 1002,'ZhaoHai','2020-05-13');INSERT INTO student VALUES( 1001,'SunQian','2021-09-20');INSERT INTO student VALUES( 1001,'LuXun','2020-4-08');INSERT INTO student VALUES( 1001,'SunWuKong','2021-8-02');

6、查询测试

 EXPLAIN SELECT * FROM student;EXPLAIN SELECT * FROM student WHERE reg_date >'2021-01-01';