达梦模式对象管理-表

• 模式

在 DM 中，一个用户可以创建多个模式，一个模式中的对象(表、视图)可以被多个用户使用。

系统为每一个用户自动建立了一个与用户名同名的模式作为默认模式，用户还可以用模式定义语句建立其它模式。

语法格式：CREATE SCHEMA <模式名> [AUTHORIZATION <用户名>][<DDL_GRANT子句> {< DDL_GRANT 子句>}];

例如

CREATE SCHEMA SCHEMATEST AUTHORIZATION SYSDBA;

注意事项：

1. 在创建新的模式时，如果已存在同名的模式，或当存在能够按名字不区分大小写匹配的同名用户时（此时认为模式名为该用户的默认模式），那么创建模式的操作会被跳过，而如果后续还有 DDL 子句，根据权限判断是否可在已存在模式上执行这些 DDL 操作；

2. AUTHORIZATION <用户名>标识了拥有该模式的用户；它是为其他用户创建模式时使用的；缺省拥有该模式的用户为 SYSDBA；

3. 使用 sch_def_clause2 创建模式时，模式名与用户名相同；

4. 使用该语句的用户必须具有 DBA 或 CREATE SCHEMA 权限；

5. DM 使用 DMSQL 程序模式执行创建模式语句，因此创建模式语句中的标识符不能使用系统的保留字；

6. 定义模式时，用户可以用单条语句同时建多个表、视图，同时进行多项授权；

7. 模式一旦定义，该用户所建基表、视图等均属该模式，其它用户访问该用户所建立的基表、视图等均需在表名、视图名前冠以模式名；而建表者访问自己当前模式所建表、视图时模式名可省；若没有指定当前模式，系统自动以当前用户名作为模式名；

8. 模式定义语句中的基表修改子句只允许添加表约束；

9. 模式定义语句中的索引定义子句不能定义聚集索引；

10. 模式未定义之前，其它用户访问该用户所建的基表、视图等均需在表名前冠以建表者名；

11. 模式定义语句不允许与其它 SQL 语句一起执行；

12. 在 DIsql 中使用该语句必须以“/”结束。

 

 

 

 

• 表

 

达梦数据库中，表的数据存储方式分为行存储和列存储。行存储是以记录为单位进行存储的，数据页面中存储的是完整的若干条记录；列存储是以列为单位进行存储的，每一个列的所有行数据都存储在一起，而且一个指定的页面中存储的都是某一个列的连续数据。

 

1. 普通表

1. 使用CREATE [[GLOBAL] TEMPORARY] TABLE <表名定义> <表结构定义>;创建表，例如：

CREATE TABLE "TS1"."EMPLOYEE"

(

"EMPNO" INT NOT NULL,

"ENAME" VARCHAR(15) NOT NULL,

"JOB" VARCHAR(10),

"MGR" INT,

"HIREDATE" DATE DEFAULT CURDATE,

"SALARY" FLOAT,

"DEPTNO" TINYINT NOT NULL,

CLUSTER PRIMARY KEY("EMPNO")) STORAGE(ON "TS1", CLUSTERBTR) ;

如图，可以看到在用户TS1下创建了雇员表EMPLOYEE，主键EMPNO创建在聚集索引上

 

 

(2) 建表时可以指定表的存储信息

例如：

CREATE TABLE TS1.PERSON

( PERSONID INT IDENTITY(1,1) CLUSTER PRIMARY KEY,

SEX CHAR(1) NOT NULL,

NAME VARCHAR(50) NOT NULL,

EMAIL VARCHAR(50),

PHONE VARCHAR(25))

STORAGE

( INITIAL  5,

MINEXTENTS 5,

NEXT  2,

ON TS1,

FILLFACTOR 85);

 

建立了TS1的表PERSON，存储在TS1表空间上初始簇大小为5，最小保留簇数目为 5，下次分配簇数目为 2，填充比例为 85

 

注意：

1) 初始簇数目：指建立表时分配的簇个数，必须为整数，最小值为 1，最大值为256，缺省为 1；

2) 下次分配簇数目：指当表空间不够时，从数据文件中分配的簇个数，必须为整数，最小值为 1，最大值为 256，缺省为 1；

3) 最小保留簇数目：当删除表中的记录后，如果表使用的簇数目小于这个值，就不再释放表的空间，必须为整数，最小值为 1，最大值为 256，缺省为 1；

4) 表空间名：在指定的表空间上建表，表空间必须已存在，缺省为该用户的默认表空间；

5) 填充比例：指定存储数据时每个数据页和索引页的充满程度，取值范围从 0 到100。默认值为 0，等价于 100，表示全满填充。插入数据时填充比例的值越低，可由新数据使用的空间就越多；更新数据时填充比例的值越大，更新导致出现的页分裂的几率越大。同样，创建索引时，填充比例的值越低，可由新索引项使用的空间也就越多；

 

