质疑openGauss官方文档--关于二级分区表的相关功能

测试二级分区表

# 创建 Range-List 复合分区
CREATE TABLE emp_range_list
    ( empno        NUMBER(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMBER(4,0)
    , hiredate     DATE
    , sal          NUMBER(7,2)
    , comm         NUMBER(7,2)
    , deptno       NUMBER(2,0)
    ) 
PARTITION BY RANGE (hiredate)
SUBPARTITION BY LIST (deptno)
  (PARTITION hiredate_1987 VALUES LESS THAN (TO_DATE('01-01-1988','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1987_deptno_10 VALUES (10),
     SUBPARTITION hiredate_1987_deptno_default VALUES (DEFAULT)),
   PARTITION hiredate_max VALUES LESS THAN (MAXVALUE)
    (SUBPARTITION hiredate_max_deptno_10 VALUES (10),
     SUBPARTITION hiredate_max_deptno_default VALUES (DEFAULT)));

# 创建 List-Range 复合分区
CREATE TABLE emp_list_range
    ( empno        NUMBER(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMBER(4,0)
    , hiredate     DATE
    , sal          NUMBER(7,2)
    , comm         NUMBER(7,2)
    , deptno       NUMBER(2,0)
    ) 
PARTITION BY LIST (deptno)
SUBPARTITION BY RANGE (hiredate)
  (PARTITION deptno_10 VALUES (10)
    (SUBPARTITION deptno_10_hiredate_1988 VALUES LESS THAN (TO_DATE('01-01-1989','DD-MM-YYYY')),
     SUBPARTITION deptno_10_hiredate_max  VALUES LESS THAN (MAXVALUE)),
   PARTITION deptno_default VALUES (DEFAULT)
    (SUBPARTITION deptno_default_hiredate_1988 VALUES LESS THAN (TO_DATE('01-01-1989','DD-MM-YYYY')),
     SUBPARTITION deptno_default_hiredate_max  VALUES LESS THAN (MAXVALUE)));
复制

测试版本

openGauss=> select version();
                                                                       version
------------------------------------------------------------------------------------------------------------------------------------------------------
 (openGauss 5.0.0 build a07d57c3) compiled at 2023-03-29 03:07:56 commit 0 last mr   on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)
复制

分区重命名

• 官方文档提供了重命名复合分区的一级分区和二级分区的方法，但实际执行提示不支持复合分区的重命名
  

# 重命名一级分区
openGauss=> ALTER TABLE emp_range_list RENAME PARTITION hiredate_1987 TO hiredate_1987_bak;
ERROR:  Un-support feature
DETAIL:  For subpartition table, ALTER TABLE ... RENAME PARTITION/SUBPARTITION is not yet supported.

# 重命名二级分区
openGauss=> ALTER TABLE emp_range_list RENAME SUBPARTITION hiredate_1987_deptno_10 TO hiredate_1987_deptno_10_bak;
ERROR:  syntax error at or near "hiredate_1987_deptno_10"
LINE 1: ALTER TABLE emp_range_list RENAME SUBPARTITION hiredate_1987...
                                                       ^
复制

移动分区表空间

• 官方文档没有提供移动复合分区的一级分区到其他表空间的方法，但是提供了移动二级分区到其他表空间的方法，但实际执行提示语法错误
  

# 创建一个新表空间
CREATE TABLESPACE ds_location1 RELATIVE LOCATION 'tablespace/tablespace_1';

# 移动一级分区，提示不支持
openGauss=> ALTER TABLE emp_range_list MOVE PARTITION hiredate_1987 TABLESPACE ds_location1;
ERROR:  Un-support feature
DETAIL:  For subpartition table, modifying tablespace is not yet supported.

# 移动二级分区，报语法错误
openGauss=> ALTER TABLE emp_range_list MOVE SUBPARTITION hiredate_1987_deptno_10 TABLESPACE ds_location1;
ERROR:  syntax error at or near "SUBPARTITION hiredate_1987_deptno_10"
LINE 1: ALTER TABLE emp_range_list MOVE SUBPARTITION hiredate_1987_d...
                                        ^
复制

切割二级分区

• 切割分区只能对二级分区（叶子节点）进行切割，被切割分区只能是Range、List分区策略，不支持切割hash分区策略。List分区策略只能是default分区才能被切割。
  
  

切割分区只能对二级分区（叶子节点）进行切割（没问题）

# 确实不支持切割一级分区
openGauss=> ALTER TABLE emp_range_list SPLIT PARTITION hiredate_max AT (TO_DATE('01-01-1989','DD-MM-YYYY')) INTO ( PARTITION hiredate_1988, PARTITION t_hiredate_max);
ERROR:  Un-support feature
DETAIL:  For subpartition table, split partition is not supported yet.

# 支持切割是范围分区的二级分区
openGauss=> ALTER TABLE emp_list_range SPLIT SUBPARTITION deptno_10_hiredate_max AT (TO_DATE('01-01-1990','DD-MM-YYYY')) INTO (SUBPARTITION deptno_10_hiredate_1989, SUBPARTITION deptno_10_hiredate_max);
ERROR:  resulting subpartition "deptno_10_hiredate_max" name conflicts with an existing subpartition

# 拆分分区时生成的分区名称与现有的分区名称不能相同，Oracle 可以支持，建议加强这块
openGauss=> ALTER TABLE emp_list_range SPLIT SUBPARTITION deptno_10_hiredate_max AT (TO_DATE('01-01-1990','DD-MM-YYYY')) INTO (SUBPARTITION deptno_10_hiredate_1989, SUBPARTITION t_deptno_10_hiredate_max);
ALTER TABLE

# 查看分区情况
SELECT c.relname as table_name, p.relname as partition_name, sp.relname subpartition_name, sp.reloptions, sp.boundaries, sp.parttype, sp.partstrategy, coalesce(t.spcname, 'default') as tablespace_name
  FROM pg_catalog.pg_partition p  -- 分区
  join pg_catalog.pg_partition sp on p.oid = sp.parentid and sp.parttype = 's'  -- 子分区
  join pg_catalog.pg_class c on p.parentid = c.oid  -- 父表详细信息
  left join pg_catalog.pg_tablespace t on p.reltablespace = t.oid  -- 父表表空间
 WHERE p.parttype = 'p' and c.relname = 'emp_list_range'
 ORDER BY partition_name, subpartition_name;

   table_name   | partition_name |      subpartition_name       |            reloptions            |       boundaries        | parttype | partstrategy | tablespace_name
----------------+----------------+------------------------------+----------------------------------+-------------------------+----------+--------------+-----------------
 emp_list_range | deptno_10      | deptno_10_hiredate_1988      | {orientation=row,compression=no} | {"1989-01-01 00:00:00"} | s        | r            | default
 emp_list_range | deptno_10      | deptno_10_hiredate_1989      |                                  | {"1990-01-01 00:00:00"} | s        | r            | default
 emp_list_range | deptno_10      | t_deptno_10_hiredate_max     |                                  | {NULL}                  | s        | r            | default
 emp_list_range | deptno_default | deptno_default_hiredate_1988 | {orientation=row,compression=no} | {"1989-01-01 00:00:00"} | s        | r            | default
 emp_list_range | deptno_default | deptno_default_hiredate_max  | {orientation=row,compression=no} | {NULL}                  | s        | r            | default
(5 rows)

# 可以看到被拆分出来的两个子分区开启了压缩功能，reloptions 字段变空。
复制

被切割分区只能是Range、List分区策略，List分区策略只能是default分区才能被切割

• Range 分区策略上个实验已经切割成功
• List 分区策略实际执行不支持切割，SPLIT SUBPARTITION 只支持 range 子分区

openGauss=> ALTER TABLE emp_range_list SPLIT SUBPARTITION hiredate_1987_deptno_default AT (40) INTO (SUBPARTITION hiredate_1987_deptno_40, SUBPARTITION t_hiredate_1987_deptno_default);
ERROR:  The syntax format of split subpartition is incorrect.
DETAIL:  SPLIT SUBPARTITION NAME AT shouldn't be used, it's for range subpartitions.

复制

交换二级分区

# 创建普通表，表结构和约束同分区表 emp_range_list 相同
create table sales_table (like emp_range_list including constraints);
# 向普通表中插入数据
INSERT INTO sales_table VALUES ('7369', 'SMITH', 'CLERK', '7902', TO_DATE('17-12-1979', 'dd-mm-yyyy'), '800', NULL, '10');

# 执行交换二级分区，报语法错误
openGauss=> ALTER TABLE emp_range_list EXCHANGE SUBPARTITION (hiredate_1987_deptno_10) WITH TABLE sales_table;
ERROR:  syntax error at or near "SUBPARTITION ("
LINE 1: ALTER TABLE emp_range_list EXCHANGE SUBPARTITION (hiredate_1...
                                            ^
复制