1.分区表管理

1.1 分区的方法

达梦数据库 DM 支持对表进行水平分区。对于水平分区，提供以下分区方式：

范围(range) 水平分区：对表中的某些列上值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上,比如时间范围。

哈希（hash ）水平分区：通过指定分区编号来均匀分布数据的一种分区类型，通过在 I/O 设备上进行散列分区，使得这些分区大小基本一致；

列表（list ）水平分区：通过指定表中的某个列的离散值集，来确定应当存储在一起的数据。例如，可以对表上的 status 列的值在('A'，'H'，'O')放在一个分区，值在('B'，'I'，'P')放在另一个分区，以此类推；比如地区。

多级分区表：按上述三种分区方式进行任意组合，将表进行多次分区，称为多级分区表。

1.2 创建分区表

1.2.1 创建范围分区表

范围分区是按照某个列或几个列的值的范围来创建分区，当用户向表中写入数据时，数据库服务器将按照这些列上的值进行判断，将数据写入相应的分区中。

在创建范围分区时，首先要指定分区列，即按照哪些列进行分区，然后为每个分区指定数据范围。范围分区支持 MAXVALUE 范围值的使用，MAXVALUE 相当于一个比任何值都大的值。范围分区非常适用于数据按时间范围组织的表，不同的时间段的数据属于不同的分区。

1.2.1.1 创建普通表的分区表

创建一个范围分区表 callinfo，用来记录用户的 2010 年的电话通讯信息，包括主叫号码、被叫号码、通话时间和时长，并且根据季度进行分区。

创建范围分区表：

--VALUES EQU OR LESS THAN（包含上限）
CREATE TABLE callinfo(
caller CHAR(15),
callee CHAR(15),
time DATETIME,
duration INT
)
PARTITION BY RANGE(time)(
PARTITION p1 VALUES LESS THAN ('2020-04-01'),
PARTITION p2 VALUES LESS THAN ('2020-07-01'),
PARTITION p3 VALUES LESS THAN ('2020-10-01'),
PARTITION p4 VALUES EQU OR LESS THAN ('2020-12-31'));

--VALUES LESS THAN（不包含上限)
CREATE TABLE callinfo2(
caller CHAR(15),
callee CHAR(15),
time DATETIME,
duration INT
)
PARTITION BY RANGE(time)(
PARTITION p1 VALUES LESS THAN ('2020-04-01'),
PARTITION p2 VALUES LESS THAN ('2020-07-01'),
PARTITION p3 VALUES LESS THAN ('2020-10-01'),
PARTITION p4 VALUES LESS THAN ('2020-12-31'),
PARTITION p5 VALUES LESS THAN (maxvalue));

--增加存放超出范围数据的分区
alter table callinfo add partition pn values less than(maxvalue);

通过数据字典查看分区的信息：

--查看表是否为分区表属性：
select table_name,partitioned from dba_tables where table_name='CALLINFO';

行号 TABLE_NAME PARTITIONED
---------- ---------- -----------
1 CALLINFO YES
--查看表的分区类型：
select table_name,partitioning_type,partition_count from dba_part_tables where
table_name='CALLINFO';

行号 TABLE_NAME PARTITIONING_TYPE PARTITION_COUNT
---------- ---------- ----------------- --------------------
1 CALLINFO RANGE 4
--查看表每个分区的最大值：
select table_name,partition_name,HIGH_VALUE from dba_tab_partitions;

行号 TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- ---------- -------------- -----------------------------
1 CALLINFO P4 DATETIME'2020-12-31 00:00:00'
2 CALLINFO P1 DATETIME'2020-04-01 00:00:00'
3 CALLINFO P2 DATETIME'2020-07-01 00:00:00'
4 CALLINFO P3 DATETIME'2020-10-01 00:00:00'

插入数据：

