分區(qū)表維護(hù)支持情況
| 維護(hù) | PGSQL 13.3 | OG 2.1 | OG 3.0 | mysql 8.0 | IvorySQL 1.2 |
|---|---|---|---|---|---|
| 增加分區(qū) | Y | Y | Y | - | Y |
| 刪除分區(qū) | Y | Y | Y | - | Y |
| 截?cái)喾謪^(qū) | Y | Y | Y | - | Y |
| 合并分區(qū) | N | Y | Y | - | N |
| 拆分分區(qū) | N | Y | Y | - | N |
| 交換分區(qū) | N | Y | Y | - | N |
| 移動(dòng)分區(qū) | Y | Y | Y | - | Y |
| 修改分區(qū)名稱 | Y | Y | Y | - | Y |
- 備注：
- openGauss 的交換分區(qū)測(cè)試失敗，不曉得啥原因，看文檔是支持交換分區(qū)。
- PostgreSQL and IvorySQL 對(duì)雖然不支持交換分區(qū)，單是可以通過分區(qū)的解綁和綁定來很簡(jiǎn)單的變相實(shí)現(xiàn)。
- 本文均是測(cè)試結(jié)果，僅供參考
查詢表的分區(qū)情況
-- oracle
select table_name,partition_name,num_rows,high_value from dba_tab_partitions where table_name='EMP_RANGE_RANGE';
TABLE_NAME PARTITION_NAME NUM_ROWS HIGH_VALUE
----------------- ---------------- ---------- ------------------------------------------------------------------------------------
EMP_RANGE_RANGE HIREDATE_MAX MAXVALUE
EMP_RANGE_RANGE HIREDATE_1987 TO_DATE(' 1988-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
EMP_RANGE_RANGE HIREDATE_1986 TO_DATE(' 1987-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
EMP_RANGE_RANGE HIREDATE_1985 TO_DATE(' 1986-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
EMP_RANGE_RANGE HIREDATE_1984 TO_DATE(' 1985-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
EMP_RANGE_RANGE HIREDATE_1983 TO_DATE(' 1984-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
EMP_RANGE_RANGE HIREDATE_1982 TO_DATE(' 1983-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
EMP_RANGE_RANGE HIREDATE_1981 TO_DATE(' 1982-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
EMP_RANGE_RANGE HIREDATE_1980 TO_DATE(' 1981-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
EMP_RANGE_RANGE HIREDATE_1979 TO_DATE(' 1980-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
10 rows selected.
select table_name,partition_name,subpartition_name,num_rows,high_value from dba_tab_subpartitions where table_name='emp_range_range';
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS HIGH_VALUE
------------------------------ ------------------------------ ------------------------------ ---------- -----------------
EMP_RANGE_RANGE HIREDATE_1980 HIREDATE_1980_MIN 1000
EMP_RANGE_RANGE HIREDATE_1980 HIREDATE_1980_1000 2000
EMP_RANGE_RANGE HIREDATE_1980 HIREDATE_1980_2000 3000
EMP_RANGE_RANGE HIREDATE_1980 HIREDATE_1980_3000 4000
EMP_RANGE_RANGE HIREDATE_1980 HIREDATE_1980_4000 5000
EMP_RANGE_RANGE HIREDATE_1980 HIREDATE_1980_MAX MAXVALUE
... ...
EMP_RANGE_RANGE HIREDATE_1987 HIREDATE_1987_MIN 1000
EMP_RANGE_RANGE HIREDATE_1987 HIREDATE_1987_1000 2000
EMP_RANGE_RANGE HIREDATE_1987 HIREDATE_1987_2000 3000
EMP_RANGE_RANGE HIREDATE_1987 HIREDATE_1987_3000 4000
EMP_RANGE_RANGE HIREDATE_1987 HIREDATE_1987_4000 5000
EMP_RANGE_RANGE HIREDATE_1987 HIREDATE_1987_MAX MAXVALUE
EMP_RANGE_RANGE HIREDATE_MAX HIREDATE_MAX_MIN 1000
EMP_RANGE_RANGE HIREDATE_MAX HIREDATE_MAX_1000 2000
EMP_RANGE_RANGE HIREDATE_MAX HIREDATE_MAX_2000 3000
EMP_RANGE_RANGE HIREDATE_MAX HIREDATE_MAX_3000 4000
EMP_RANGE_RANGE HIREDATE_MAX HIREDATE_MAX_4000 5000
EMP_RANGE_RANGE HIREDATE_MAX HIREDATE_MAX_MAX MAXVALUE
-- PostgreSQL and IvorySQL
\d+ emp_range_range --只能查到一級(jí)分區(qū)，二級(jí)分區(qū)需要繼續(xù)使用 \d+ 查看
postgres=# \d+ emp_range_range
Partitioned table "public.emp_range_range"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+-----------------------+-----------+----------+---------+----------+--------------+-------------
empno | numeric(4,0) | | | | main | |
ename | character varying(10) | | | | extended | |
job | character varying(9) | | | | extended | |
mgr | numeric(4,0) | | | | main | |
hiredate | date | | | | plain | |
sal | numeric(7,2) | | | | main | |
comm | numeric(7,2) | | | | main | |
deptno | numeric(2,0) | | | | main | |
Partition key: RANGE (hiredate)
Partitions: emp_range_range_1979 FOR VALUES FROM ('1979-01-01') TO ('1980-01-01'), PARTITIONED,
emp_range_range_1980 FOR VALUES FROM ('1980-01-01') TO ('1981-01-01'), PARTITIONED,
emp_range_range_1981 FOR VALUES FROM ('1981-01-01') TO ('1982-01-01'), PARTITIONED,
emp_range_range_1982 FOR VALUES FROM ('1982-01-01') TO ('1983-01-01'), PARTITIONED,
emp_range_range_1983 FOR VALUES FROM ('1983-01-01') TO ('1984-01-01'), PARTITIONED,
emp_range_range_1984 FOR VALUES FROM ('1984-01-01') TO ('1985-01-01'), PARTITIONED,
emp_range_range_1985 FOR VALUES FROM ('1985-01-01') TO ('1986-01-01'), PARTITIONED,
emp_range_range_1986 FOR VALUES FROM ('1986-01-01') TO ('1987-01-01'), PARTITIONED,
emp_range_range_1987 FOR VALUES FROM ('1987-01-01') TO ('1988-01-01'), PARTITIONED,
emp_range_range_1988 FOR VALUES FROM ('1988-01-01') TO ('1989-01-01'), PARTITIONED,
emp_range_range_default DEFAULT, PARTITIONED
postgres=# \d+ emp_range_range_1979
Partitioned table "public.emp_range_range_1979"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+-----------------------+-----------+----------+---------+----------+--------------+-------------
empno | numeric(4,0) | | | | main | |
ename | character varying(10) | | | | extended | |
job | character varying(9) | | | | extended | |
mgr | numeric(4,0) | | | | main | |
hiredate | date | | | | plain | |
sal | numeric(7,2) | | | | main | |
comm | numeric(7,2) | | | | main | |
deptno | numeric(2,0) | | | | main | |
Partition of: emp_range_range FOR VALUES FROM ('1979-01-01') TO ('1980-01-01')
Partition constraint: ((hiredate IS NOT NULL) AND (hiredate >= '1979-01-01'::date) AND (hiredate < '1980-01-01'::date))
Partition key: RANGE (sal)
Partitions: emp_range_range_1979_1000 FOR VALUES FROM (1000.00) TO (2000.00),
emp_range_range_1979_2000 FOR VALUES FROM (2000.00) TO (3000.00),
emp_range_range_1979_3000 FOR VALUES FROM (3000.00) TO (4000.00),
emp_range_range_1979_4000 FOR VALUES FROM (4000.00) TO (5000.00),
emp_range_range_1979_max FOR VALUES FROM (5000.00) TO (MAXVALUE),
emp_range_range_1979_min FOR VALUES FROM (MINVALUE) TO (1000.00)
-- openGauss
\d+ emp_range_range --只能看到分區(qū)個(gè)數(shù)
openGauss=# \d+ emp_range_range
Table "public.emp_range_range"
Column | Type | Modifiers | Storage | Stats target | Description
----------+--------------------------------+-----------+----------+--------------+-------------
empno | numeric(4,0) | | main | |
ename | character varying(10) | | extended | |
job | character varying(9) | | extended | |
mgr | numeric(4,0) | | main | |
hiredate | timestamp(0) without time zone | | plain | |
sal | numeric(7,2) | | main | |
comm | numeric(7,2) | | main | |
deptno | numeric(2,0) | | main | |
Partition By RANGE(hiredate) Subpartition By RANGE(sal)
Number of partitions: 10 (View pg_partition to check each partition range.)
Number of subpartitions: 60 (View pg_partition to check each subpartition range.)
Has OIDs: no
Options: orientation=row, compression=no
-- openGauss 的 pg_partition 視圖查詢一級(jí)分區(qū)還可以，如果想一次性查詢二級(jí)分區(qū)(subpartition)比較費(fèi)勁，此時(shí)可以使用 compare_tool 工具
openGauss=# select relname,parttype,partstrategy,parentid,boundaries from pg_partition where parentid=(select parentid from pg_partition where relname ='emp_range_range');
relname | parttype | partstrategy | parentid | boundaries
-----------+----------+--------------+----------+------------
emp_list | r | l | 25617 |
deptno_10 | p | l | 25617 | {10}
deptno_20 | p | l | 25617 | {20}
deptno_30 | p | l | 25617 | {30}
compare_tool 工具
compare_tool 是一個(gè)兼容工具集合，旨在為從其他異構(gòu)數(shù)據(jù)庫，遷移到 openGauss 之后的系統(tǒng)，創(chuàng)建必要的函數(shù)，以及系統(tǒng)視圖的兼容。為后續(xù)的系統(tǒng)運(yùn)維與應(yīng)用改造提供便利。
compare_tool 詳情參考：https://gitee.com/enmotech/compat-tools
- 以下使用 compare_tool 創(chuàng)建 Oracle 兼容視圖
tar -xvf compat-tools-v2022.03.23.tar
gsql -d postgres -p 1412 -U omm -r -f compat-tools/Oracle_Views.sql
gsql -d postgres -p 1412 -U omm -r
openGauss=# select table_name,partition_name,subpartition_name,num_rows,high_value from dba_tab_subpartitions where table_name='EMP_RANGE_RANGE';
table_name | partition_name | subpartition_name | num_rows | high_value
-----------------+----------------+--------------------+----------+------------
EMP_RANGE_RANGE | HIREDATE_1979 | HIREDATE_1979_MIN | 0 | 1000
EMP_RANGE_RANGE | HIREDATE_1979 | HIREDATE_1979_1000 | 0 | 2000
EMP_RANGE_RANGE | HIREDATE_1979 | HIREDATE_1979_2000 | 0 | 3000
EMP_RANGE_RANGE | HIREDATE_1979 | HIREDATE_1979_3000 | 0 | 4000
EMP_RANGE_RANGE | HIREDATE_1979 | HIREDATE_1979_4000 | 0 | 5000
EMP_RANGE_RANGE | HIREDATE_1979 | HIREDATE_1979_MAX | 0 | MAXVALUE
... ...
刪除分區(qū)
-- oracle and openGauss
alter table emp_range drop partition hiredate_1979;
-- PostgreSQL and IvorySQL 刪除子表就是刪除分區(qū)
drop table emp_range_hiredate_2020;
- Oracle 和 openGauss 最后一個(gè)分區(qū)或子分區(qū)不允許刪除，PostgreSQL and IvorySQL 可以刪除最后一個(gè)分區(qū)或子分區(qū)
-- oracle
ORA-14083: cannot drop the only partition of a partitioned table
ORA-14629: cannot drop the only subpartition of a partition
-- openGauss
ERROR: Cannot drop the only partition of a partitioned table
ERROR: Cannot drop the only subpartition of a partitioned table
- Oracle 和 openGauss 不允許刪除哈希分區(qū)表的任何分區(qū)或子分區(qū)，報(bào)錯(cuò)信息如下
-- oracle
ORA-14255: table is not partitioned by Range, List, Composite Range or Composite List method
ORA-14206: table is not subpartitioned by List or Range methods
-- openGauss
ERROR: Droping hash partition is unsupported.
ERROR: Un-support feature
DETAIL: The syntax is unsupported for hash subpartition
- PostgreSQL 和 IvorySQL 雖然可以刪除哈希分區(qū)表的分區(qū)，但是后續(xù)插入的數(shù)據(jù)還會(huì)分配到已經(jīng)刪除的分區(qū)中，當(dāng)發(fā)現(xiàn)子分區(qū)不存在會(huì)報(bào)出以下錯(cuò)誤信息
postgres=# insert into emp_hash (sal) values (2225);
ERROR: no partition of relation "emp_hash" found for row
DETAIL: Partition key of the failing row contains (sal) = (2225.00).
- Oracle 不允許刪除間隔分區(qū)表手工創(chuàng)建的最后一個(gè)分區(qū)，報(bào)錯(cuò)信息如下
ORA-14758: Last partition in the range section cannot be dropped
解決方法：將間隔分區(qū)表轉(zhuǎn)為普通分區(qū)表
-- 設(shè)置自動(dòng)分區(qū)為普通范圍分區(qū)
SQL> alter table EMP_INTERVAL set interval();
-- 查看修改后的分區(qū)信息
SQL> select table_name,partition_name,num_rows,high_value,interval from dba_tab_partitions where table_name='EMP_INTERVAL';
-- 刪除手工創(chuàng)建的分區(qū)
SQL> alter table EMP_INTERVAL drop partition SAL_P1;
-- 設(shè)置普通分區(qū)為自動(dòng)分區(qū)
SQL> alter table EMP_INTERVAL set interval(1000);
增加分區(qū)
- 添加分區(qū)的方式
-- oracle and openGauss
alter table emp_range add partition hiredate_1988 values less than (to_date('1989-01-01','yyyy-dd-mm'));
alter table EMP_LIST add partition DEPTNO_40 values (40);
-- PostgreSQL and IvorySQL 創(chuàng)建子表進(jìn)行增加分區(qū)
create table emp_range_hiredate_1989 PARTITION of emp_range FOR VALUES FROM ('1989-01-01 00:00:00+08') TO ('1990-01-01 00:00:00+08');
create table emp_list_deptno_40 partition of emp_list for values in (40);
- 如果 PostgreSQL 和 IvorySQL 在 RANGE 和 LIST 類型的分區(qū)表的 DEFAULT 分區(qū)中存在要添加的分區(qū)數(shù)據(jù)時(shí)
-- 解綁 Default 分區(qū)
ALTER TABLE emp_range DETACH PARTITION emp_range_hiredate_default;
-- 創(chuàng)建新分區(qū)
create table emp_range_hiredate_2020 PARTITION of emp_range FOR VALUES FROM ('2020-01-01 00:00:00+08') TO ('2021-01-01 00:00:00+08');
-- Default 分區(qū)數(shù)據(jù)轉(zhuǎn)移到 新分區(qū)
INSERT INTO emp_range_hiredate_2020 SELECT * FROM emp_range_hiredate_default where hiredate >= ('2020-01-01 00:00:00+08') and hiredate < ('2021-01-01 00:00:00+08');
DELETE FROM emp_range_hiredate_default where hiredate >= ('2020-01-01 00:00:00+08') and hiredate < ('2021-01-01 00:00:00+08');
-- 重新綁定 Default 分區(qū)
ALTER TABLE emp_range ATTACH PARTITION emp_range_hiredate_default DEFAULT;
-- 解綁 Default 分區(qū)
alter table emp_list detach partition emp_list_deptno_default;
-- 創(chuàng)建新分區(qū)
create table emp_list_deptno_50 partition of emp_list for values in (50);
-- Default 分區(qū)數(shù)據(jù)轉(zhuǎn)移到 新分區(qū)
insert into emp_list_deptno_50 select * from emp_list_deptno_default where deptno = 50;
delete from emp_list_deptno_default where deptno = 50;
-- 重新綁定 Default 分區(qū)
alter table emp_list attach partition emp_list_deptno_default default;
- RANGE 分區(qū)表存在 MAXVALUE 時(shí)添加分區(qū)的報(bào)錯(cuò)信息
-- oracle
sql> alter table emp_range add partition hiredate_1988 values less than (to_date('1989-01-01','yyyy-dd-mm'));
ora-14074: partition bound must collate higher than that of the last partition
-- openGauss
openGauss=# alter table emp_range add partition hiredate_1988 values less than (to_date('1989-01-01','yyyy-dd-mm'));
ERROR: upper boundary of adding partition MUST overtop last existing partition
- LIST 分區(qū)表存在 DEFAULT 時(shí)添加分區(qū)的報(bào)錯(cuò)信息
-- oracle
SQL> alter table EMP_LIST add partition DEPTNO_40 values (40);
ORA-14323: cannot add partition when DEFAULT partition exists
-- openGauss 3.0，openGauss 2.1 不支持 DEFAULT 分區(qū)
openGauss=# alter table EMP_LIST add partition DEPTNO_40 values (40);
ERROR: list boundary of adding partition MUST NOT overlap with existing partition
- PostgreSQL 在 RANGE 和 LIST 類型的分區(qū)表的 DEFAULT 分區(qū)中存在要添加的分區(qū)數(shù)據(jù)時(shí)的報(bào)錯(cuò)信息
postgres=# insert into emp_range (hiredate) values ('2020-02-02');
postgres=# create table emp_range_hiredate_2020 PARTITION of emp_range FOR VALUES FROM ('2020-01-01 00:00:00+08') TO ('2021-01-01 00:00:00+08');
ERROR: updated partition constraint for default partition "emp_range_hiredate_default" would be violated by some row
postgres=# insert into EMP_LIST (deptno) values (50);
postgres=# create table emp_list_deptno_50 partition of emp_list for values in (50);
ERROR: updated partition constraint for default partition "emp_list_deptno_default" would be violated by some row
- Oracle 支持為 HASH 分區(qū)表添加分區(qū)，添加分區(qū)完成后會(huì)進(jìn)行數(shù)據(jù)重分布
SQL> analyze table EMP_HASH compute statistics;
SQL> select table_name,partition_name,num_rows,high_value from dba_tab_partitions where table_name='EMP_HASH';
TABLE_NAME PARTITION_NAME NUM_ROWS HIGH_VALUE
------------ ----------------- ---------- ------------
EMP_HASH EMP_HASH_4 4
EMP_HASH EMP_HASH_3 2
EMP_HASH EMP_HASH_2 4
EMP_HASH EMP_HASH_1 4
SQL> alter table emp_hash add partition emp_hash_5;
SQL> analyze table EMP_HASH compute statistics;
SQL> select table_name,partition_name,num_rows,high_value from dba_tab_partitions where table_name='EMP_HASH';
TABLE_NAME PARTITION_NAME NUM_ROWS HIGH_VALUE
------------ ---------------- ---------- -----------
EMP_HASH EMP_HASH_5 2
EMP_HASH EMP_HASH_4 4
EMP_HASH EMP_HASH_3 2
EMP_HASH EMP_HASH_2 4
EMP_HASH EMP_HASH_1 2
- openGauss 不支持為 HASH 分區(qū)表添加分區(qū)
openGauss=# alter table emp_hash add partition emp_hash_5;
ERROR: syntax error at or near ";"
- PostgreSQL and IvorySQL 的 HASH 分區(qū)表問題
-- 哈希分區(qū)有 MODULUS 總分區(qū)個(gè)數(shù)限制，當(dāng)分區(qū)個(gè)數(shù) REMAINDER 達(dá)到 MODULUS 上限時(shí)不能添加分區(qū)
postgres=# create table emp_hash_5 partition of EMP_HASH FOR VALUES WITH (modulus 4, remainder 4);
ERROR: remainder for hash partition must be less than modulus
-- 如果分區(qū)個(gè)數(shù)與 MODULUS 不相等，則插入數(shù)據(jù)會(huì)存在報(bào)錯(cuò)現(xiàn)象，
-- PostgreSQL 認(rèn)為當(dāng)前表的分區(qū)個(gè)數(shù)與 MODULUS 相等，插入數(shù)據(jù)時(shí)會(huì)計(jì)算進(jìn)去，不管真實(shí)存在多少個(gè)分區(qū)。
CREATE TABLE emp_hash (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate DATE,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY HASH (sal);
CREATE TABLE emp_hash_1 PARTITION of emp_hash FOR VALUES WITH (MODULUS 6, REMAINDER 0);
CREATE TABLE emp_hash_2 PARTITION of emp_hash FOR VALUES WITH (MODULUS 6, REMAINDER 1);
CREATE TABLE emp_hash_3 PARTITION of emp_hash FOR VALUES WITH (MODULUS 6, REMAINDER 2);
CREATE TABLE emp_hash_4 PARTITION of emp_hash FOR VALUES WITH (MODULUS 6, REMAINDER 3);
postgres=# insert into emp_hash select * from emp;
ERROR: no partition of relation "emp_hash" found for row
DETAIL: Partition key of the failing row contains (sal) = (800.00).
- INTERVAL 分區(qū)表不支持添加分區(qū)
SQL> alter table EMP_INTERVAL_DATE_YEAR add partition HIREDATE_P2 values less than (to_date('1978-01-01','yyyy-dd-mm'));
ORA-14760: ADD PARTITION is not permitted on Interval partitioned objects
openGauss=# alter table EMP_INTERVAL_DATE_YEAR add partition HIREDATE_P2 values less than (to_date('1978-01-01','yyyy-dd-mm'));
ERROR: can not add partition against interval partitioned table
截?cái)喾謪^(qū)
- 截?cái)喾謪^(qū)的方式
-- oracle and openGauss
alter table emp_range truncate partition hiredate_1979;
alter table emp_range_range truncate subpartition hiredate_1979_3000;
-- PostgreSQL and IvorySQL 創(chuàng)建子表進(jìn)行 truncate
truncate table emp_range_hiredate_1979;
truncate table emp_range_range_1979_3000;
合并分區(qū) (Oracle and openGauss)
- 合并分區(qū)的方式
-- oracle and openGauss
-- openGauss 不支持合并 LIST/HASH 分區(qū)表，不支持合并組合分區(qū)表，不支持合并 subpartition
alter table EMP_RANGE merge partitions HIREDATE_1979,HIREDATE_1980 into partition HIREDATE_1980;
alter table EMP_RANGE_RANGE merge subpartitions HIREDATE_1980_MIN,HIREDATE_1980_1000 into subpartition HIREDATE_1980_1000;
alter table EMP_LIST merge partitions DEPTNO_10,DEPTNO_20 into partition DEPTNO_10_20;
alter table EMP_INTERVAL_DATE_YEAR merge partitions SYS_P1,SYS_P2 into partition SYS_P2;
- openGauss 不支持合并 LIST/HASH 分區(qū)表
openGauss=# alter table EMP_LIST merge partitions DEPTNO_10,DEPTNO_20 into partition DEPTNO_10_20;
ERROR: can not merge LIST/HASH partition table
- openGauss 不支持合并組合分區(qū)表
openGauss=# alter table EMP_RANGE_RANGE merge partitions HIREDATE_1979,HIREDATE_1980 into partition HIREDATE_1980;
ERROR: Un-support feature
DETAIL: For subpartition table, merge partitions is not yet supported.
- openGauss 不支持合并 subpartition
openGauss=# alter table EMP_RANGE_RANGE merge subpartitions HIREDATE_1980_MIN,HIREDATE_1980_1000 into subpartition HIREDATE_1980_1000;
ERROR: syntax error at or near "subpartitions"
LINE 1: alter table EMP_RANGE_RANGE merge subpartitions HIREDATE_198...
^
- HASH 分區(qū)表不支持合并
-- oracle
SQL> alter table EMP_HASH merge partitions EMP_HASH_3,EMP_HASH_4 into partition EMP_HASH_3;
ORA-14255: table is not partitioned by Range, List, Composite Range or Composite List method
-- openGauss
openGauss=# alter table EMP_HASH merge partitions EMP_HASH_3,EMP_HASH_4 into partition EMP_HASH_3;
ERROR: can not merge LIST/HASH partition table
拆分分區(qū) (Oracle and openGauss)
- 拆分分區(qū)的方式
-- oracle
alter table emp_range split partition hiredate_1980 at (to_date('01-01-1980','dd-mm-yyyy')) into (partition hiredate_1979,partition hiredate_1980);
alter table emp_range split partition HIREDATE_MAX at (to_date('01-01-1989','dd-mm-yyyy')) into (partition hiredate_1988,partition HIREDATE_MAX);
alter table emp_list split partition deptno_10_20 values(10) into (partition deptno_10, partition deptno_20);
alter table emp_interval split partition sys_p135 at (3000) into (partition emp_interval_3000,partition emp_interval_4000);
alter table emp_interval_date_year split partition sys_p138 at (to_date('1981-01-01','yyyy-mm-dd')) into (partition sys_p137,partition sys_p138);
alter table emp_range_list split partition hiredate_max at (to_date('1989-01-01','yyyy-mm-dd')) into (partition hiredate_1988,partition hiredate_max);
alter table emp_range_list split subpartition HIREDATE_1988_DEPTNO_DEFAULT values(40) into (subpartition HIREDATE_1988_DEPTNO_40,subpartition HIREDATE_1988_DEPTNO_DEFAULT);
- openGauss 拆分分區(qū)，新分區(qū)名不能與被拆的分區(qū)名相同
openGauss=# alter table emp_range split partition HIREDATE_MAX at (to_date('01-01-1989','dd-mm-yyyy')) into (partition hiredate_1988,partition HIREDATE_MAX);
ERROR: resulting partition "hiredate_max" name conflicts with that of an existing partition
openGauss=# alter table emp_range split partition HIREDATE_MAX at (to_date('01-01-1989','dd-mm-yyyy')) into (partition hiredate_1988,partition HIREDATE_MAX2);
ALTER TABLE
- openGauss 不支持拆分 LIST 分區(qū)
openGauss=# ALTER TABLE EMP_LIST SPLIT PARTITION DEPTNO_10_20 VALUES(10) INTO (PARTITION DEPTNO_10, PARTITION DEPTNO_20);
ERROR: syntax error at or near "VALUES"
LINE 1: ALTER TABLE EMP_LIST SPLIT PARTITION DEPTNO_10_20 VALUES(10)...
^
openGauss=# ALTER TABLE EMP_LIST SPLIT PARTITION DEPTNO_10_20 AT (10) INTO (PARTITION DEPTNO_10, PARTITION DEPTNO_20);
ERROR: can not split LIST/HASH partition table
- openGauss 不支持拆分組合分區(qū)表
openGauss=# alter table emp_range_list split partition hiredate_max at (to_date('1989-01-01','yyyy-mm-dd')) into (partition hiredate_1988,partition hiredate_max);
ERROR: Un-support feature
DETAIL: For subpartition table, split partition is not supported yet.
- openGauss 居然支持拆分 subpartition，好意外，而且拆分的子分區(qū)還是 LIST 分區(qū)，名稱還是沖突的，看來對(duì) subpartition 是 LIST 分區(qū)有特殊對(duì)待？
openGauss=# alter table emp_range_list split subpartition HIREDATE_1987_DEPTNO_DEFAULT values(40) into (subpartition HIREDATE_1987_DEPTNO_40,subpartition HIREDATE_1987_DEPTNO_DEFAULT);
ALTER TABLE
openGauss=# alter table EMP_RANGE_RANGE split subpartition HIREDATE_1987_MAX at (6000) into (subpartition HIREDATE_1987_5000, subpartition HIREDATE_1987_MAX);
ERROR: resulting subpartition "hiredate_1987_max" name conflicts with that of an existing subpartition
openGauss=# alter table EMP_RANGE_RANGE split subpartition HIREDATE_1987_MAX at (6000) into (subpartition HIREDATE_1987_5000, subpartition HIREDATE_1987_MAX2);
ALTER TABLE
交換分區(qū)
- Oracle 的分區(qū)交換
-- 創(chuàng)建一個(gè)空的普通表
SQL> create table hiredate_1981_tt as select * from emp where rownum=0;
-- 執(zhí)行分區(qū)交換
SQL> alter table emp_range exchange partition hiredate_1981 with table hiredate_1981_tt;
-- 查詢交換后的普通表和分區(qū)里的數(shù)據(jù)
SQL> select * from emp_range partition(hiredate_1981);
no rows selected
SQL> select * from hiredate_1981;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
- Oracle 的組合分區(qū)交換一級(jí)分區(qū)
-- 創(chuàng)建一個(gè)空的分區(qū)表，分區(qū)內(nèi)容為原分區(qū)表的二級(jí)分區(qū)
CREATE TABLE hiredate_1982_tt
( 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 (sal)
(PARTITION hiredate_1982_min VALUES LESS THAN (1000),
PARTITION hiredate_1982_1000 VALUES LESS THAN (2000),
PARTITION hiredate_1982_2000 VALUES LESS THAN (3000),
PARTITION hiredate_1982_3000 VALUES LESS THAN (4000),
PARTITION hiredate_1982_4000 VALUES LESS THAN (5000),
PARTITION hiredate_1982_max VALUES LESS THAN (MAXVALUE));
-- 執(zhí)行分區(qū)交換
SQL> alter table EMP_RANGE_RANGE exchange partition HIREDATE_1982 with table HIREDATE_1982_tt;
-- 查詢交換后的普通表和分區(qū)里的數(shù)據(jù)
SQL> select * from HIREDATE_1982_tt;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
- Oracle 的組合分區(qū)交換一個(gè) SUBPARTITION
-- 創(chuàng)建一個(gè)空的普通表
SQL> create table HIREDATE_1981_2000_tt as select * from emp where rownum=0;
-- 執(zhí)行分區(qū)交換
SQL> alter table EMP_RANGE_RANGE exchange subpartition HIREDATE_1981_2000 with table HIREDATE_1981_2000_tt;
-- 查詢交換后的普通表和分區(qū)里的數(shù)據(jù)
SQL> select * from HIREDATE_1981_2000_tt;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
- openGauss 上沒有測(cè)試成功，不曉得啥原因
openGauss=# create table hiredate_1981_tt as select * from emp where rownum=0;
INSERT 0 0
openGauss=# alter table emp_range exchange partition hiredate_1981 with table hiredate_1981_tt;
ERROR: syntax error at or near "hiredate_1981"
LINE 1: alter table emp_range exchange partition hiredate_1981 with ...
^
openGauss=# alter table EMP_RANGE_RANGE exchange subpartition HIREDATE_1981_2000 with table HIREDATE_1981_2000_tt;
ERROR: syntax error at or near "subpartition HIREDATE_1981_2000"
LINE 1: alter table EMP_RANGE_RANGE exchange subpartition HIREDATE_1...
^
- PostgreSQL and IvorySQL 的分區(qū)交換就是先解綁子表再綁定普通表
-- 創(chuàng)建一個(gè)空的普通表
postgres=# create table hiredate_1981_tt as select * from emp limit 0;
-- 解綁子表
postgres=# ALTER TABLE emp_range DETACH PARTITION emp_range_hiredate_1981;
-- 綁定普通表
postgres=# ALTER TABLE emp_range ATTACH PARTITION hiredate_1981_tt FOR VALUES FROM ('1981-01-01') TO ('1982-01-01');
-- 看心情改名字
ALTER TABLE ... RENAME TO ...;
移動(dòng)分區(qū)
-- oracle
SQL> alter table EMP_RANGE move partition HIREDATE_1987 tablespace users;
SQL> alter table EMP_RANGE_RANGE move subpartition HIREDATE_1987_3000 tablespace users;
-- openGauss
[omm@mysql ~]$ mkdir /home/omm/tbs_users
openGauss=# create tablespace tbs_users owner omm location '/home/omm/tbs_users';
openGauss=# alter table EMP_RANGE move partition HIREDATE_1987 tablespace tbs_users;
-- PostgreSQL and IvorySQL 就是對(duì)表進(jìn)行移動(dòng)
[postgres@pgtest2 ~]$ mkdir /home/postgres/tbs_users
postgres=# create tablespace tbs_users owner postgres location '/home/postgres/tbs_users';
postgres=# ALTER TABLE emp_range_hiredate_1987 SET TABLESPACE tbs_users;
- oracle 和 openGauss 不能移動(dòng)組合分區(qū)的一級(jí)分區(qū)
SQL> alter table EMP_RANGE_RANGE move partition HIREDATE_1987 tablespace users;
ORA-14257: cannot move partition other than a Range, List, System, or Hash partition
openGauss=# alter table EMP_RANGE_RANGE move partition HIREDATE_1987 tablespace tbs_users;
ERROR: Un-support feature
DETAIL: For subpartition table, modifying tablespace is not yet supported.
- openGauss 不能移動(dòng) subpartition
openGauss=# alter table EMP_RANGE_RANGE move subpartition HIREDATE_1987_3000 tablespace tbs_users;
ERROR: syntax error at or near "subpartition HIREDATE_1987_3000"
LINE 1: alter table EMP_RANGE_RANGE move subpartition HIREDATE_1987_...
^
修改分區(qū)名稱
-- oracle and openGauss
alter table EMP_INTERVAL rename partition SYS_P133 to EMP_INTERVAL_2000;
alter table EMP_RANGE_RANGE rename partition HIREDATE_1987 to HIREDATE_1987_2;
alter table EMP_RANGE_RANGE rename subpartition HIREDATE_1987_1000 to HIREDATE_1987_1000_2;
-- PostgreSQL and IvorySQL 就是修改表名
ALTER TABLE emp_range_hiredate_1983 RENAME TO emp_range_hiredate_1983_2;
ALTER TABLE emp_range_range_1983 RENAME TO emp_range_range_1983_2;
ALTER TABLE emp_range_range_1983_3000 RENAME TO emp_range_range_1983_3000_2;
- openGauss 3.0 不支持修改組合分區(qū)的一級(jí)分區(qū)名
openGauss=# alter table EMP_RANGE_RANGE rename partition HIREDATE_1987 to HIREDATE_1987_2;
ERROR: Un-support feature
DETAIL: For subpartition table, ALTER TABLE ... RENAME PARTITION/SUBPARTITION is not yet supported.
- openGauss 不支持修改 subpartition 的名稱
openGauss=# alter table EMP_RANGE_RANGE rename subpartition HIREDATE_1987_1000 to HIREDATE_1987_1000_2;
ERROR: syntax error at or near "HIREDATE_1987_1000"
LINE 1: alter table EMP_RANGE_RANGE rename subpartition HIREDATE_198...
^
最后修改時(shí)間：2022-04-13 14:18:59
「喜歡這篇文章，您的關(guān)注和贊賞是給作者最好的鼓勵(lì)」
關(guān)注作者
【版權(quán)聲明】本文為墨天輪用戶原創(chuàng)內(nèi)容，轉(zhuǎn)載時(shí)必須標(biāo)注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權(quán)追究責(zé)任。如果您發(fā)現(xiàn)墨天輪中有涉嫌抄襲或者侵權(quán)的內(nèi)容，歡迎發(fā)送郵件至：contact@modb.pro進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，墨天輪將立刻刪除相關(guān)內(nèi)容。
