【技术分享】Gauss DB 分区表知多少

发表时间：2023-09-21 16:50

常见的分区方案有范围分区（ Range Partitioning）、间隔分区（ Interval Partitioning）、哈希分区（ Hash Partitioning）、列表分区（ List Partitioning）、数值分区（ Value Partition）等。

目前行存表支持范围分区、哈希分区、列表分区，列存表仅支持范围分区。

一、分区表创建

1.范围分区

范围分区是生产系统中最常见的分区类型，通常在以时间维度（ Date、 Time Stamp）描述数据场景中使用。

less then语法

对于从句是VALUE LESS THAN的语法格式，范围分区策略的分区键最多支持16列

创建表空间及schem

\c testdb1 lsq CREATE TABLESPACE tbs01 RELATIVE LOCATION 'tablespace/tbs01'; CREATE TABLESPACE tbs02 RELATIVE LOCATION 'tablespace/tbs02'; CREATE TABLESPACE tbs03 RELATIVE LOCATION 'tablespace/tbs03'; -- 创建临时schema。 select current_schema; CREATE SCHEMA lvs; SET CURRENT_SCHEMA TO lvs;

创建分区表

CREATE TABLE pt01(id INTEGER ,name varchar2(100) )PARTITION BY RANGE(id)(PARTITION P1 VALUES LESS THAN(10),PARTITION P2 VALUES LESS THAN(20),PARTITION P3 VALUES LESS THAN(30),PARTITION P4 VALUES LESS THAN(40),PARTITION P5 VALUES LESS THAN(50),PARTITION P6 VALUES LESS THAN(60),PARTITION P7 VALUES LESS THAN(70),PARTITION P8 VALUES LESS THAN(MAXVALUE))ENABLE ROW MOVEMENT;

start end语法

对于从句是START END的语法格式，范围分区策略的分区键仅支持1列。

可实现类似间隔分区的效果。

CREATE TABLE startend_pt (c1 INT, c2 INT)TABLESPACE tbs01PARTITION BY RANGE (c2) (PARTITION p1 START(1) END(1000) EVERY(200) TABLESPACE tbs02,PARTITION p2 END(2000),PARTITION p3 START(2000) END(2500) TABLESPACE tbs03,PARTITION p4 START(2500),PARTITION p5 START(3000) END(5000) EVERY(1000) TABLESPACE tbs03)ENABLE ROW MOVEMENT;select relname,parttype,parentid,partstrategy,boundaries from pg_partition where parentid in (SELECT parentid FROM pg_partition where relname='startend_pt');

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240723153024068.png$

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240723153156526.png$

2.哈希分区

哈希分区策略的分区键仅支持1列。

CREATE TABLE pt02(id INTEGER ,name varchar2(100) )PARTITION BY HASH(id)(PARTITION P1,PARTITION P2,PARTITION P3,PARTITION P4,PARTITION P5,PARTITION P6,PARTITION P7,PARTITION P8);SELECT pg_get_tabledef('pt02');select relname,parttype,parentid,partstrategy,boundaries from pg_partition where parentid in (SELECT parentid FROM pg_partition where relname='pt02');

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240723161055709.png$

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240723161401659.png$

3.列表分区

列表分区的优势在于可以以枚举分区值方式对数据进行分区，可以对无序和不相关的数据集进行分组和组织。对于未定义在列表中的分区键值，可以使用默认分区（ DEFAULT）来进行数据的保存，这样所有未映射到任何其他分区的行都不会生成错误。

列表分区策略的分区键最多支持16列。

CREATE TABLE pt03(id INTEGER NOT NULL,name CHAR(24))PARTITION BY LIST(id)(PARTITION p0 VALUES (1,4,7),PARTITION p1 VALUES (2,5,8),PARTITION p2 VALUES (3,6,9),PARTITION p3 VALUES (DEFAULT));插入数据：DECLARE v_id int; v_name VARCHAR2(30);BEGIN for i in 1..12 loop v_id := i; v_name := 'name'||i; INSERT into pt03(id,name) VALUES (v_id,v_name); commit; END LOOP;END;/SELECT pg_get_tabledef('pt03');select relname,parttype,parentid,partstrategy,boundaries from pg_partition where parentid in (SELECT parentid FROM pg_partition where relname='pt03');

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240723162043799.png$

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240723162102658.png$

3.创建二级分区表

