分区表DQL/DML
由于分区的实现完全体现在数据库内核中,用户对分区表的DQL/DML与非分区表相比,在语法上没有任何区别。
出于分区表的易用性考虑,GaussDB支持指定分区的DQL/DML操作,指定分区可以通过PARTITION (partname)或者PARTITION FOR (partvalue)来实现,对于二级分区表还可以通过SUBPARTITION (subpartname)或者SUBPARTITION FOR (subpartvalue)指定具体的二级分区。指定分区执行DQL/DML时,若插入的数据不属于目标分区,则业务会产生报错;若查询的数据不属于目标分区,则会跳过该数据的处理。
指定分区DQL/DML支持以下几类语法:
- 查询(SELECT)
- 插入(INSERT)
- 更新(UPDATE)
- 删除(DELETE)
- 插入或更新(UPSERT)
- 合并(MERGE INTO)
指定分区做DQL/DML的示例如下:
/* 创建二级分区表 list_list_02 */ gaussdb=# CREATE TABLE IF NOT EXISTS list_list_02 ( id INT, role VARCHAR(100), data VARCHAR(100) ) PARTITION BY LIST (id) SUBPARTITION BY LIST (role) ( PARTITION p_list_2 VALUES(0,1,2,3,4,5,6,7,8,9) ( SUBPARTITION p_list_2_1 VALUES ( 0,1,2,3,4,5,6,7,8,9 ), SUBPARTITION p_list_2_2 VALUES ( DEFAULT ), SUBPARTITION p_list_2_3 VALUES ( 10,11,12,13,14,15,16,17,18,19), SUBPARTITION p_list_2_4 VALUES ( 20,21,22,23,24,25,26,27,28,29 ), SUBPARTITION p_list_2_5 VALUES ( 30,31,32,33,34,35,36,37,38,39 ) ), PARTITION p_list_3 VALUES(10,11,12,13,14,15,16,17,18,19) ( SUBPARTITION p_list_3_2 VALUES ( DEFAULT ) ), PARTITION p_list_4 VALUES( DEFAULT ), PARTITION p_list_5 VALUES(20,21,22,23,24,25,26,27,28,29) ( SUBPARTITION p_list_5_1 VALUES ( 0,1,2,3,4,5,6,7,8,9 ), SUBPARTITION p_list_5_2 VALUES ( DEFAULT ), SUBPARTITION p_list_5_3 VALUES ( 10,11,12,13,14,15,16,17,18,19), SUBPARTITION p_list_5_4 VALUES ( 20,21,22,23,24,25,26,27,28,29 ), SUBPARTITION p_list_5_5 VALUES ( 30,31,32,33,34,35,36,37,38,39 ) ), PARTITION p_list_6 VALUES(30,31,32,33,34,35,36,37,38,39), PARTITION p_list_7 VALUES(40,41,42,43,44,45,46,47,48,49) ( SUBPARTITION p_list_7_1 VALUES ( DEFAULT ) ) ) ENABLE ROW MOVEMENT; /* 导入数据 */ INSERT INTO list_list_02 VALUES(null, 'alice', 'alice data'); INSERT INTO list_list_02 VALUES(2, null, 'bob data'); INSERT INTO list_list_02 VALUES(null, null, 'peter data'); /* 对指定分区进行查询 */ -- 查询分区表全部数据 gaussdb=# SELECT * FROM list_list_02 ORDER BY data; id | role | data ----+-------+------------ | alice | alice data 2 | | bob data | | peter data (3 rows) -- 查询分区p_list_4数据 gaussdb=# SELECT * FROM list_list_02 PARTITION (p_list_4) ORDER BY data; id | role | data ----+-------+------------ | alice | alice data | | peter data (2 rows) -- 查询(100, 100)所对应的二级分区的数据,即二级分区p_list_4_subpartdefault1,这个分区是在p_list_4下自动创建的一个分区范围定义为DEFAULT的分区 gaussdb=# SELECT * FROM list_list_02 SUBPARTITION FOR(100, 100) ORDER BY data; id | role | data ----+-------+------------ | alice | alice data | | peter data (2 rows) -- 查询分区p_list_2 数据 gaussdb=# SELECT * FROM list_list_02 PARTITION (p_list_2) ORDER BY data; id | role | data ----+------+---------- 2 | | bob data (1 row) -- 查询(0, 100)所对应的二级分区的数据,即二级分区p_list_2_2 gaussdb=# SELECT * FROM list_list_02 SUBPARTITION FOR (0, 100) ORDER BY data; id | role | data ----+------+---------- 2 | | bob data (1 row) /* 对指定分区做IUD */ -- 删除分区p_list_5中的全部数据 gaussdb=# DELETE FROM list_list_02 PARTITION (p_list_5); -- 指定分区p_list_7_1插入数据,由于数据不符合该分区约束,插入报错 gaussdb=# INSERT INTO list_list_02 SUBPARTITION (p_list_7_1) VALUES(null, 'cherry', 'cherry data'); ERROR: inserted subpartition key does not map to the table subpartition -- 将一级分区值100所属分区的数据进行更新 gaussdb=# UPDATE list_list_02 PARTITION FOR (100) SET id = 1; --upsert gaussdb=# INSERT INTO list_list_02 (id, role, data) VALUES (1, 'test', 'testdata') ON DUPLICATE KEY UPDATE role = VALUES(role), data = VALUES(data); --merge into gaussdb=# CREATE TABLE IF NOT EXISTS list_tmp ( id INT, role VARCHAR(100), data VARCHAR(100) ) PARTITION BY LIST (id) ( PARTITION p_list_2 VALUES(0,1,2,3,4,5,6,7,8,9), PARTITION p_list_3 VALUES(10,11,12,13,14,15,16,17,18,19), PARTITION p_list_4 VALUES( DEFAULT ), PARTITION p_list_5 VALUES(20,21,22,23,24,25,26,27,28,29), PARTITION p_list_6 VALUES(30,31,32,33,34,35,36,37,38,39), PARTITION p_list_7 VALUES(40,41,42,43,44,45,46,47,48,49)) ENABLE ROW MOVEMENT; gaussdb=# MERGE INTO list_tmp target USING list_list_02 source ON (target.id = source.id) WHEN MATCHED THEN UPDATE SET target.data = source.data, target.role = source.role WHEN NOT MATCHED THEN INSERT (id, role, data) VALUES (source.id, source.role, source.data); gaussdb=# DROP TABLE list_tmp; DROP TABLE list_list_02;