数据仓库服务 GAUSSDB(DWS)-ALTER TABLE PARTITION:示例

时间:2024-12-06 15:12:41

示例

  • add_clause子句用于为指定的分区表添加一个或多个分区。

    为范围分区表customer_address增加分区ca_address_sk介于700和900之间:

    1
    ALTER TABLE customer_address ADD PARTITION P5 VALUES LESS THAN (900);
    

    为范围分区表customer_address增加分区: [5000, 5300), [5300, 5600), [5600, 5900), [5900, 6000):

    1
    ALTER TABLE customer_address_SE ADD PARTITION p6 START(5000) END(6000) EVERY(300);
    

    为范围分区表customer_address增加MAXVALUE分区p6:

    1
    ALTER TABLE customer_address ADD PARTITION p6 END(MAXVALUE);
    

    为列表分区表增加分区P6:

    1
    ALTER TABLE data_list ADD PARTITION P6 VALUES (202302,202303);
    
  • modify_clause子句用于设置分区索引是否可用。

    给分区表customer_address创建LOCAL索引student_grade_index,并指定分区的索引名称:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    CREATE INDEX customer_address_index ON customer_address(ca_address_id) LOCAL
    (
            PARTITION P1_index,
            PARTITION P2_index,
            PARTITION P3_index,
            PARTITION P4_index,
            PARTITION P5_index,
            PARTITION P6_index
    );
    

    重建分区表customer_address中分区P1上的所有索引:

    1
    ALTER TABLE customer_address MODIFY PARTITION P1 REBUILD UNUSABLE LOCAL INDEXES;
    

    设置分区表customer_address的分区P3上的所有索引不可用:

    1
    ALTER TABLE customer_address MODIFY PARTITION P3 UNUSABLE LOCAL INDEXES;
    
  • split_clause子句用于把一个分区切割成多个分区。

    将范围分区表customer_address的P6分区以1200为分割点切分:

    1
    ALTER TABLE customer_address SPLIT PARTITION P6 AT(1200) INTO (PARTITION P6a,PARTITION P6b); 
    

    将范围分区表customer_address中200所在的分区分割成多个分区:

    1
    ALTER TABLE customer_address SPLIT PARTITION FOR(200) INTO(PARTITION p_part START(100) END(300) EVERY(50));
    

    将列表分区表data_list的分区P2分割成p2a和p2b两个分区:

    1
    ALTER TABLE data_list SPLIT PARTITION P2 VALUES(202210) INTO (PARTITION p2a,PARTITION p2b);
    
  • exchange_clause子句:把普通表的数据迁移到指定的分区。

    下面示例演示了把一个普通表math_grade数据迁移到分区表student_grade中分区(math)的操作。创建分区表student_grade :

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    CREATE TABLE student_grade (
            stu_name     char(5),
            stu_no       integer,
            grade        integer,
            subject      varchar(30)
    )
    PARTITION BY LIST(subject)
    (
            PARTITION gym VALUES('gymnastics'),
            PARTITION phys VALUES('physics'),
            PARTITION history VALUES('history'),
            PARTITION math VALUES('math')
    );
    

    添加数据到分区表student_grade中:

    1
    2
    3
    4
    5
    6
    7
    INSERT INTO student_grade VALUES 
            ('Ann', 20220101, 75, 'gymnastics'),
            ('Jeck', 20220103, 60, 'math'),
            ('Anna', 20220108, 56, 'history'),
            ('Jann', 20220107, 82, 'physics'),
            ('Molly', 20220104, 91, 'physics'),
            ('Sam', 20220105, 72, 'math');
    

    查询分区表student_grade的math分区记录:

    1
    2
    3
    4
    5
    6
    SELECT * FROM student_grade PARTITION (math);
     stu_name |  stu_no  | grade | subject
    ----------+----------+-------+---------
     Jeck     | 20220103 |    60 | math
     Sam      | 20220105 |    72 | math
    (2 rows)
    

    创建一个与分区表student_grade定义匹配的普通表math_grade:

    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE math_grade 
    (
            stu_name     char(5),
            stu_no       integer,
            grade        integer,
            subject      varchar(30)
    );
    

    添加了数据到表math_grade中。数据与分区表student_grade的math分区的分区规则一致:

    1
    2
    3
    4
    5
    INSERT INTO math_grade VALUES 
            ('Ann', 20220101, 75, 'math'),
            ('Jeck', 20220103, 60, 'math'),
            ('Anna', 20220108, 56, 'math'),
            ('Jann', 20220107, 82, 'math');
    

    将普通表math_grade数据迁移到分区表student_grade中分区(math):

    1
    ALTER TABLE student_grade EXCHANGE PARTITION (math) WITH TABLE math_grade;
    

    对分区表student_grade的查询表明表math_grade中的数据已和分区math中的数据交换:

    1
    2
    3
    4
    5
    6
    7
    8
    SELECT * FROM student_grade PARTITION (math);
     stu_name |  stu_no  | grade | subject
    ----------+----------+-------+---------
     Anna     | 20220108 |    56 | math
     Jeck     | 20220103 |    60 | math
     Ann      | 20220101 |    75 | math
     Jann     | 20220107 |    82 | math
    (4 rows)
    

    对表math_grade的查询显示了之前存储在分区math中的记录已被移动到表student_grade中:

    1
    2
    3
    4
    5
    6
    SELECT * FROM math_grade
     stu_name |  stu_no  | grade | subject
    ----------+----------+-------+---------
     Jeck     | 20220103 |    60 | math
     Sam      | 20220105 |    72 | math
    (2 rows)
    
  • truncate_partitioned_clause子语法用于清理表分区的数据。

    清空表student_grade分区p1:

    1
    ALTER TABLE student_grade TRUNCATE PARTITION p1;
    
  • row_clause子句用于设置分区表的行迁移开关。

    打开分区表customer_address的迁移开关:

    1
    ALTER TABLE customer_address ENABLE ROW MOVEMENT;
    
  • merge_clause子句用于把多个分区合并成一个分区。

    将范围分区表customer_address的P2,P3两个分区合并为一个分区:

    1
    ALTER TABLE customer_address MERGE PARTITIONS P2, P3 INTO PARTITION P_M; 
    
  • drop_clause子句用于删除分区表中的指定分区。

    删除分区表customer_address的分区P6:

    1
    ALTER TABLE customer_address DROP PARTITION P6;
    

    删除分区表customer_address的多个分区P3,P4,P5:

    1
    ALTER TABLE customer_address DROP PARTITION P3, P4, P5;
    
support.huaweicloud.com/sqlreference-910-dws/dws_06_0143.html