CREATE TABLE t1_sub_rr (c1 INT,c2 INT,c3 INT)PARTITION BY RANGE (c1)SUBPARTITION BY LIST (c2)(PARTITION p_2021 VALUES LESS THAN (2022) (SUBPARTITION p_2021_1 VALUES (1),SUBPARTITION p_2021_2 VALUES (2),SUBPARTITION p_2021_3 VALUES (3)),PARTITION p_2022 VALUES LESS THAN (2023) (SUBPARTITION p_2022_1 VALUES (1),SUBPARTITION p_2022_2 VALUES (2),SUBPARTITION p_2022_3 VALUES (3)),PARTITION p_2023 VALUES LESS THAN (2024) (SUBPARTITION p_2023_1 VALUES (1),SUBPARTITION p_2023_2 VALUES (2),SUBPARTITION p_2023_3 VALUES (3)),PARTITION p_2024 VALUES LESS THAN (2025) (SUBPARTITION p_2024_1 VALUES (1),SUBPARTITION p_2024_2 VALUES (2),SUBPARTITION p_2024_3 VALUES (3)),PARTITION p_2025 VALUES LESS THAN (2026) (SUBPARTITION p_2025_1 VALUES (1),SUBPARTITION p_2025_2 VALUES (2),SUBPARTITION p_2025_3 VALUES (3)),PARTITION p_2026 VALUES LESS THAN (2027) (SUBPARTITION p_2026_1 VALUES (1),SUBPARTITION p_2026_2 VALUES (2),SUBPARTITION p_2026_3 VALUES (3)));

二、分区表运维管理

1.插入数据

DECLARE v_id int; v_name VARCHAR2(30);BEGIN for i in 1..70 loop v_id := i; v_name := 'name'||i; INSERT into pt01(id,name) VALUES (v_id,v_name); commit; END LOOP;END;/

2.查看分区表定义

SELECT pg_get_tabledef('pt02');

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240723141059984.png$

3.查看表分区信息

select relname,parttype,parentid,partstrategy,boundaries from pg_partition where parentid in (SELECT parentid FROM pg_partition where relname='pt01');

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240723141242664.png$

4.查看分区数据

select * from pt01 partition (p1) order by 1;或：select * from pt01 partition for (2) order by 1;

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240723141444960.png$

5.清空分区数据

ALTER TABLE pt01 truncate partition p1;

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240723141541338.png$

6.删除分区

ALTER TABLE pt01 DROP PARTITION P1;

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240723141708111.png$

7.增加分区

ALTER TABLE pt01 ADD PARTITION P10 VALUES LESS THAN (100);

此时，增加分区边界上线（100）在现有分区边界范围内（maxvalue）。报错如下：

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240723141808147.png$

可以改为split最后一个分区来实现增加分区。参见：“分区的分割”

8.分区的分割

范围分区

ALTER TABLE pt01 SPLIT PARTITION P9 AT (65) INTO (partition p12,partition p13) UPDATE GLOBAL INDEX;

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240724142008935.png$

列表分区

CREATE TABLE pt03(id INTEGER NOT NULL,name CHAR(24))PARTITION BY LIST(id)(PARTITION p0 VALUES (1,4,7),PARTITION p1 VALUES (2,5,8),PARTITION p2 VALUES (3,6,9),PARTITION p3 VALUES (DEFAULT));插入数据：DECLARE v_id int; v_name VARCHAR2(30);BEGIN for i in 1..12 loop v_id := i; v_name := 'name'||i; INSERT into pt03(id,name) VALUES (v_id,v_name); commit; END LOOP;END;/ALTER TABLE pt03 SPLIT PARTITION p2 INTO(PARTITION p4 VALUES (3,6),PARTITION p5 VALUES (9))UPDATE GLOBAL INDEX;

9.分区的合并

ALTER TABLE pt01 MERGE PARTITIONS P4,P5 INTO PARTITION P11;

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240723142607596.png$

10.分区改名

ALTER TABLE pt01 RENAME PARTITION P7 TO P9;

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240723142736039.png$

11.分区更改表空间

select pg_get_tabledef('pt01');ALTER TABLE pt01 MOVE PARTITION P2 TABLESPACE tbs01;select pg_get_tabledef('pt01');

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240723142850391.png$

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240723143034057.png$

12.交换分区

用户可以使用交换分区的命令来将分区与普通表的数据进行交换。交换分区可以快速将数据导入/导出分区表，实现数据高效加载的目的。在业务迁移的场景，使用交换分区比常规导入会快很多。交换分区可以通过指定分区名或者分区值来进行。