1. 临时表

当处理复杂的查询或事务时，由于在数据写入永久表之前需要暂时存储一些行信息或需要保存查询的中间结果，可能需要一些表来临时存储这些数据。DM 允许创建临时表来保存会话甚至事务中的数据。在会话或事务结束时，这些表上的数据将会被自动清除。

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

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

2. ON COMMIT PRESERVE ROWS：指定临时表是会话级的，会话结束时才清空表，并释放临时 B 树。

例如：

CREATE GLOBAL TEMPORARY TABLE TMP_EMP(

EMPNO INT PRIMARY KEY,

ENAME VARCHAR(15) NOT NULL,

JOB VARCHAR(10))

ON COMMIT DELETE ROWS;

建立了一个事务级别的全局临时表，将在事务提交或回滚后删除表中数据。

备注：

TEMPORARY 临时表不支持压缩 compress 功能；

GLOBAL 目前仅支持 GLOBAL 临时表，因此建临时表时是否指定GLOBAL 效果是一样的。

 

1. 外部表

外部表，是指不存在于数据库中的表。通过向达梦提供描述外部表的元数据，我们可以把一个操作系统文件当成一个只读的数据库表，就像这些数据存储在一个普通数据库表中一样来进行访问。

例如：

存在数据文件'd:\data.txt'如：

使用建表语句如：

create external table ext_table2(c1 int,c2 int,c3 int) from datafile

'd:\data.txt' parms(fields delimited by '|', records delimited by 0x0d0a);

便创建了外部表ext_table2

 

1. HUGE表

Huge File System（检查HFS）是达梦数据库实现的，针对海量数据进行分析的一种高效、简单的列存储机制。列存储表（也称为HUGE表）就是建立在HFS存储机制上的一种表。

 

HUGE表是建立在自己特有的表空间HTS（HUGE TABLESPACE，即HUGE表空间）上的。最多可创建32767个HUGE表空间，其相关信息存储在动态视图V$HUGE_TABLESPACE中。

 

这个表空间与普通的表空间不同。普通的表空间，数据是通过段、簇、页来管理的，并且以固定大小（4K、8K、16K、32K）的页面为管理单位；而HUGE表空间是通过HFS存储机制来管理的，它相当于一个文件系统。创建一个HTS，其实就是创建一个空的文件目录（系统中有一个默认HTS，目录名为HMAIN）。在创建一个HUGE表并插入数据时，数据库会在指定的HTS表空间目录下创建一系列的目录及文件。

 

(1).使用语句CREATE HUGE TABLE <表名定义> <表结构定义>[<PARTITION 子句>] [<STORAGE 子句 1>][<压缩子句>] [<日志属性>][<DISTRIBUTE 子句>]来创建HUGE表，如：

CREATE HUGE TABLE orders

(

o_orderkey INT,

o_custkey INT,

o_orderstatus  CHAR(1),

o_totalprice  FLOAT,

o_orderdate DATE,

o_orderpriority CHAR(15),

o_clerk CHAR(15),

o_shippriority INT,

o_comment VARCHAR(79) STORAGE(stat none)

)STORAGE(SECTION(65536) , FILESIZE(64), WITH DELTA, ON HTS_NAME) COMPRESS

LEVEL 9 FOR 'QUERY HIGH' (o_comment);

创建了一个名为 ORDERS 的事务型 HUGE 表，ORDERS 表的区大小为 65536行，文件大小为 64M，指定所在的表空间为 HTS_NAME，o_comment 列指定的区大小为不做统计信息，其它列（默认）都做统计信息，指定列 o_comment 列压缩类型为查询高压缩率，压缩级别为 9。

 

说明：

DM支持两种类型的HUGE表：非事务型HUGE表和事务型HUGE表

在创建表时STORAGE里指定WITH(WITHOUT) DELTA来创建事务型HUGE表（非事务型HUGE表）

 

（2）.当非事务型HUGE表在操作过程中出现系统崩溃或者断电等问题时，因为修改时采取的是直接写的策略，所以有可能会出现数据不一致的问题。为了保证数据的一致性，在操作时可以适当地做一些日志来保证数据的完整性，完整性保证策略主要是通过数据的镜像来实现的，镜像的不同程度可以实现不同程度的完整性恢复。镜像文件是放在表目录中的以.mir为扩展名的文件。DM提供三种方案：

 

LOG NONE：不做镜像。相当于不做数据一致性的保证，如果出错只能手动通过系统函数来修复表数据，当然速度是最快的，不需要额外的IO，这种选项如果用户明确知道自己的环境不会出现问题可以采用，效率最高。

 