insert into callinfo values('art','add','2020-01-01 10:00:00',10);
insert into callinfo values('art','add','2020-02-01 11:00:00',3);
insert into callinfo values('art','add','2020-04-01 00:00:00',5);
insert into callinfo values('srt','bdd','2020-07-01 11:00:00',5);
insert into callinfo values('brt','ddd','2020-12-31 11:00:00',5);
insert into callinfo values('brt','ddd','2020-12-31 11:00:00',5);
insert into callinfo values('brt','ddd','2020-12-31 00:00:00',5);
insert into callinfo values('crt','ddd','2021-1-1 00:00:00',5);
insert into callinfo values('crt','ddd','2021-1-1 00:00:00',5);

查看数据：

select * from callinfo partition(p1);
select * from callinfo partition(p2);
select * from callinfo partition(p3);
select * from callinfo partition(p4);

创建分区表，每个分区指定表空间：

--创建表空间：
create tablespace test1 datafile 'test101.dbf' size 64;
create tablespace test2 datafile 'test201.dbf' size 64;
create tablespace test3 datafile 'test301.dbf' size 64;
create tablespace test4 datafile 'test401.dbf' size 64;
--创建分区表：
create table callinfo3 (
caller CHAR(15),
callee CHAR(15),
time DATETIME,
duration INT
)
PARTITION BY RANGE(time)(
PARTITION p1 VALUES LESS THAN ('2020-04-01') tablespace test1,
PARTITION p2 VALUES LESS THAN ('2020-07-01') tablespace test2,
PARTITION p3 VALUES LESS THAN ('2020-10-01') tablespace test3,
PARTITION p4 VALUES EQU OR LESS THAN (maxvalue) tablespace test4);
--查看分区所在表空间：
select table_name,partition_name,HIGH_VALUE,tablespace_name from dba_tab_partitions where
table_name='CALLINFO3';

行号 TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
---------- ---------- -------------- ----------------------------- ---------------
1 CALLINFO3 P4 MAXVALUE TEST4
2 CALLINFO3 P1 DATETIME'2020-04-01 00:00:00' TEST1
3 CALLINFO3 P2 DATETIME'2020-07-01 00:00:00' TEST2
4 CALLINFO3 P3 DATETIME'2020-10-01 00:00:00' TEST3

1.2.1.2 创建堆表的分区表

所有子分区必须在同一个表空间

create table callinfo4 (
caller CHAR(15),
callee CHAR(15),
time DATETIME,
duration INT
)
PARTITION BY RANGE(time)(
PARTITION p1 VALUES LESS THAN ('2020-04-01'),
PARTITION p2 VALUES LESS THAN ('2020-07-01'),
PARTITION p3 VALUES LESS THAN ('2020-10-01'),
PARTITION p4 VALUES EQU OR LESS THAN (maxvalue)) storage(on test1,nobranch);

1.2.2 创建间隔分区表

属于范围分区表的扩展，当插入的数据超出分区范围后，按照指定的间隔规则，自动创建分区，实现分区自动管理。不允许手动添加分区。不支持 NUMTODSINTERVAL(1,'day')。

间隔分区关键字：

numtoyminterval(年、月)

numtodsinterval(日、小时、分钟、秒)

创建以月为单位的间隔分区：

CREATE TABLE interval_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p0 VALUES LESS THAN ('2008-1-1'),
PARTITION p1 VALUES LESS THAN ('2009-1-1'),
PARTITION p2 VALUES LESS THAN ('2009-7-1'),
PARTITION p3 VALUES LESS THAN ('2010-1-1') );

查看分区信息：

select partition_name,SUBPARTITION_COUNT,HIGH_VALUE from dba_tab_partitions where table_name like '%SALES%';

插入超出范围数据:

insert into interval_sales values(1,1,'2010-2-1',2000);
Commit;

再次查看分区信息：

select partition_name,SUBPARTITION_COUNT,HIGH_VALUE from dba_tab_partitions where table_name like '%SALES%';

创建以年为间隔的分区:

CREATE TABLE interval_sales_new
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, 'YEAR'))
( PARTITION p0 VALUES LESS THAN ('2008-1-1'),
PARTITION p1 VALUES LESS THAN ('2009-1-1'),
PARTITION p2 VALUES LESS THAN ('2010-1-1'),
PARTITION p3 VALUES LESS THAN ('2011-1-1') );

1.2.3 创建list分区表