如果交换的数据不完全属于目标分区，请不要申明WITHOUT VALIDATION交换分区，否则会破坏分区约束规则，导致分区表后续DML业务结果异常。

可以申明WITH VALIDATION VERBOSE，此时数据库会校验普通表的每一行，将不满足目标分区的分区键约束规则的数据，插入到分区表的其他分区中，最后再进行普通表与目标分区的交换。

执行交换分区命令会使得Global索引失效，可以通过UPDATE GLOBAL INDEX子句来同步更新Global索引，或者用户自行重建Global索引。

进行交换的普通表和分区必须满足如下条件：● 普通表和分区的列数目相同，对应列的信息严格一致。● 普通表和分区的表压缩信息严格一致。● 普通表索引和分区Local索引个数相同，且对应索引的信息严格一致。● 普通表和分区的表约束个数相同，且对应表约束的信息严格一致。● 普通表不可以是临时表。● 普通表和分区表上不可以有动态数据脱敏，行访问控制约束。

CREATE TABLE range_sales ( id INT4 NOT NULL, time DATE ) PARTITION BY RANGE (time) ( PARTITION date_202001 VALUES LESS THAN ('2020-02-01'), PARTITION date_202002 VALUES LESS THAN ('2020-03-01'), PARTITION date_202003 VALUES LESS THAN ('2020-04-01'), PARTITION date_202004 VALUES LESS THAN ('2020-05-01') ) ; CREATE TABLE exchange_sales ( id INT4 NOT NULL, time DATE ) ; insert into exchange_sales values(1,'2020-01-01'); insert into exchange_sales values(2,'2020-01-03'); insert into exchange_sales values(3,'2020-01-05'); insert into exchange_sales values(4,'2020-01-07'); insert into exchange_sales values(5,'2020-02-03'); insert into exchange_sales values(6,'2020-04-08'); 将分区DATE_202001和普通表exchange_sales做交换： ALTER TABLE range_sales EXCHANGE PARTITION FOR ('2020-01-08') WITH TABLE exchange_sales WITH VALIDATION VERBOSE;

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240724135710587.png$

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240724142753855.png$

13.分区表行迁移

用户可以使用ALTER TABLE ENABLE/DISABLE ROW MOVEMENT来开启/关闭分区表行迁移。

开启行迁移时，允许通过更新操作将一个分区中的数据迁移到另一个分区中；关闭行迁移时，如果出现这种更新行为，则业务报错。

如果业务明确不允许对分区键所在列进行更新操作，建议关闭分区表行迁移。

CREATE TABLE list_sales ( product_id INT4 NOT NULL, channel_id CHAR(1) ) PARTITION BY LIST (channel_id) ( PARTITION channel1 VALUES ('0', '1', '2'), PARTITION channel2 VALUES ('3', '4', '5'), PARTITION channel3 VALUES ('6', '7'), PARTITION channel4 VALUES ('8', '9') ) ENABLE ROW MOVEMENT; INSERT INTO list_sales VALUES (1,'0'); --跨分区更新成功，数据从分区channel1迁移到分区channel2 UPDATE list_sales SET channel_id = '3' WHERE channel_id = '0'; --关闭分区表行迁移 ALTER TABLE list_sales DISABLE ROW MOVEMENT; --跨分区更新失败，报错fail to update partitioned table "list_sales" UPDATE list_sales SET channel_id = '0' WHERE channel_id = '3'; --分区内更新依然成功 UPDATE list_sales SET channel_id = '4' WHERE channel_id = '3';

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240724155715620.png$

14.分区表索引重建/不可用

用户可以通过命令使得一个分区表索引或者一个索引分区不可用，此时该索引/索引分区不再维护。使用重建索引命令可以重建分区表索引，恢复索引的正常功能。此外，部分分区级DDL操作也会使得Global索引失效，包括删除drop、交换exchange、清空truncate、分割split、合并merge。如果在DDL操作中带UPDATEGLOBAL INDEX子句，则会同步更新Global索引，否则需要用户自行重建索引。

索引重建/不可用

使用ALTER INDEX可以设置索引是否可用。例如，假设分区表range_ sales上存在索引range_sales_idx，可以通过如下命令设置其不可用。

create unique index range_sales_idx on range_sales(id); ALTER INDEX range_sales_idx UNUSABLE;

可以通过如下命令重建索引range_sales_idx。

