kingbaseESV8分区表

骆驼祥子dba

已于 2024-03-28 23:02:22 修改

阅读量545

点赞数 4

文章标签：数据库

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

本文链接：https://blog.csdn.net/fishv668/article/details/136997306

版权

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

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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