标签：00 01 1981 -- partition emp hiredate openGauss exchange

概念描述

MogDB 提供了从分区交换的功能，如单表转化到一个分区中
基本语法：ALTER TABLE...EXCHANGE PARTITION
数据库版本

测试验证

1、环境准备


miao=> select version();
version
--------------------------------------------------------------------------------------------------------------------------------------------------
(MogDB 2.1.1 build b5f25b20) compiled at 2022-03-21 14:42:30 commit 0 last mr on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)

2、创建测试表

CREATE TABLE emp_range
( empno NUMBER(8,0)
, ename VARCHAR2(10)
, job VARCHAR2(9)
, mgr NUMBER(4,0)
, hiredate DATE
, sal NUMBER(7,2)
, comm NUMBER(7,2)
, deptno NUMBER(8,0)
)
PARTITION BY RANGE (hiredate)
(PARTITION hiredate_1979 VALUES LESS THAN (TO_DATE('1980-01-01','YYYY-MM-DD')),
PARTITION hiredate_1980 VALUES LESS THAN (TO_DATE('1981-01-01','YYYY-MM-DD')),
PARTITION hiredate_1981 VALUES LESS THAN (TO_DATE('1982-01-01','YYYY-MM-DD')),
PARTITION hiredate_1982 VALUES LESS THAN (TO_DATE('1983-01-01','YYYY-MM-DD')),
PARTITION hiredate_1983 VALUES LESS THAN (TO_DATE('1984-01-01','YYYY-MM-DD')),
PARTITION hiredate_1984 VALUES LESS THAN (TO_DATE('1985-01-01','YYYY-MM-DD')),
PARTITION hiredate_1985 VALUES LESS THAN (TO_DATE('1986-01-01','YYYY-MM-DD')),
PARTITION hiredate_1986 VALUES LESS THAN (TO_DATE('1987-01-01','YYYY-MM-DD')),
PARTITION hiredate_1987 VALUES LESS THAN (TO_DATE('1988-01-01','YYYY-MM-DD')),
PARTITION hiredate_max VALUES LESS THAN (MAXVALUE))
;

 


openGauss=# insert into emp_1 select * from emp;
INSERT 0 14
openGauss=#
openGauss=#
openGauss=# select * from emp_1;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+---------------------+---------+---------+--------
7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | | 20
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30
7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | | 20
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | | 30
7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | | 10
7788 | SCOTT | ANALYST | 7566 | 1987-07-13 00:00:00 | 3000.00 | | 20
7839 | KING | PRESIDENT | | 1981-11-17 00:00:00 | 5000.00 | | 10
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30
7876 | ADAMS | CLERK | 7788 | 1987-07-13 00:00:00 | 1100.00 | | 20
7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | | 30
7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | | 20
7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | | 10
7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | | 20
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30
7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | | 20
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | | 30
7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | | 10
7788 | SCOTT | ANALYST | 7566 | 1987-07-13 00:00:00 | 3000.00 | | 20
7839 | KING | PRESIDENT | | 1981-11-17 00:00:00 | 5000.00 | | 10
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30
7876 | ADAMS | CLERK | 7788 | 1987-07-13 00:00:00 | 1100.00 | | 20
7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | | 30
7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | | 20
7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | | 10
(28 rows)

openGauss=#
openGauss=# select * from emp_range;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+-----+-----+----------+-----+------+--------
(0 rows)

3、交换分区操作


openGauss=# alter table emp_range exchange partition (hiredate_max) with table emp_1 VERBOSE;
ERROR: tables in ALTER TABLE EXCHANGE PARTITION must have the same number of indexs

错误原因: 表必须有相同数量的索引。

解决办法: 因是测试环境，删除索引。

4、问题处理

openGauss=# select * from pg_indexes where tablename = 'emp_range';
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+-----------+------------+------------------------------------------------------------------------------------------------------------------
public | emp_range | ind_emp2 | | CREATE INDEX ind_emp2 ON emp_range USING ubtree (empno) WITH (storage_type=USTORE) TABLESPACE pg_default
public | emp_range | ind_emp | | CREATE INDEX ind_emp ON emp_range USING ubtree (hiredate) LOCAL WITH (storage_type=USTORE) TABLESPACE pg_default
(2 rows)

openGauss=#
openGauss=# select * from pg_indexes where tablename = 'emp_1';
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+-----------+------------+----------
(0 rows)

openGauss=#
openGauss=# drop index ind_emp2;
DROP INDEX
openGauss=# drop index ind_emp;
DROP INDEX

5、交换分区操作


openGauss=# alter table emp_range exchange partition (hiredate_max) with table emp_1 VERBOSE;
ALTER TABLE
openGauss=# openGauss=# select * from emp_range partition (hiredate_1982);
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-------+------+---------------------+---------+------+--------
7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | | 10
7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | | 10
(2 rows)

openGauss=#

标签：00,01,1981,--,partition,emp,hiredate,openGauss,exchange
From： https://www.cnblogs.com/xinxin1222/p/18021800

MogDB 学习笔记之 --exchange partition