ALTER INDEX range_sales_idx REBUILD;

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240724155956279.png$

Local 索引分区重建/不可用

● 使用ALTER INDEX PARTITION可以设置Local索引分区是否可用。● 使用ALTER TABLE MODIFY PARTITION可以设置分区表上指定分区的所有索引分区是否可用。

例如，假设分区表range_sales上存在两张Local索引range_sales_idx1和range_sales_idx2，假设其在分区date_202001上对应的索引分区名分别为range_sales_idx1_part1和range_sales_idx2_part1。

下面给出了维护分区表分区索引的语法：

● 可以通过如下命令设置分区date_202001上的所有索引分区均不可用。

select table_owner,table_name,partition_name from adm_tab_partitions where table_name='range_sales' order by 1,2,3; ALTER TABLE range_sales MODIFY PARTITION date_202001 UNUSABLE LOCAL INDEXES;

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240724164105007.png$

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240724160426442.png$

● 或者通过如下命令单独设置分区date_202001上的索引分区range_sales_idx1_part1不可用。

select index_owner,index_name,partition_name,index_partition_usable,status from adm_ind_partitions where index_name='range_sales_idx' order by 1,2,3; ALTER INDEX range_sales_idx MODIFY PARTITION date_202001_id_idx UNUSABLE;

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240724164455508.png$

● 可以通过如下命令重建分区date_202001上的所有索引分区。

ALTER TABLE range_sales MODIFY PARTITION date_202001 REBUILD UNUSABLE LOCAL INDEXES;

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240724164709813.png$

● 或者通过如下命令单独重建分区date_202001上的索引分区range_sales_idx1_part1。

ALTER INDEX range_sales_idx REBUILD PARTITION date_202001_id_idx;

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240724164850744.png$

三、分区表查询优化

1.分区剪枝

分区剪枝是GaussDB提供的一种分区表查询优化技术，数据库SQL引擎会根据查询条件，只扫描特定的部分分区。

只有分区表页面扫描和Local索引扫描才会触发分区剪枝， Global索引没有分区的概念，不需要进行剪枝。

分区表静态剪枝

对于检索条件中分区键上带有常数的分区表查询语句，在优化器阶段将对indexscan、bitmap indexscan、 indexonlyscan等算子中包含的检索条件作为剪枝条件，完成分区的筛选。

比较表达式

SET max_datanode_for_plan = 1;EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM pt01 WHERE id = 1;EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM pt01 WHERE id < 30;EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM pt01 WHERE id > 40;

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240723165123262.png$

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240723165358216.png$

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240723165421409.png$

逻辑表达式

EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM pt01 WHERE NOT id = 1;

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240723165753498.png$

数组表达式

EXPLAIN (VERBOSE ON, COSTS OFF) SELECT * FROM pt01 WHERE id IN (11, 33, 55);

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240723165934311.png$

分区表动态剪枝

对于检索条件中存在带有变量的分区表查询语句，由于优化器阶段无法获取用户的绑定参数，因此优化器阶段仅能完成indexscan、 bitmapindexscan、 indexonlyscan等算子检索条件的解析，后续会在执行器阶段获得绑定参数后，完成分区筛选。

PREPARE p1(int) AS SELECT * FROM pt01 WHERE id = $1;EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p1(66);

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240723172644500.png$

2.分区索引

分区表上的索引共有三种类型：

Global Non-Partitioned Index

Global Partitioned Index

Local Partitioned Index

目前GaussDB支持Global Non-Partitioned Index和Local Partitioned Index类型索引

唯一约束和主键约束的约束键包含所有分区键则创建LOCAL索引，否则创建GLOBAL索引。

当查询语句在查询数据涉及多个分区时，建议使用GLOBAL索引，反之建议使用LOCAL索引。但需要注意GLOBAL索引在分区维护语法中存在额外的开销。

创建LOCAL索引

CREATE INDEX pt01_idx02 ON pt01 (id) LOCAL;

创建GLOBAL索引

CREATE TABLE pt04 (LIKE pt01 INCLUDING PARTITION);CREATE INDEX pt04_idx01 ON pt04 (id) GLOBAL;

查询索引

\di+ pt01_idx02select * from MY_PART_INDEXES where index_name='pt01_idx02';select index_owner,index_name,partition_name,index_partition_usable "usable",partition_position "position",status,last_analyzed,def_tablespace_name from MY_IND_PARTITIONS order by 1,4;

