MogDB 学习笔记之 --exchange partition
# 概念描述
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=#
```
最新文章
- Windows Azure Storage (6) Windows Azure Storage之Table
- Does Lamda expression return value?
- BFS、DFS与选课问题（拓扑排序）
- ios 使用xib时，在UIScrollView中添建内容view时，使用约束的注意
- IE6实现图片或背景的圆角效果
- webapi 获取请求参数
- Android Studio工程导入另一个工程作为lib
- python(进程池/线程池)
- Linux下SSH+Firefox
- “扩展域”与"边带权"的并查集
- laravel-阿里大于
- (转)C# WebApi 跨域问题解决方案：CORS
- if 判断语句
- PHP(Mysql/Redis)消息队列的介绍及应用场景案例--转载
- Darwin Streaming Server for Windows 安装
- C#实现软件授权，限定MAC运行（软件license管理，简单软件注册机制）
- 【版本】API NDK 系统 分辨率 统计
- C语言 · 数对
- 【Python】IO编程
- NEU 1497 Kid and Ants 思路 难度:0