LOG LAST：做部分镜像。但是在任何时候都只对当前操作的区做镜像，如果当前区的操作完成了，那么这个镜像也就失效了，可能会被下一个被操作区覆盖，这样做的好处是镜像文件不会太大，同时也可以保证数据是完整的。但有可能遇到的问题是：一次操作很多的情况下，有可能一部分数据已经完成，另一部分数据还没有来得及做的问题。如果用户能接受这个问题的话这个选择不失为最佳选择，这也是系统默认的选择

 

LOG ALL：全部做镜像。在操作过程中，所有被修改的区都会被记录下来，当一次操作修改的数据过多时，镜像文件有可能会很大，但好处是，能够保证操作完整性。比如，在操作过程中失败了，那么这个操作会完整的撤消，不存在上面一部分修改部分还没修改的问题。

例如：

CREATE HUGE TABLE T1

(

A INT,

B INT

) STORAGE(WITHOUT DELTA) tablespace HTS_NAME LOG ALL;

(3). AUX辅助表

对于每个HUGE表，相应地配备一个AUX辅助表来管理其数据。因为在HUGE表文件中只存储了数据，辅助表用来管理以及辅助系统用户操作这些数据，AUX辅助表是在创建HUGE表时系统自动创建的，表名为“表名AUX”，如果该HUGE表为分区表，则辅助表名为“子表名AUX”，如果该HUGE表为分区表，则辅助表名为“子表名AUX”，如果该HUGE表为分区表，则辅助表名为“子表名AUX”。

如像T1插入数据如：

insert into t1 values(2,2);

commit;

则：

 

1. 水平分区表

水平分区包括范围分区、哈希分区和列表分区三种。水平分区表的创建需要通过<PARTITION 子句>指定。

范围（RANGE）分区，按照分区列的数据范围，确定实际数据存放位置的划分方式。

列表（LIST）分区，通过指定表中的某一个列的离散值集，来确定应当存储在一起的数据。范围分区是按照某个列上的数据范围进行分区的，如果某个列上的数据无法通过划分范围的方法进行分区，并且该列上的数据是相对固定的一些值，可以考虑使用 LIST 分区。一般来说，对于数字型或者日期型的数据，适合采用范围分区的方法；而对于字符型数据，取值比较固定的，则适合于采用 LIST 分区的方法。

哈希（HASH）分区，对分区列值进行 HASH 运算后，确定实际数据存放位置的划分方式，主要用来确保数据在预先确定数目的分区中平均分布，允许只建立一个 HASH 分区。在很多情况下，用户无法预测某个列上的数据变化范围，因而无法实现创建固定数量的范围分区或 LIST 分区。在这种情况下，DM 哈希分区提供了一种在指定数量的分区中均等地划分数据的方法， 基于分区键的散列值（HASH 值）将行映射到分区中。当用户向表中写入数据时，数据库服务器将根据一个哈希函数对数据进行计算，把数据均匀地分布在各个分区中。在哈希分区中，用户无法预测数据将被写入哪个分区中。

 

(1).例如 ：

CREATE TABLE callinfo(

caller CHAR(15),

callee CHAR(15),

time  DATETIME,

duration  INT

)

PARTITION BY RANGE(time)(

PARTITION p1 VALUES LESS THAN ('2019-01-01'),

PARTITION p2 VALUES LESS THAN ('2020-01-01'),

PARTITION p3 VALUES LESS THAN ('2021-01-01'),

PARTITION p4 VALUES EQU OR LESS THAN ('2022-01-01'));

创建了一个按年分区的电话信息表

注意：不在分区中的值会无法插入，尤其是在多列分区时要尤为注意

例如插入

INSERT INTO callinfo(

caller,

callee,

time,

duration

)

VALUES

(

'TEST1',

'TEST2',

to_date('2025-01-01','yyyy-mm-dd'),

4

)

则可以看到

 

 

 

 

(2). 查询分区子表

当在分区表中执行 DML 操作时，实际上是在各个分区子表上透明地修改数据。当执行SELECT 命令时，可以指定查询某个分区上的数据。

例如：

 

(3).多级分区

建立产品表，由于产品只在几个固定的城市销售，所以可以按照销售城市对该表进行 LIST 分区,并制定按年份的RANGE分区例如

CREATE TABLE SALES(

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 ('2018-01-01'),

SUBPARTITION P12 VALUES LESS THAN ('2019-01-01'),

SUBPARTITION P13 VALUES LESS THAN ('2020-10-01'),

SUBPARTITION P14 VALUES EQU OR LESS THAN (MAXVALUE))

(

PARTITION P1 VALUES ('北京', '天津')

(

SUBPARTITION P21 VALUES LESS THAN ('2018-01-01'),

SUBPARTITION P22 VALUES LESS THAN ('2019-01-01'),

SUBPARTITION P23 VALUES LESS THAN ('2020-10-01'),

SUBPARTITION P24 VALUES EQU OR LESS THAN (MAXVALUE)

),

PARTITION P2 VALUES ('上海', '南京', '杭州'),

PARTITION P3 VALUES (DEFAULT)

);

：