分区表测试方法

-- 创建分区表
-- RANGE分区
CREATE TABLE customer_partition_range
(c_custkey BIGINT(20),
c_name VARCHAR(25),
c_address VARCHAR(25),
c_city VARCHAR(10),
c_nation VARCHAR(15),
c_region VARCHAR(12),
c_phone VARCHAR(15),
c_mktsegment VARCHAR(10))
partition BY range(c_custkey)
(PARTITION customer_p1 VALUES LESS THAN (10000),
PARTITION customer_p2 VALUES LESS THAN (20000),
PARTITION customer_p3 VALUES LESS THAN maxvalue);
-- LIST分区
CREATE TABLE customer_partition_list
(c_custkey BIGINT(20),
c_name VARCHAR(25),
c_address VARCHAR(25),
c_city VARCHAR(10),
c_nation VARCHAR(15),
c_region VARCHAR(12),
c_phone VARCHAR(15),
c_mktsegment VARCHAR(10))
partition BY list(c_custkey)(
PARTITION customer_p1 VALUES in (1,10,100,1000,10000,100000,1000000,10000000,100000000),
PARTITION customer_p2 VALUES in (2,20,200,2000,20000,200000,2000000,20000000,200000000)
);
-- KEY分区
CREATE TABLE customer_partition_key
(c_custkey BIGINT(20),
c_name VARCHAR(25),
c_address VARCHAR(25),
c_city VARCHAR(10),
c_nation VARCHAR(15),
c_region VARCHAR(12),
c_phone VARCHAR(15),
c_mktsegment VARCHAR(10))
partition BY key(c_custkey) partitions 3;
-- HASH分区
CREATE TABLE customer_partition_hash
(c_custkey BIGINT(20),
c_name VARCHAR(25),
c_address VARCHAR(25),
c_city VARCHAR(10),
c_nation VARCHAR(15),
c_region VARCHAR(12),
c_phone VARCHAR(15),
c_mktsegment VARCHAR(10))
partition BY hash(c_custkey) partitions 3;

-- 分区管理支持：分区删除、添加分区
-- RANGE分区
ALTER TABLE customer_partition_range DROP PARTITION customer_p3;
ALTER TABLE customer_partition_range ADD PARTITION(PARTITION customer_p4 VALUES LESS THAN (30000));
ALTER TABLE customer_partition_range ADD PARTITION(PARTITION customer_p5 VALUES LESS THAN maxvalue);
-- LIST分区
ALTER TABLE customer_partition_list DROP PARTITION customer_p2;
ALTER TABLE customer_partition_list ADD PARTITION(PARTITION customer_p3 VALUES in (3,30,300,3000,30000,300000,3000000,30000000,300000000));

-- 子分区
CREATE TABLE customer_partition_rh
(c_custkey BIGINT(20),
c_name VARCHAR(25),
c_address VARCHAR(25),
c_city VARCHAR(10),
c_nation VARCHAR(15),
c_region VARCHAR(12),
c_phone VARCHAR(15),
c_mktsegment VARCHAR(10))
partition BY range(c_custkey)
SUBPARTITION BY HASH(c_custkey)
(PARTITION customer_p1 VALUES LESS THAN (10000),
PARTITION customer_p2 VALUES LESS THAN (20000),
PARTITION customer_p3 VALUES LESS THAN maxvalue)
;
CREATE TABLE customer_partition_rk
(c_custkey BIGINT(20),
c_name VARCHAR(25),
c_address VARCHAR(25),
c_city VARCHAR(10),
c_nation VARCHAR(15),
c_region VARCHAR(12),
c_phone VARCHAR(15),
c_mktsegment VARCHAR(10))
partition BY range(c_custkey)
SUBPARTITION BY key(c_name)
(PARTITION customer_p1 VALUES LESS THAN (10000),
PARTITION customer_p2 VALUES LESS THAN (20000),
PARTITION customer_p3 VALUES LESS THAN maxvalue)
;
CREATE TABLE customer_partition_lh
(c_custkey BIGINT(20),
c_name VARCHAR(25),
c_address VARCHAR(25),
c_city VARCHAR(10),
c_nation VARCHAR(15),
c_region VARCHAR(12),
c_phone VARCHAR(15),
c_mktsegment VARCHAR(10))
partition BY list(c_custkey)
SUBPARTITION BY HASH(c_custkey)
(
PARTITION customer_p1 VALUES in (1,10,100,1000,10000,100000,1000000,10000000,100000000),
PARTITION customer_p2 VALUES in (2,20,200,2000,20000,200000,2000000,20000000,200000000)
);
CREATE TABLE customer_partition_lk
(c_custkey BIGINT(20),
c_name VARCHAR(25),
c_address VARCHAR(25),
c_city VARCHAR(10),
c_nation VARCHAR(15),
c_region VARCHAR(12),
c_phone VARCHAR(15),
c_mktsegment VARCHAR(10))
partition BY list(c_custkey)
SUBPARTITION BY key(c_name)
(
PARTITION customer_p1 VALUES in (1,10,100,1000,10000,100000,1000000,10000000,100000000),
PARTITION customer_p2 VALUES in (2,20,200,2000,20000,200000,2000000,20000000,200000000)
);

-- 插入数据，验证分区后准确性
-- RANGE分区
insert into customer_partition_range values(1,2,3,4,5,6,7,8);
insert into customer_partition_range values(100,200,300,400,500,600,700,800);
insert into customer_partition_range values(10000,200,300,400,500,600,700,800);
insert into customer_partition_range values(10001,200,300,400,500,600,700,800);
insert into customer_partition_range values(20000,200,300,400,500,600,700,800);
insert into customer_partition_range values(20001,200,300,400,500,600,700,800);
insert into customer_partition_range values(1000000000,200,300,400,500,600,700,800);
insert into customer_partition_range values(9000000000,200,300,400,500,600,700,800);
-- 查询验证数据分区后的准确性
select * from customer_partition_range PARTITION (customer_p1);
select * from customer_partition_range PARTITION (customer_p2);
select * from customer_partition_range PARTITION (customer_p3);
select * from customer_partition_range PARTITION (customer_p5);

-- LIST分区
insert into customer_partition_list values(1,2,3,4,5,6,7,8);
insert into customer_partition_list values(30,20,30,40,50,60,70,80);
insert into customer_partition_list values(100,200,300,400,500,600,700,800);
insert into customer_partition_list values(10000,200,300,400,500,600,700,800);
insert into customer_partition_list values(30000,200,300,400,500,600,700,800);
insert into customer_partition_list values(100000000,200,300,400,500,600,700,800);
insert into customer_partition_list values(300000000,200,300,400,500,600,700,800);
-- 查询验证数据分区后的准确性
select * from customer_partition_list PARTITION(customer_p1);
select * from customer_partition_list PARTITION(customer_p3);