MogDB 学习笔记之 --exchange partition

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

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)

一、测试分区交换

创建测试表

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)

交换分区

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

问题处理：表必须有相同数量的索引，因是测试环境，删除索引。

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

再次执行交换分区

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=#