范围分区是按照某个列上的数据范围进行分区的，如果某个列上的数据无法通过划分范围的方法进行分区，并且该列上的数据是相对固定的一些值，可以考虑使用 LIST 分区。

一般来说，对于数字型或者日期型的数据，适合采用范围分区的方法；而对于字符型数据，取值比较固定的，则适合于采用 LIST 分区的方法。

例如，创建一个产品销售记录表 sales，记录产品的销量情况。由于产品只在几个固定的城市销售，所以可以按照销售城市对该表进行分区。

创建list分区表：

--创建list分区表
CREATE TABLE sales(
sales_id INT,
saleman CHAR(20),
saledate DATETIME,
city CHAR(10)
)
PARTITION BY LIST(city)
(
PARTITION p1 VALUES ('北京', '天津'),
PARTITION p2 VALUES ('上海', '南京', '杭州'),
PARTITION p3 VALUES ('武汉', '长沙'),
PARTITION p4 VALUES ('广州', '深圳')
);
--插入数据
insert into sales VALUES(4,'CCCCCC','2022-03-14','西安');
--增加存放超出列表数据的分区
alter table sales add partition pn values(default);


--通过指定 default 来存放超出列表的数据
CREATE TABLE sales1(
sales_id INT,
saleman CHAR(20),
saledate DATETIME,
city CHAR(10)
)
PARTITION BY LIST(city)
(
PARTITION p1 VALUES ('北京', '天津'),
PARTITION p2 VALUES ('上海', '南京', '杭州'),
PARTITION p3 VALUES ('武汉', '长沙'),
PARTITION p4 VALUES ('广州', '深圳'),
PARTITION p5 VALUES (default)
);

1.2.4 创建hash分区表

在很多情况下，用户无法预测某个列上的数据变化范围，因而无法实现创建固定数量的范围分区或 LIST 分区。

在这种情况下，DM 哈希分区提供了一种在指定数量的分区中均等地划分数据的方法，基于分区键的散列值将行映射到分区中。当用户向表中写入数据时，数据库服务器将根据一个哈希函数对数据进行计算，把数据均匀地分布在各个分区中。在哈希分区中，用户无法预测数据将被写入哪个分区中。

现在重新考虑产品销售表的例子。如果销售城市不是相对固定的，而是遍布全国各地，这时很难对表进行 LIST 分区。如果为该表进行哈希分区，可以很好地解决这个问题。

CREATE TABLE sales01(
sales_id INT,
saleman CHAR(20),
saledate DATETIME,
city CHAR(10)
)
PARTITION BY HASH(city)(
PARTITION p1,
PARTITION p2,
PARTITION p3,
PARTITION p4
);

--如果不需指定分区表名，可以通过指定哈希分区个数来建立哈希分区表。
CREATE TABLE sales02(
sales_id INT,
saleman CHAR(20),
saledate DATETIME,
city CHAR(10)
)
PARTITION BY HASH(city)
PARTITIONS 4 STORE IN (test1, test2, test3, test4);

**说明：**PARTITIONS 后的数字表示哈希分区的分区数，STORE IN 子句中指定了哈希分区依次使用的表空间。使用这种方式建立的哈希分区表分区名是匿名的，DM 统一使用DMHASHPART+分区号（从 0 开始）作为分区名。

1.2.5 创建组合分区表

在很多情况下，经过一次分区并不能精确地对数据进分类，这时需要多级分区表。

例如，创建一个产品销售记录表 sales，记录产品的销量情况。由于产品需要按地点和销售时间进行统计，则可以对该表进行 LIST-RANGE 分区。

创建组合分区：

--创建list-rang组合分区
CREATE TABLE SALES03(SALES_ID INT,SALEMAN CHAR(20),SALEDATE DATETIME,CITY CHAR(10))
PARTITION BY LIST(CITY) --主分区
SUBPARTITION BY RANGE(SALEDATE) --子分区
SUBPARTITION TEMPLATE(   --子分区模板
   SUBPARTITION P11 VALUES LESS THAN ('2012-04-01'),
   SUBPARTITION P12 VALUES LESS THAN ('2012-07-01'),
   SUBPARTITION P13 VALUES LESS THAN ('2012-10-01'),
   SUBPARTITION P14 VALUES EQU OR LESS THAN (MAXVALUE))
