首页 > 其他分享 >openGauss 创建和管理分区表

openGauss 创建和管理分区表

时间：2024-04-30 11:14:02浏览次数：28  
标签：tpcds 创建 ca PARTITION character 分区表 TABLE openGauss

创建和管理分区表

背景信息

openGauss数据库支持的分区表为范围分区表、间隔分区表、列表分区表、哈希分区表。

  • 范围分区表：将数据基于范围映射到每一个分区，这个范围是由创建分区表时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期，例如将销售数据按照月份进行分区。
  • 间隔分区表：是一种特殊的范围分区表，相比范围分区表，新增间隔值定义，当插入记录找不到匹配的分区时，可以根据间隔值自动创建分区。
  • 列表分区表：将数据中包含的键值分别存储再不同的分区中，依次将数据映射到每一个分区，分区中包含的键值由创建分区表时指定。
  • 哈希分区表：将数据根据内部哈希算法依次映射到每一个分区中，包含的分区个数由创建分区表时指定。

分区表和普通表相比具有以下优点：

  • 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索效率。
  • 增强可用性：如果分区表的某个分区出现故障，表在其他分区的数据仍然可用。
  • 方便维护：如果分区表的某个分区出现故障，需要修复数据，只修复该分区即可。
  • 均衡I/O：可以把不同的分区映射到不同的磁盘以平衡I/O，改善整个系统性能。

普通表若要转成分区表，需要新建分区表，然后把普通表中的数据导入到新建的分区表中。因此在初始设计表时，请根据业务提前规划是否使用分区表。

操作步骤

示例一：使用默认表空间

  • 创建分区表（假设用户已创建tpcds schema）

    openGauss=# CREATE TABLE tpcds.customer_address
(
    ca_address_sk       integer                  NOT NULL   ,
    ca_address_id       character(16)            NOT NULL   ,
    ca_street_number    character(10)                       ,
    ca_street_name      character varying(60)               ,
    ca_street_type      character(15)                       ,
    ca_suite_number     character(10)                       ,
    ca_city             character varying(60)               ,
    ca_county           character varying(30)               ,
    ca_state            character(2)                        ,
    ca_zip              character(10)                       ,
    ca_country           character varying(20)               ,
    ca_gmt_offset       numeric(5,2)                        ,
    ca_location_type    character(20)
)

PARTITION BY RANGE (ca_address_sk)
(
        PARTITION P1 VALUES LESS THAN(5000),
        PARTITION P2 VALUES LESS THAN(10000),
        PARTITION P3 VALUES LESS THAN(15000),
        PARTITION P4 VALUES LESS THAN(20000),
        PARTITION P5 VALUES LESS THAN(25000),
        PARTITION P6 VALUES LESS THAN(30000),
        PARTITION P7 VALUES LESS THAN(40000),
        PARTITION P8 VALUES LESS THAN(MAXVALUE)
)
ENABLE ROW MOVEMENT;

    当结果显示为如下信息，则表示创建成功。

    CREATE TABLE

    说明：

    创建列存分区表的数量建议不超过1000个。

  • 插入数据

    将表tpcds.customer_address的数据插入到表tpcds.web_returns_p2中。

    例如在数据库中创建了一个表tpcds.customer_address的备份表tpcds.web_returns_p2，现在需要将表tpcds.customer_address中的数据插入到表tpcds.web_returns_p2中，则可以执行如下命令。

    openGauss=# CREATE TABLE tpcds.web_returns_p2
(
    ca_address_sk       integer                  NOT NULL   ,
    ca_address_id       character(16)            NOT NULL   ,
    ca_street_number    character(10)                       ,
    ca_street_name      character varying(60)               ,
    ca_street_type      character(15)                       ,
    ca_suite_number     character(10)                       ,
    ca_city             character varying(60)               ,
    ca_county           character varying(30)               ,
    ca_state            character(2)                        ,
    ca_zip              character(10)                       ,
    ca_country           character varying(20)               ,
    ca_gmt_offset       numeric(5,2)                        ,
    ca_location_type    character(20)
)

