云数据库 GAUSSDB-CREATE TABLE SUBPARTITION:示例

时间：2024-11-02 18:45:39

云数据库 GAUSSDB

示例

创建二级分区表示例

    
     
       
       
         --创建二级分区表tbl_list_list，一级分区和二级分区类型都是LIST。
gaussdb=# CREATE TABLE tbl_list_list(
    sal_year    varchar(4)  NOT NULL,
    area_id     char(5)     NOT NULL,
    emp_id      char(5)     NOT NULL,
    sales_amt   int
) PARTITION BY LIST (sal_year) SUBPARTITION BY LIST(area_id)(
    PARTITION P_2019 VALUES ('2019')(
        SUBPARTITION p_2019_01001 VALUES ('01001'),
        SUBPARTITION p_2019_01002 VALUES ('01002'),
        SUBPARTITION p_2019_01003 VALUES ('01003')            
    ),
    PARTITION p_2020 VALUES ('2020')(
        SUBPARTITION p_2020_01001 VALUES ('01001'),
        SUBPARTITION p_2020_01002 VALUES ('01002'),
        SUBPARTITION p_2020_01003 VALUES ('01003')      
    )
);

--创建二级分区表tbl_range_list,其一级分区为RANGE类型二级分区为list类型。
gaussdb=# CREATE TABLE tbl_range_list(
    sal_date    varchar(6)  NOT NULL,
    area_id     char(5)     NOT NULL,
    emp_id      char(5)     NOT NULL,
    sales_amt   int         
) PARTITION BY RANGE (sal_date) SUBPARTITION BY LIST(area_id)(
    PARTITION p_201901 VALUES LESS THAN (201902)(
        SUBPARTITION p_201901_01001 VALUES ('01001'),
        SUBPARTITION p_201901_01002 VALUES ('01002'),
        SUBPARTITION p_201901_01003 VALUES ('01003')
    ),
    PARTITION p_201902 VALUES LESS THAN (201903)(
        SUBPARTITION p_201902_01001 VALUES ('01001'),
        SUBPARTITION p_201902_01002 VALUES ('01002'),
        SUBPARTITION p_201902_01003 VALUES ('01003')
    ) 
);

        

      

    
   

对二级分区表进行DML指定分区操作

INSERT

--指定一级分区插入数据。
gaussdb=# INSERT INTO tbl_range_list PARTITION(p_201901) VALUES('201901', '01001', '0001', 75000 );

--实际分区和指定分区不一致，报错。
gaussdb=# INSERT INTO tbl_range_list PARTITION(p_201902) VALUES('201901', '01001', '0002', 6000);
ERROR:  inserted partition key does not map to the table partition
DETAIL:  N/A.

--指定二级分区插入数据。
gaussdb=# INSERT INTO tbl_range_list SUBPARTITION(p_201902_01001) VALUES('201902', '01001', '0002', 8000);

SELECT

--指定分区查询数据。
gaussdb=# SELECT * FROM tbl_range_list PARTITION(p_201902);
 sal_date | area_id | emp_id | sales_amt 
----------+---------+--------+-----------
 201902   | 01001   | 0002   |      8000
(1 row)

gaussdb=# SELECT * FROM tbl_range_list SUBPARTITION(p_201901_01001);
 sal_date | area_id | emp_id | sales_amt 
----------+---------+--------+-----------
 201901   | 01001   | 0001   |     75000
(1 row)

UPDATE

--指定分区更新数据。
gaussdb=# UPDATE tbl_range_list PARTITION(p_201901) SET sales_amt = 7000;

gaussdb=# SELECT * FROM tbl_range_list;
 sal_date | area_id | emp_id | sales_amt 
----------+---------+--------+-----------
 201901   | 01001   | 0001   |      7000
 201902   | 01001   | 0002   |      8000
(2 rows)

gaussdb=# UPDATE tbl_range_list SUBPARTITION FOR('201902','01001') SET sales_amt=6000;

gaussdb=# SELECT * FROM tbl_range_list;
 sal_date | area_id | emp_id | sales_amt 
----------+---------+--------+-----------
 201901   | 01001   | 0001   |      7000
 201902   | 01001   | 0002   |      6000
(2 rows)

DELETE

--指定分区删除数据。
gaussdb=# DELETE FROM tbl_range_list PARTITION (p_201901);
DELETE 1

gaussdb=# DELETE FROM tbl_range_list SUBPARTITION (p_201902_01001);
DELETE 1

gaussdb=# DELETE FROM tbl_range_list SUBPARTITION for ('201901','01002');
DELETE 0

--参数sql_compatibility='B'时，可指定多分区删除数据。
gaussdb=# CREATE DATABASE db dbcompatibility 'B';
gaussdb=# \c db
db=# CREATE TABLE range_list
(
    month_code VARCHAR2 ( 30 ) NOT NULL ,
    dept_code  VARCHAR2 ( 30 ) NOT NULL ,
    user_no    VARCHAR2 ( 30 ) NOT NULL ,
    sales_amt  int
)
PARTITION BY RANGE (month_code) SUBPARTITION BY LIST (dept_code)
(
  PARTITION p_201901 VALUES LESS THAN( '201903' )
  (
    SUBPARTITION p_201901_a VALUES ('1'),
    SUBPARTITION p_201901_b VALUES ('2')
  ),
  PARTITION p_201902 VALUES LESS THAN( '201910' )
  (
    SUBPARTITION p_201902_a VALUES ('1'),
    SUBPARTITION p_201902_b VALUES ('2')
  )
);

db=# DELETE FROM range_list AS t partition (p_201901_a, p_201901);
DELETE 0

--删除数据库(根据实际情况替换数据库名)。
db=# \c postgres 
gaussdb=# DROP DATABASE db;

--删除表。
gaussdb=# DROP TABLE tbl_list_list;
gaussdb=# DROP TABLE tbl_range_list;