kingbaseESV8分区表

原创已于 2024-03-28 23:02:22 修改 · 629 阅读

10 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

于 2024-03-24 22:24:57 首次发布

本文介绍了如何使用SQL创建分区表，包括范围（按年龄或日期）、列表（按城市代码）和哈希（按ID）分区。还探讨了如何使用`EXPLAIN`分析查询性能，以及如何设置`constraint_exclusion`参数以优化分区查询。

--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;