修改索引分区的表空间

ALTER INDEX pt01_idx02 MOVE PARTITION c TABLESPACE tbs01;

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240723212855732.png$

重命名索引分区

ALTER INDEX pt01_idx02 RENAME PARTITION p100_id_idx TO p1_id_idx;

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240723213255885.png$

删除索引

DROP INDEX pt01_idx02;

3.分区统计信息

ANALYZE pt01 WITH ALL;SELECT relname, parttype, relpages, reltuples FROM pg_partition WHERE parentid=(SELECT oid FROM pg_class WHERE relname='pt01') ORDER BY relname;SELECT schemaname,tablename,partitionname,subpartitionname,attname,null_frac,avg_width,n_distinct,n_dndistinct,correlation FROM pg_stats WHERE tablename='pt01' and partitionname is not null ORDER BY 1,2,3,4,5;SELECT schemaname,tablename,partitionname,subpartitionname,attname,histogram_bounds FROM pg_stats WHERE tablename='pt01' and partitionname is not null ORDER BY 1,2,3,4,5;CREATE INDEX pt01_idx01 ON pt01(text(id)) LOCAL;ANALYZE pt01 WITH ALL;

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240723191344518.png$

四、相关系统表及视图

PG_PARTITION

ADM_PART_TABLES：所有分区表信息。

ADM_TAB_PARTITIONS：所有分区信息。

select table_owner,table_name,partition_name from adm_tab_partitions where table_name='range_sales' order by 1,2,3;

ADM_PART_INDEXES：所有Local索引信息。

ADM_IND_PARTITIONS：所有索引分区信息。

select index_owner,index_name,partition_name,index_partition_usable,status from adm_ind_partitions where index_name='range_sales_idx' order by 1,2,3;

五、相关内置工具函数

查看索引分区信息

SELECT oid,relname,parttype,parentid,boundaries,indextblid FROM pg_partition order by 4,3,2;

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240724125402125.png$

pg_get_tabledef

获取分区表的定义，入参可以为表的OID或者表名。

SELECT pg_get_tabledef('pt01');

pg_stat_get_partition_tuples_hot_updated

返回给定分区id的分区热更新元组数的统计。或使用relname代替oid。

DECLARE v_id int; v_name VARCHAR2(30);BEGIN for i in 400..800 loop v_id := i; v_name := 'name'||i; INSERT into pt01(id,name) VALUES (v_id,v_name); commit; END LOOP;END;/UPDATE pt01 SET name = 'lvs';SELECT pg_stat_get_partition_tuples_hot_updated(16690);

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240724124536862.png$

pg_partition_size

pg_partition_size(oid,oid)指定OID代表的分区使用的磁盘空间。其中，第一个oid为表的OID，第二个oid为分区的OID。或使用relname代替oid。

SELECT pg_partition_size('pt01', 'p1');

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240724123706204.png$

pg_partition_indexes_size

pg_partition_indexes_size(oid,oid)指定OID代表的分区索引使用的磁盘空间。其中，第一个oid为表的OID，第二个oid为分区的OID。

SELECT pg_partition_indexes_size(16686, 16701);

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240724125425980.png$

或第一个text为表名，第二个text为分区名。

SELECT pg_partition_indexes_size('pt01', 'p1');

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240724130021623.png$

pg_partition_filenode

pg_partition_filenode(partition_oid)获取到指定分区表的OID所对应的filenode。

SELECT pg_partition_filepath(16701);

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240724130305698.png$

pg_partition_filepath

pg_partition_filepath(partition_oid)指定分区的文件路径名。

SELECT pg_partition_filepath(16701);

$D:\lsq\%E5%B7%A5%E4%BD%9C\20240717_gaussdb_package_%E5%88%86%E5%8C%BA%E8%A1%A8_%E7%BA%A6%E6%9D%9F\gaussdb_%E5%88%86%E5%8C%BA%E8%A1%A8.assets\image-20240724130504552.png$

参考文档

云数据库 GaussDB 8.102 特性指南.pdf----P53

云数据库 GaussDB 8.102 分布式版开发者指南.pdf

P56 4.3.2 表设计

P1714 8.1.5 使用分区表

P1483 7.12.4.45 CREATE TABLE PARTITION

分享到：

昵称：

验证码：

快速导航首页解决方案实施分享关于我们联系我们

联系方式

联系邮箱：callcenter@antute.com.cn 联系电话：
400-069-0667

官方公众号