PARTITION BY RANGE (ca_address_sk)
(
        PARTITION P1 VALUES LESS THAN(5000),
        PARTITION P2 VALUES LESS THAN(10000),
        PARTITION P3 VALUES LESS THAN(15000),
        PARTITION P4 VALUES LESS THAN(20000),
        PARTITION P5 VALUES LESS THAN(25000),
        PARTITION P6 VALUES LESS THAN(30000),
        PARTITION P7 VALUES LESS THAN(40000),
        PARTITION P8 VALUES LESS THAN(MAXVALUE)
)
ENABLE ROW MOVEMENT;
CREATE TABLE
openGauss=# INSERT INTO tpcds.web_returns_p2 SELECT * FROM tpcds.customer_address;
INSERT 0 0

  • 修改分区表行迁移属性

    openGauss=# ALTER TABLE tpcds.web_returns_p2 DISABLE ROW MOVEMENT;
ALTER TABLE

  • 删除分区

    删除分区P8。

    openGauss=# ALTER TABLE tpcds.web_returns_p2 DROP PARTITION P8;
ALTER TABLE

  • 增加分区

    增加分区P8，范围为 40000<= P8<=MAXVALUE。

    openGauss=# ALTER TABLE tpcds.web_returns_p2 ADD PARTITION P8 VALUES LESS THAN (MAXVALUE);
ALTER TABLE

  • 重命名分区

    • 重命名分区P8为P_9。

      openGauss=# ALTER TABLE tpcds.web_returns_p2 RENAME PARTITION P8 TO P_9;
ALTER TABLE

    • 重命名分区P_9为P8。

      openGauss=# ALTER TABLE tpcds.web_returns_p2 RENAME PARTITION FOR (40000) TO P8;
ALTER TABLE

  • 查询分区

    查询分区P6。

    openGauss=# SELECT * FROM tpcds.web_returns_p2 PARTITION (P6);
openGauss=# SELECT * FROM tpcds.web_returns_p2 PARTITION FOR (35888);

  • 删除分区表和表空间

    openGauss=# DROP TABLE tpcds.customer_address;
DROP TABLE
openGauss=# DROP TABLE tpcds.web_returns_p2;
DROP TABLE

示例二：使用用户自定义表空间

按照以下方式对范围分区表的进行操作。

  • 创建表空间

    openGauss=# CREATE TABLESPACE example1 RELATIVE LOCATION 'tablespace1/tablespace_1';
openGauss=# CREATE TABLESPACE example2 RELATIVE LOCATION 'tablespace2/tablespace_2';
openGauss=# CREATE TABLESPACE example3 RELATIVE LOCATION 'tablespace3/tablespace_3';
openGauss=# CREATE TABLESPACE example4 RELATIVE LOCATION 'tablespace4/tablespace_4';

    当结果显示为如下信息，则表示创建成功。

    CREATE TABLESPACE

  • 创建分区表

    openGauss=# CREATE TABLE tpcds.customer_address
(
    ca_address_sk       integer                  NOT NULL   ,
    ca_address_id       character(16)            NOT NULL   ,
    ca_street_number    character(10)                       ,
    ca_street_name      character varying(60)               ,
    ca_street_type      character(15)                       ,
    ca_suite_number     character(10)                       ,
    ca_city             character varying(60)               ,
    ca_county           character varying(30)               ,
    ca_state            character(2)                        ,
    ca_zip              character(10)                       ,
    ca_country           character varying(20)               ,
    ca_gmt_offset       numeric(5,2)                        ,
    ca_location_type    character(20)
)
TABLESPACE example1