(PARTITION P1 VALUES ('北京', '天津')
  (SUBPARTITION P11_1 VALUES LESS THAN ('2012-10-01'),
   SUBPARTITION P11_2 VALUES EQU OR LESS THAN (MAXVALUE)),
PARTITION P2 VALUES ('上海', '南京', '杭州'),
PARTITION P3 VALUES (DEFAULT));
--插入数据
insert into SALES03 values(1,'AAA','2012-05-01','北京');
insert into SALES03 values(1,'BBB','2012-05-01','武汉');

1.3 分区维护

1.3.1 增加分区

ALTER TABLE callinfo ADD PARTITION p5 VALUES LESS THAN ('2021-4-1');

1.3.2 删除分区

ALTER TABLE callinfo DROP PARTITION p1;

1.3.3 合并分区

要想将两个范围分区的内容融合到一个分区，就要使用 ALTER TABLE MERGE PARTITION语句。

ALTER TABLE callinfo MERGE PARTITIONS p3, p4 into partition p3_4;

1.3.4 拆分分区

ALTER TABLE 语句的 SPLIT PARTITION 子句被用于将一分区中的内容重新划分成两个新的分区。当一个分区变得太大以至于要用很长时间才能完成备份、恢复或维护操作时，就应考虑做分割分区的工作，还可以用 SPLIT PARTITION 子句来分重新划分 I/O 负载。

--拆分分区会导致数据的重组和分区索引的重建
ALTER TABLE callinfo SPLIT PARTITION p3_4 AT ('2020-9-30') INTO (PARTITION p3, PARTITION p4);

1.3.5 交换分区

CREATE TABLE callinfo_2020Q2(
caller CHAR(15),
callee CHAR(15),
time DATETIME,
duration INT
);
ALTER TABLE callinfo EXCHANGE PARTITION p2 WITH TABLE callinfo_2020Q2;

2.临时表管理

临时表 ON COMMIT 关键词指定表中的数据是事务级还是或会话级的，默认情况下是事务级的。

1.on commit delete rows：指定临时表是事务级的，每次事务提交或回滚之后，表中所有数据都被删除；

2.on commit preserve rows：指定临时表是会话级的，会话结束时才清空表，并释放临时B树。

2.1 创建事务级临时表

创建事务级临时表：

--创建临时表
CREATE GLOBAL TEMPORARY TABLE TMP_EMP(
EMPNO INT PRIMARY KEY,
ENAME VARCHAR(15) NOT NULL,
JOB VARCHAR(10))
ON COMMIT DELETE ROWS;
--插入数据
insert into tmp_emp values(1,'LILY','DBA');
insert into tmp_emp values(2,'TEST','DBA');
--未提交查询
select * from tmp_emp;
行号 EMPNO ENAME JOB
---------- ----------- ----- ---
1 1 LILY DBA
2 2 test DBA
--提交后查询
commit;
select * from tmp_emp;
未选定行

查询临时表：

select t.TABLE_NAME, t.TABLESPACE_NAME, t.DURATION from USER_TABLES t where
t.TEMPORARY='Y';

2.2 创建会话级临时表

创建会话级临时表：

--创建临时表
CREATE GLOBAL TEMPORARY TABLE TMP_EMP2(
EMPNO INT PRIMARY KEY,
ENAME VARCHAR(15) NOT NULL,
JOB VARCHAR(10))
ON COMMIT PRESERVE ROWS;
--插入数据
insert into tmp_emp2 values(1,'LILY','DBA');
insert into tmp_emp2 values(2,'TEST','DBA');
--未提交查询
select * from tmp_emp2;
行号     EMPNO       ENAME JOB
---------- ----------- ----- ---
1          1           LILY  DBA
2          2           TEST  DBA
--提交后查询
commit;
select * from tmp_emp2;

行号     EMPNO       ENAME JOB
---------- ----------- ----- ---
1          1           LILY  DBA
2          2           TEST  DBA