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+ student
 SELECT partitioning_type,partition_count FROM user_part_tables WHERE
 table 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 $$
 BEGIN
    IF(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_insert
   BEFORE INSERT ON student
   FOR 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';