PARTITION BY RANGE (ca_address_sk)
(
        PARTITION P1 VALUES LESS THAN(5000),
        PARTITION P2 VALUES LESS THAN(10000),
        PARTITION P3 VALUES LESS THAN(15000),
        PARTITION P4 VALUES LESS THAN(20000),
        PARTITION P5 VALUES LESS THAN(25000),
        PARTITION P6 VALUES LESS THAN(30000),
        PARTITION P7 VALUES LESS THAN(40000),
        PARTITION P8 VALUES LESS THAN(MAXVALUE) TABLESPACE example2
)
ENABLE ROW MOVEMENT;

    当结果显示为如下信息，则表示创建成功。

    CREATE TABLE

    说明：

    创建列存分区表的数量建议不超过1000个。

  • 插入数据

    将表tpcds.customer_address的数据插入到表tpcds.web_returns_p2中。

    例如在数据库中创建了一个表tpcds.customer_address的备份表tpcds.web_returns_p2，现在需要将表tpcds.customer_address中的数据插入到表tpcds.web_returns_p2中，则可以执行如下命令。

    openGauss=# CREATE TABLE tpcds.web_returns_p2
(
    ca_address_sk       integer                  NOT NULL   ,
    ca_address_id       character(16)            NOT NULL   ,
    ca_street_number    character(10)                       ,
    ca_street_name      character varying(60)               ,
    ca_street_type      character(15)                       ,
    ca_suite_number     character(10)                       ,
    ca_city             character varying(60)               ,
    ca_county           character varying(30)               ,
    ca_state            character(2)                        ,
    ca_zip              character(10)                       ,
    ca_country           character varying(20)               ,
    ca_gmt_offset       numeric(5,2)                        ,
    ca_location_type    character(20)
)
TABLESPACE example1
PARTITION BY RANGE (ca_address_sk)
(
        PARTITION P1 VALUES LESS THAN(5000),
        PARTITION P2 VALUES LESS THAN(10000),
        PARTITION P3 VALUES LESS THAN(15000),
        PARTITION P4 VALUES LESS THAN(20000),
        PARTITION P5 VALUES LESS THAN(25000),
        PARTITION P6 VALUES LESS THAN(30000),
        PARTITION P7 VALUES LESS THAN(40000),
        PARTITION P8 VALUES LESS THAN(MAXVALUE) TABLESPACE example2
)
ENABLE ROW MOVEMENT;
CREATE TABLE
openGauss=# INSERT INTO tpcds.web_returns_p2 SELECT * FROM tpcds.customer_address;
INSERT 0 0

  • 修改分区表行迁移属性

    openGauss=# ALTER TABLE tpcds.web_returns_p2 DISABLE ROW MOVEMENT;
ALTER TABLE

  • 删除分区

    删除分区P8。

    openGauss=# ALTER TABLE tpcds.web_returns_p2 DROP PARTITION P8;
ALTER TABLE

  • 增加分区

    增加分区P8，范围为 40000<= P8<=MAXVALUE。

    openGauss=# ALTER TABLE tpcds.web_returns_p2 ADD PARTITION P8 VALUES LESS THAN (MAXVALUE);
ALTER TABLE

  • 重命名分区

    • 重命名分区P8为P_9。

      openGauss=# ALTER TABLE tpcds.web_returns_p2 RENAME PARTITION P8 TO P_9;
ALTER TABLE

    • 重命名分区P_9为P8。

      openGauss=# ALTER TABLE tpcds.web_returns_p2 RENAME PARTITION FOR (40000) TO P8;
ALTER TABLE

  • 修改分区的表空间

    • 修改分区P6的表空间为example3。

      openGauss=#  ALTER TABLE tpcds.web_returns_p2 MOVE PARTITION P6 TABLESPACE example3;
ALTER TABLE

    • 修改分区P4的表空间为example4。

      openGauss=#  ALTER TABLE tpcds.web_returns_p2 MOVE PARTITION P4 TABLESPACE example4;
ALTER TABLE

  • 查询分区

    查询分区P6。

    openGauss=# SELECT * FROM tpcds.web_returns_p2 PARTITION (P6);
openGauss=# SELECT * FROM tpcds.web_returns_p2 PARTITION FOR (35888);

  • 删除分区表和表空间

    openGauss=# DROP TABLE tpcds.web_returns_p2;
DROP TABLE
openGauss=# DROP TABLESPACE example1;
openGauss=# DROP TABLESPACE example2;
openGauss=# DROP TABLESPACE example3;
openGauss=# DROP TABLESPACE example4;
DROP TABLESPACE

详情查看：https://opengauss.org

详情查看：https://docs-opengauss.osinfra.cn

标签：tpcds,创建,ca,PARTITION,character,分区表,TABLE,openGauss
From： https://www.cnblogs.com/techbing/p/18167321

相关文章

  • openGauss 创建和管理视图
    创建和管理视图背景信息当用户对数据库中的一张或者多张表的某些字段的组合感兴趣，而又不想每次键入这些查询时，用户就可以定义一个视图，以便解决这个问题。视图与基本表不同，不是物理上实际存在的，是一个虚表。数据库中仅存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来......
  • openGauss 创建和管理表
    创建和管理表创建表向表中插入数据更新表中数据查看数据删除表中数据详情查看：https://opengauss.org详情查看：https://docs-opengauss.osinfra.cn......
  • openGauss 创建和管理表空间
    创建和管理表空间背景信息通过使用表空间，管理员可以控制一个数据库安装的磁盘布局。这样有以下优点：如果初始化数据库所在的分区或者卷空间已满，又不能逻辑上扩展更多空间，可以在不同的分区上创建和使用表空间，直到系统重新配置空间。表空间允许管理员根据数据库对象的使用模......
  • openGauss 创建和管理schema
    创建和管理schema背景信息schema又称作模式。通过管理schema，允许多个用户使用同一数据库而不相互干扰，可以将数据库对象组织成易于管理的逻辑组，同时便于将第三方应用添加到相应的schema下而不引起冲突。管理schema包括：创建schema、使用schema、删除schema、设置schema的搜索路径......
  • openGauss 管理员
    管理员初始用户数据库安装过程中自动生成的帐户称为初始用户。初始用户拥有系统的最高权限，能够执行所有的操作。如果安装时不指定初始用户名称则该帐户与进行数据库安装的操作系统用户同名。如果在安装时不指定初始用户的密码，安装完成后密码为空，在执行其他操作前需要通过gsql客......
  • openGauss 归档账本数据库
    归档账本数据库前提条件系统中需要有审计管理员或者具有审计管理员权限的角色。数据库正常运行，并且对防篡改数据库执行了一系列增、删、改等操作，保证在查询时段内有账本操作记录结果产生。数据库已经正确配置审计文件的存储路径audit_directory。背景信息账本数据库归档......
  • openGauss 管理数据库安全
    管理数据库安全客户端接入认证管理用户及权限设置数据库审计设置账本数据库详情查看：https://opengauss.org详情查看：https://docs-opengauss.osinfra.cn......
  • openGauss 管理用户及权限
    管理用户及权限默认权限机制管理员三权分立用户角色Schema用户权限设置行级访问控制设置安全策略详情查看：https://opengauss.org详情查看：https://docs-opengauss.osinfra.cn......
  • openGauss 更新表中数据
    更新表中数据修改已经存储在数据库中数据的行为叫做更新。用户可以更新单独一行、所有行或者指定的部分行。还可以独立更新每个字段，而其他字段则不受影响。使用UPDATE命令更新现有行，需要提供以下三种信息：表的名称和要更新的字段名字段的新值要更新哪些行SQL通常不会为数据......
  • openGauss 对比-磁盘与MOT
    对比：磁盘与MOT下表简要对比了基于openGauss磁盘的存储引擎和MOT存储引擎的各种特性。对比：基于磁盘与MOT特性openGauss磁盘存储openGaussMOT引擎英特尔x86+鲲鹏ARM是是SQL和功能集覆盖率100%98%纵向扩容（多核，NUMA）低效高效吞吐量高极高时......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99