达梦数据库--DM8模式对象创建和管理（详解）

disclipline

已于 2023-05-26 11:55:57 修改

阅读量1.9k

点赞数

分类专栏：运维达梦国产数据库文章标签：数据库运维

于 2023-05-25 17:49:37 首次发布

本文链接：https://blog.csdn.net/disclipline/article/details/130872103

版权

达梦同时被 3 个专栏收录

13 篇文章 0 订阅

订阅专栏

国产数据库

13 篇文章 0 订阅

订阅专栏

运维

12 篇文章 0 订阅

订阅专栏

文章目录

- DM模式对象创建和管理
- 模式对象介绍：
1. 表；
二．管理索引
三. 管理触发器
四. 管理视图序列和同义词

DM模式对象创建和管理

模式对象介绍：

1. 表；

一．管理表

1.1. 管理表的准则:

1.2 表的类型

1.2.1普通表：

1.2.2 临时表

1.2.3 堆表

1.2.4 分区表

1.2.5 HUGE表

1.2.6外部表

1.3 表操作

1.3.1创建表

1.3.2 更改表

1.3.3 删除表

1.3.4清空表

1.3.5 查看表信息

1.3.7 查看自增列信息

1.3.8 查看表的空间使用情况

二．管理索引

2.1 管理索引准则

2.2索引分类：

2.3 索引操作

2.3.1创建索引

2.3.2重建索引

2.3.3 删除索引

2.4 查看索引信息

2.5 创建索引注意事项

三. 管理触发器

3.1 触发器的使用

3.2 触发器的类型

3.2.1 表级触发器

3.2.2 时间触发器

3.2.3 事件触发器

四. 管理视图序列和同义词

4.1管理视图

4.1.1 视图定义

4.1.2 创建视图

4.1.2 视图的更新和编译

4.1.3 视图的删除

4.2 管理序列

模式对象介绍：

用户的模式(SCHEMA）指的是用户账号拥有的对象集，在概念上可将其看作是包含表、视图、索引和权限定义的对象。在 DM 中，一个用户可以创建多个模式，一个模式中的对象（表、视图等）可以被多个用户使用。模式不是严格分离的，一个用户可以访问他所连接的数据库中有权限访问的任意模式中的对象。

DM 模式可以通过 SQL 语句进行操作。DM 模式主要包含以下的模式对象：

1. 表； 
2. 视图； 
3. 索引； 
4. 触发器； 
5. 存储过程/函数； 
6. 序列； 
7. 全文索引； 
8. 包； 
9. 同义词； 
10. 类； 
11. 外部链接

一．管理表
1.1. 管理表的准则:
设计表：

规范化表，估算并校正表结构，使数据冗余达到最小；
为每个列选择合适的数据类型，是否允许为空等，并根据实际情况判断是否需要对
列进行加密或压缩处理；
建立合适的完整性约束
建立合适的聚集索引。每个表（列存储表，堆表除外）都含一个聚集索引，默认以 ROWID 建立，而建立合适的聚集索引，可以有效加快表的检索效率；
根据实际需要，建立合适类型的表。DM 支持的表类型包括普通表、临时表、水平分区表、堆表和列存储表。

指定表的空间上限
在创建表时指定 SPACE LIMIT 子句，可以对表的存储空间指定上限。DM 支持对表的存储空间指定大小，单位是 MB，即表的大小可由管理员指定，便于表的规模管理。当表的所有索引所占用的存储空间超过指定大小时，表将不能再新增数据。
指定表的存储位置
创建表时，在 STORAGE 子句中，可对表指定存储的表空间。如果没有指定，则该表将创建在用户的默认表空间中。

在创建表时，通过指定合适的表空间，有以下优点：

提高数据库系统的性能，因为不同的数据库表可能对应不同的数据文件，可减少对相同文件的竞争；
减少数据库管理的时间，数据库表分布在不同的表空间中，即使一个表空间损坏，也不影响其他表空间上数据库表的正常访问

1.2 表的类型
1.2.1普通表：
普通表都是以 B 树形式存放的，ROWID 都是逻辑的 ROWID，即从 1 一直增长下去。在并发情况下，每次插入过程中都需要逻辑生成 ROWID，这样影响了插入数据的效率；对于每一条数据都需要存储 ROWID 值，也会花费较大的存储空间。使用CREATE TABLE创建，通过STORAGE关键字指定存储参数。
1.2.2 临时表
当处理复杂的查询或事务时，由于在数据写入永久表之前需要暂时存储一些行信息或需
要保存查询的中间结果，可能需要一些表来临时存储这些数据。DM 允许创建临时表来保存会话甚至事务中的数据。在会话或事务结束时，这些表上的数据将会被自动清除。
临时表是当事务完成或者会话结束时清空表中数据的一种表，创建在临时表空间上，表结构不会删除，临时表 ON COMMIT 关键词指定表中的数据是事务级还是会话级的：
1）、ON COMMIT DELETE ROWS：指定临时表是事务级的，每次事务提交或回滚之后，表中所有数据都被删除；
2）、ON COMMIT PRESERVE ROWS：指定临时表是会话级的，会话结束时才清空表，并释放临时 B 树。

DM 临时表支持以下功能：

在临时表中，会话可以像普通永久表一样更新、插入和删除数据；
临时表的 DML 操作产生较少的 REDO 日志；
临时表支持建索引，以提高查询性能；
在一个会话或事务结束后，数据将自动从临时表中删除；
不同用户可以访问相同的临时表，每个用户只能看到自己的数据；
临时表的数据量很少，意味着更高效的查询效率；
临时表的表结构在数据删除后仍然存在，便于以后的使用；
临时表的权限管理跟普通表一致。
临时表 ON COMMIT 关键词指定表中的数据是事务级还是或会话级的，默认情况下是事
务级的。

1.2.3 堆表

在这里插入图片描述

采用了物理 ROWID 形式的堆表，DM 服务器内部对聚集索引进行了调整，没有采用传统B 树结构，取而代之的是“扁平 B 树”，数据页都是通过链表形式存储。为支持并发插入，扁平 B 树可以支持最多 128 个数据页链表（最多 64 个并发分支和最多 64 个非并发分支），在 B 树的控制页中记录了所有链表的首、尾页地址。对于非并发分支，如果分支数有多个，即存在多个链表，则不同的用户登录系统之后，会依据其事务 ID 号，随机选择一条链表来对堆表进行插入操作。对于并发分支，则不同用户会选择不同的分支来进行插入，如果存在多个用户选择了同一条分支的情况，才需要等待其他用户插入结束并释放锁之后才能进行插入。在并发情况下，不同用户可以在不同的链表上进行插入，效率得到较大提升。

1.2.4 分区表
为提高数据库在大数据量读写操作和查询时的效率，达梦数据库提供了对表和索引进行分区的技术，把表和索引等数据库对象中的数据分割成小的单位，分别存放在一个个单独的段中，用户对表的访问转化为对较小段的访问，以改善大型应用系统的性能。达梦提供了水平分区的方式，该方式包含了范围（range）、哈希（HASH）和列表（list）、间隔分区（interval)四种方法。分区的方法：
（1）范围（range）分区：对表中的某些列上值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上。
（2）哈希（hash）分区：通过指定分区编号来均匀分布数据的一种分区类型，通过在I/O设备上进行散列分区，使得这些分区大小基本一致。
（3）列表（list）分区：通过指定表中的某个列的离散值集，来确定应当存储在一起的数据。
（4）间隔（interval）分区：间隔分区其实和范围分区是一样的，但是它比范围分区要更加高级。范围分区需要我们手动的去分配每个范围区间，所以使用起来不是那么灵活。例如某公司的数据，想要以年度为分区范围，每年都要手动新增一个分区，比较麻烦。而间隔分区就能完美的解决此类问题。
（5）多级分区表：上述三种方式的任意组合。
使用 partition 子句指定分区方式和分区列，以及分区的名字等信息。

1.2.5 HUGE表
在这里插入图片描述

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

这个表空间与普通表空间不同。普通表空间，数据是通过段、簇、页来管理的，并且以固定大小的页面为管理单位；而HUGE 表空间是通过 HFS 存储机制来管理的，它相当于一个文件系统。
1、HUGE FILE SYSTEM：是达梦数据库自有的，针对海量数据进行分析一种高效的表，列存储表是建立在HFS（分层文件管理系统）上的。
2、HUGE表建立在自己特有的表空间上HUGE表空间，默认在HMAIN表空间上。
3、相关信息存在V$huge_tablespace中。最多可以创建32767个huge表空间。
4、创建HUGE表时，根据with和without 来确定表是非事务还是事务型的HUGE表。
5、HUGE表与普通行表一样，可以进行增删改操作。
6、但HUGE表的删除与更新操作的效率会比行表低一些，并发操作性能会比行差一些。
HUGE表的一些限制：
1、支持定义NULL，NOT NULL, UNIQUE ,PRIMARY KEY
2、HUGE表不建立聚族索引，允许建立二级索引，不支持位图索引，其中unique不检查唯一性。
3、不支持表空间限制
4、不支持大字段列
5、不支持全文索引
6、不支持建立触发器。

1.2.6外部表
外部表是表的数据不会放在数据库里面，会放在操作系统的文件里面。通过数据库外部表可以查看外部文件的数据。
1.建立外部表时，不会产生页、簇、段等存储结构。
2.只能与表相关的定义放在数据库字典中，不能对外部表的内容进行修改（update、instert、 delete）
3.不能对外部表创建索引。
4.外部表可以通过SQL语句解码器来完成。而不需要将外部表装载到数据库中。

1.3 表操作
1.3.1创建表
1.3.1.1创建普通表：
DM8对普通表和索引提供的参数
在这里插入图片描述

示例：
例如：在 users 表空间上建立了 employee 表

CREATE TABLE EMPLOYEE ( 
EMPNO INT PRIMARY KEY, 
ENAME VARCHAR(15) NOT NULL, 
JOB VARCHAR(10), 
MGR INT
CONSTRAINT EMP_FKEY REFERENCES EMPLOYEE(EMPNO), 
HIREDATE DATE DEFAULT (CURDATE), 
SALARY FLOAT, 
DEPTNO TINYINT NOT NULL 
CONSTRAINT DEPT_FKEY REFERENCES DEPT(DEPTNO))
STORAGE (
INITIAL 50,            #初始簇数目为50
NEXT 50,              #下次分配簇数目为50
MINEXTENTS 10,        #最小保留簇为10
FILLFACTOR 80,        #填充因子为 80%
ON USERS);			 #指定表空间users

注意：
当填充因子取值低，则需要更多的页来存储数据，因而读取范围大，会影响性能。而当填充因子取值高，则在更新数据时可能造成大量的页拆分，页拆分需要消耗较多 CPU 和 I/O资源，同样会影响性能。原则上，在只读表上应该设置填充因子高，而有大量更新的表上应该设置较低的值。默认情况下，DM 新建的表和索引的填充因子是 100，可根据实际情况设置合适的填充因子大小。
也可以使用CREATE TABLE AS SELECT从已存在的表构建新表，在dm.ini中参数CTAB_SEL_WITH_CONS=1为 1 进行原始表上约束的拷贝，列上能拷贝的约束包括默认值属性、自增属性、非空属性以及加密属性，表上能拷贝的约束包括唯一约束、PK 约束以及 CHECK约束。

1.3.1.2 创建临时表
下面的例子创建一个事务级的临时表,每次事务提交或回滚之后，表中所有数据都被删除：

CREATE GLOBAL TEMPORARY TABLE TMP_EMP( 
EMPNO INT 
PRIMARY KEY, 
ENAME VARCHAR(15) 
NOT NULL, 
JOB VARCHAR(10)) 
ON COMMIT DELETE ROWS;

1.3.1.3 创建堆表
SQL 建表语句指定创建表时可以在 STORAGE 选项中指定需要创建的表形式，与堆表创建形式相关的关键字有三个，分别是 NO BRANCH、BRANCH、CLUSTERBTR。
NOBRANCH：如果指定为 NOBRANCH，则创建的表为堆表，并发分支个数为 0，非并发分支个数为 1；
BRANCH(n，m)：如果为该形式，则创建的表为堆表，并发分支个数为 n，非并发个数为 m；
BRANCH n：指定创建的表为堆表，并发分支个数为 n，非并发分支个数为 0；
CLUSTERBTR：创建的表为索引组织表。

示例：

create table "userheap"(
id int primary key,
sex int,
name varchar(15))
storage(
branch (2,4));  #并发分支为2，非并发个数为4

1.3.1.4 创建分区表
（1）创建范围分区表
范围分区是按照某个列或几个列的值的范围来创建分区，当用户向表中写入数据时，数
据库服务器将按照这些列上的值进行判断，将数据写入相应的分区中。
在创建范围分区时，首先要指定分区列，即按照哪些列进行分区，然后为每个分区指定
数据范围。范围分区支持 MAXVALUE 范围值的使用，MAXVALUE 相当于一个比任何值都大的值。范围分区非常适用于数据按时间范围组织的表，不同的时间段的数据属于不同的分区。
例如，以下语句创建一个范围分区表 callinfo，用来记录用户的 2010 年的电话通讯
信息，包括主叫号码、被叫号码、通话时间和时长，并且根据季度进行分区。

CREATE TABLE callinfo( 
caller CHAR(15), 
callee CHAR(15), 
time 
DATETIME, 
duration 
INT 
) 
PARTITION BY RANGE(time)( 
PARTITION p1 VALUES LESS THAN ('2010-04-01'), 
PARTITION p2 VALUES LESS THAN ('2010-07-01'), 
PARTITION p3 VALUES LESS THAN ('2010-10-01'), 
PARTITION p4 VALUES EQU OR LESS THAN ('2010-12-31') --'2010-12-31'也可替换 
为 MAXVALUE 
);

值得注意的是，MAXVALUE 之间无法比较大小。如下所示：

create table callinfo ( 
caller CHAR(15),管理分区表和分区索引 
143 
callee CHAR(15), 
time 
DATETIME, 
duration 
INT 
) 
partition by range(caller, callee) 
( 
partition p1 values less than ('a', 'b'), 
partition p2 values less than (maxvalue, 'd'), 
partition p3 values less than (maxvalue,maxvalue)

在创建分区表时，首先通过―PARTITION BY RANGE‖子句指定分区的类型为范围分区，然后在这个子句之后指定一个或多个列作为分区列，如 callinfo 的 time 字段。
表中的每个分区都可以通过―PARTITION‖子句指定一个名称。并且每一个分区都有一个范围，通过―VALUES LESS THAN‖子句可以指定上界，而它的下界是前一个分区的上界。
如分区 p2 的 time 字段取值范围是[‘2010-04-01’, ‘2010-07-01’)。如果通过
―VALUES EQU OR LESS THAN‖指定上界，即该分区包含上界值，如分区 p4 的 time 字段取值范围是[‘2010-10-01’, ‘2010-12-31’]。另外，可以对每一个分区指定
storage 子句，不同分区可存储在不同表空间中。
如果分区表包含多个分区列，采用多列比较方式定位匹配分区。首先，比较第一个分区列值，如果第一列值在范围之内，就以第一列为依据进行分区；如果第一列值处于边界值，那么需要比较第二列的值，根据第二列为依据进行分区；如果第二列的值也处于边界值，需要继续比较后续分区列值，以此类推，直到确定目标分区为止。
（2）创建 LIST 分区表
范围分区是按照某个列上的数据范围进行分区的，如果某个列上的数据无法通过划分范围的方法进行分区，并且该列上的数据是相对固定的一些值，可以考虑使用 LIST 分区。一般来说，对于数字型或者日期型的数据，适合采用范围分区的方法；而对于字符型数据，取值比较固定的，则适合于采用 LIST 分区的方法。
例如，创建一个产品销售记录表 sales，记录产品的销量情况。由于产品只在几个固定的城市销售，所以可以按照销售城市对该表进行分区。

CREATE TABLE sales( 
sales_id 
INT,管理分区表和分区索引 
144 
saleman CHAR(20), 
saledate 
DATETIME, 
city 
CHAR(10) 
) 
PARTITION BY LIST(city)( 
PARTITION p1 VALUES ('北京', '天津'), 
PARTITION p2 VALUES ('上海', '南京', '杭州'), 
PARTITION p3 VALUES ('武汉', '长沙'), 
PARTITION p4 VALUES ('广州', '深圳') 
);

在创建 LIST 分区时，通过―PARTITION BY LIST‖子句指定对表进行 LIST 分区，
然后在每个分区中分区列的取值通过 VALUES 子句指定。当用户向表插入数据时，只要分区列的数据与 VALUES 子句指定的数据之一相等，该行数据便会写入相应的分区子表中。注意的是，LIST 分区的分区键必须唯一。
（3）创建哈希分区表
在很多情况下，用户无法预测某个列上的数据变化范围，因而无法实现创建固定数量的范围分区或 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 (ts1, ts2, ts3, ts4);

PARTITIONS 后的数字表示哈希分区的分区数，STORE IN 子句中指定了哈希分区依次使用的表空间。使用这种方式建立的哈希分区表分区名是匿名的，DM 统一使用 DMHASHPART+分区号（从 0 开始）作为分区名。例如，需要查询 sales 第一个分区的数据，可执行以下语句：

SELECT * FROM sales02 PARTITION (dmhashpart0);

（4）创建多级分区表
在很多情况下，经过一次分区并不能精确地对数据进分类，这时需要多级分区表。
例如，创建一个产品销售记录表 sales，记录产品的销量情况。由于产品需要按地点和销售时间进行统计，则可以对该表进行 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 ('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) 
);

在创建多级分区表时，指定了子分区模板，同时子分区 P1 自定义了子分区描述 P11_1
和 P11_2。P1 有两个子分区 P11_1 和 P11_2。而子分区 P2 和 P3 有四个子分区 P11、P12、 P13 和 P14。 DM 支持最多八层多级分区。
下面给出一个三级分区的例子，更多级别的分区表的建表语句语法类推。

CREATE TABLE STUDENT(NAME VARCHAR(20), AGE INT, SEX VARCHAR(10) CHECK (SEX 
IN ('MAIL','FEMAIL')), GRADE INT CHECK (GRADE IN (7,8,9))) 
PARTITION BY LIST(GRADE)管理分区表和分区索引 
SUBPARTITION BY LIST(SEX) SUBPARTITION TEMPLATE 
( 
SUBPARTITION Q1 VALUES('MAIL'), 
SUBPARTITION Q2 VALUES('FEMAIL') 
), 
SUBPARTITION BY RANGE(AGE) SUBPARTITION TEMPLATE 
( 
SUBPARTITION R1 VALUES LESS THAN (12), 
SUBPARTITION R2 VALUES LESS THAN (15), 
SUBPARTITION R3 VALUES LESS THAN (MAXVALUE) 
) 
( 
PARTITION P1 VALUES (7), 
PARTITION P2 VALUES (8), 
PARTITION P3 VALUES (9) 
);

1.3.1.5 创建HUGE表
在这里插入图片描述

下面是一个综合的创建 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。

1.3.2 更改表
想更改的表如果在所属的模式中，用户必须具有 ALTER TABLE 数据库权限；若在其他模式中，用户必须有 ALTER ANY TABLE 的数据库权限。
通过更改表，用户可以对数据库中的表作如下修改：

添加或删除列，或修改现有的列的定义（列名、数据类型、长度、默认值）。其中，
对于添加列，当设置 INI 参数 ALTER_TABLE_OPT 为 1 时，添加列采用查询插入
实现，可能会导致 ROWID 的改变；ALTER_TABLE_OPT 为 2 时，系统开启快速加
列功能，对于没有默认值或者默认值为 NULL 的新列，系统内部会标记为附加列，
能够达到瞬间加列的效果，此时记录 ROWID 不会改变，若有默认值且默认值不为
NULL，则默认值的存储长度不能超过 4000 字节，此时仍旧采取查询插入实现；
ALTER_TABLE_OPT 为 3 时，系统会开启快速加列功能，允许指定新增列的默认值，
系统会为该列设置附加列标记，查询表中已存在的数据时，会自动为记录设置追加
列默认值，此时记录 ROWID 不会改变；
添加、修改或删除与表相关的完整性约束；
重命名一个表；
启动或停用与表相关的完整性约束；
启动或停用与表相关的触发器；
修改表的 SPACE LIMIT；
增删自增列

1.3.3 删除表
当一个表不再使用时，可以将其删除。删除表时，将产生以下结果：

表的结构信息从数据字典中删除，表中的数据不可访问；
表上的所有索引和触发器被一起清除；
所有建立在该表上的同义词、视图和存储过程变为无效；
所有分配给表的簇标记为空闲，可被分配给其他的数据库对象。
一般情况下，普通用户只能删除自己模式下的表。若要删除其他模式下的表，则必须具
有 DROP ANY TABLE 数据库权限。
以下语句可删除 employee 表：
DROP TABLE employee;
删除不存在的表会报错。若指定 IF EXISTS 关键字，删除不存在的表，不会报错，如：
DROP TABLE IF EXISTS employee;
如果要删除的表被其他表引用，即其他表的外键引用了表的任何主键或唯一键，则需要
在 DROP TABLE 语句中包含 CASCADE 选项，如：
DROP TABLE employee CASCADE;

1.3.4清空表
有些情况下，当表的数据不再使用时，需要删除表的所有行，即清空该表。DM8 支持以
下方式来删除表中的所有的行：

使用 DELETE 语句；
使用 DROP 和 CREATE 语句；
使用 TRUNCATE 语句。
使用 DELETE
使用 DELETE 语句能删除表中的行。例如，下面的语句删除 employee 表中的所有行：
DELETE FROM employee;
但是，使用 DELETE 清空表，当表有很多行时，会消耗很多系统资源。因为，DELETE
操作需要 CPU 时间，并且会产生大量的 REDO 日志和 UNDO 记录。另外，如果表上关联了元组级触发器，每删除一行，就会启动一次触发器。这都需要大量的系统资源。
使用 DROP 和 CREATE
使用 DROP 删除一个表，然后创建一个同名的表，也可以达到清空表的效果。例如，下
面的语句先删除 employe 表，之后再重新创建。
DROP TABLE EMPLOYEE;
CREATE TABLE EMPLOYEE(…);
当删除和重新创建表时，所有与之相关联的索引、完整性约束和触发器也被删除。同样，
所有针对被删除表的授权也会被删除。
使用 TRUNCATE
使用 TRUNCATE 语句能删除表中的所有行。例如，下面的语句清空 employee 表。
TRUNCATE TABLE EMPLOYEE;
TRUNCATE 语句为我们提供了一种快速、有效地删除表所有行的方法。并且 TRUNCATE
是一个 DDL 语句，不会产生任何回滚信息。执行 TRUNCATE 会立即提交，而且不能回滚。
TRUNCATE 语句并不影响与被删除的表相关联的任何结构、约束、触发器或者授权。另
外，DM 数据库 TRUNCATE 表后，原来分配给该表的空间会被释放，供其他数据库对象使用，大大提高空间的利用效率。
一般情况下，普通用户只能 TRUNCATE 自己模式下的表。若要 TRUNCATE 其他模式下
的表，则必须具有 DROP ANY TABLE 数据库权限。
如果要清空的表被其他表引用，即其他表的外键引用了表的任何主键或唯一键，并且子
表不为空或子表的外键约束未被禁用，则不能 TRUNCATE 该表。

1.3.5 查看表信息
1 查看表定义
创建表后，可以通过 SP_TABLEDEF 系统过程查看表的定义。

CALL SP_TABLEDEF('SYSDBA', 'EMPLOYEE');

DM 通过提供的 TABLEDEF 函数来显示当前表的定义。当表多次进行 ALTER TABLE 后，
显示的表定义将是最后一次修改后的建表语句。

SQL> CALL SP_TABLEDEF('SYSDBA', 'EMPLOYEE');

2 查看自增列信息
DM 支持 INT 和 BIGINT 两种数据类型的自增列，并提供以下函数查看表上自增列的当
前值、种子和增量等信息：

IDENT_CURRENT：获得表上自增列的当前值；
IDENT_SEED：获得表上自增列的种子信息；
IDENT_INCR：获得表上自增列的增量信息。

CREATE TABLE IDENT_TABLE ( 
C1 INT IDENTITY(100, 100), 
C2 INT ); 
SELECT IDENT_CURRENT('SYSDBA.IDENT_TABLE'); 
SELECT IDENT_SEED('SYSDBA.IDENT_TABLE'); 
SELECT IDENT_INCR('SYSDBA.IDENT_TABLE');

3 查看表的空间使用情况
DM 使用段、簇和页实现数据的物理组织。DM 支持查看表的空间使用情况，包括：

TABLE_USED_SPACE：已分配给表的页面数；
TABLE_USED_PAGES：表已使用的页面数。
例如：

CREATE TABLE SPACE_TABLE ( 
C1 INT, 
C2 INT ); 
SELECT TABLE_USED_SPACE('SYSDBA','SPACE_TABLE'); 
SELECT TABLE_USED_PAGES('SYSDBA','SPACE_TABLE');

二．管理索引
2.1 管理索引准则
DM8 提供了几种最常见类型的索引，对不同场景有不同的功能，它们是：

聚集索引：每一个普通表有且只有一个聚集索引；
唯一索引：索引数据根据索引键唯一；
函数索引：包含函数/表达式的预先计算的值；
位图索引：对低基数的列创建位图索引；
位图连接索引：针对两个或者多个表连接的位图索引，主要用于数据仓库中；
全文索引：在表的文本列上而建的索引。具体内容请参考第 19 章。
索引在逻辑上和物理上都与相关的表的数据无关，作为无关的结构，索引需要存储空间。
创建或删除一个索引，不会影响基本的表、数据库应用或其他索引。当插入、更改和删除相
关的表的行时，DM8 会自动管理索引。如果删除索引，所有的应用仍继续工作，但访问以前被索引了的数据时速度可能会变慢

2.2索引分类：
1、聚集索引：每一个普通表有且只有一个聚集索引。注意：在建表的时候要把主键选好，尽量不要在对数据量非常大的表，建立聚集索引。
2、唯一索引：唯一索引可以保证表上不会有两行数据具有相同的值。注意：唯一索引可以保证表上不会有两行数据在键列上具有相同的值。
3、函数索引：包含函数/表达式的预先计算的值。
4、位图索引：列上值的类型少，某一列值重复值比较多可以创建位图索引。
5、复合索引：表中两个或两个以上的列一起建立索引。
6、全文索引：在表中文本列上建索引。

2.3 索引操作
2.3.1创建索引
2.3.1.1唯一索引

CREATE UNIQUE index  <用户.索引名> ON .<用户.模式.表名(列名称)>

例如：
CREATE UNIQUE INDEX T1_ID_INDEX ON DMTEST.T1(ID)
2.3.1.2函数索引

CREATE index  <用户.索引名>  ON  <用户.模式.表名(函数名(列名称))>

例如：

CREATE INDEX TEST.IND_T2 ON TEST.T2(UPPER(NAME)) TABLESPACE IND1;

单行函数：字符：initcap, lower, upper,lpad, trim replace,instr,substr
数字：round ,trunc ,mod 日期：add_months ,months_between,next_day ,last_day
聚合函数：sum ,avg ,count ,max ,min

2.3.1.3、复合索引

CREATE index  <用户.索引名>  ON  <用户.模式.表名((列1,列2))>

举例：

create index test.ind_t3 on test.t3(id,name);

1、复合索引中,单独使前导列（第一个字段）优化器会选择走索引;
2、单独使用非前导列优化器不会选择走索引（全表扫）;
3、如果同时使用复合索引中的字段，优化器会选择走索引;
4、索引在查询语句里面是否会用到，和 sql 语句的编写很重要，包括走索引的扫描方式，都会根据表中的列值的分布，统计信息是否完整，过滤字段选择情况不同而会不同。

2.3.1.4、位图索引

CREATE BITMAP INDEX <用户.索引名>  ON  <用户.模式.表名((列名称))>

举例：

CREATE BITMAP INDEX TEST.IND_T4 ON TEST.T4(SEX);

2.3.1.5、分区索引

CREATE  INDEX <用户.索引名>  ON  <用户.模式.表名((列名称))>

举例：

CREATE INDEX TEST.IND_T5_R1 ON TEST.T5_R1(ID);

如果表是分区表，在该表上创建的索引就是分区索引，非堆表每个分区一个索引（局部索引），堆表可以创建全局过索引，也可以是局部索引。在创建索引时候指定 GLOBAL 关键字，那么建立的索引就是全局索引。
2.3.1.6、全文索引

CREATE CONTEXT INDEX cti_address ON person.address (address1) LEXER DEFAULT_LEXER;
CREATE CONTEXT INDEX cti_address ON person.address (address1) SYNC TRANSACTION;
更新全文索引：
alter context index cti_address on person.address rebuild;
更新增加全文索引信息：
alter context index cti_address on person.address increment;
删除全文索引：
DROP CONTEXT INDEX cti_address ON PERSON.ADDRESS;
查询全文索引：
CTISYS.SYSCONTEXTINDEXES

2.3.2重建索引
当一个表经过大量的增删改操作后，表的数据在物理文件中可能存在大量碎片，从而影
响访问速度。另外，当删除表的大量数据后，若不再对表执行插入操作，索引所处的段可能
占用了大量并不使用的簇，从而浪费了存储空间。
可以使用重建索引来对索引的数据进行重组，使数据更加紧凑，并释放不需要的空间，
从而提高访问效率和空间效率。DM8 提供的重建索引的系统函数为：
SP_REBUILD_INDEX(SCHEMA_NAME varchar(256), INDEX_ID int);
SCHEAM_NAME 为索引所在的模式名，INDEX_ID 为索引 ID。
使用说明：

水平分区子表，临时表和系统表上建的索引不支持重建
虚索引和聚集索引不支持重建
例如，需要重建索引 emp_name，假设其索引 ID 为 1547892，那么使用以下语句重建

重建索引： 

SP_REBUILD_INDEX('SYSDBA', 1547892);


**2.3.3 删除索引** 
用户可能出于以下某项原因需要删除一个索引： 
1. 不再需要该索引； 
2. 该索引没有为针对其相关的表所发布的查询提供所期望的性能改善。例如，表可能 
很小，或者尽管表中有许多行但只有很少的索引项； 
3. 应用没有用该索引来查询数据。 
要想删除索引，则该索引必须包含在用户的模式中或用户必须具有 DROP ANY INDEX 
数据库权限。索引删除之后，该索引的段的所有簇都返回给包含它的表空间，并可用于表空 
间中的其他对象。 
如何删除索引，取决于是否是用 CREATE INDEX 语句明确地创建该索引的，是则可以 
用 DROP INDEX 语句删除该索引。如下面的语句删除 emp_ename 索引。 
DROP INDEX emp_ename; 
删除不存在的索引会报错。若指定 IF EXISTS 关键字，删除不存在的索引，不会报错， 
如： 
DROP INDEX IF EXISTS emp_ename; 
然而，不能直接删除与已启用的 UNIQUE KEY 键或 PRIMARY KEY 键约束相关的索引。 
要删除一个与约束相关的索引，必须停用或删除该约束本身。如下面的语句删除主键约束 
pk_emp_name，同时删除其对应的索引。 
ALTER TABLE emp DROP CONSTRAINT pk_emp_name; 
除了删除普通索引，DM8 还提供删除聚集索引，只要其聚集索引是通过 CREATE 
CLUSTER INDEX 明确建立的。例如，下面的语句删除 emp 表的聚集索引 clu_emp_name。 
DROP INDEX clu_emp_name; 
删除聚集索引其实是使用 ROWID 作为索引列重建聚集索引，即跟新建聚集索引一样会 
重建这个表以及其所有索引。 
删除表就自动删除了所有与其相关的索引

**2.4 查看索引信息**
创建索引后，可以通过 INDEXDEF 系统函数查看索引的定义。 

```c
INDEXDEF(INDEX_ID int, PREFLAG int); 
INDEX_ID 为索引 ID，PREFLAG 表示返回信息中是否增加模式名前缀。例如，需要查 
看索引 emp_name 的定义，假设其索引 ID 为 1547892，那么使用以下语句查看索引定义。 
SELECT INDEXDEF(1547892, 0); 
或 SELECT INDEXDEF(1547892, 1);

2.5 创建索引注意事项：

1、在 CREATE INDEX
语句中列的排序会影响查询的性能。通常，将最常用的列放在最前面。如果查询中有多个字段组合定位，则不应为每个字段单独创建索引，而应该创建一个组合索引。当两个或多个字段都是等值查询时，组合索引中各个列的前后关系是无关紧要的。但是如果是非等值查询时，要想有效利用组合索引，则应该按等值字段在前，非等值字段在后的原则创建组合索引，查询时只能利用一个非等值的字段。
2、一个表可以有任意数量的索引。但是，索引越多，修改表数据的开销就越大。当插入或删除行时，表上的所有索引也要被更改；更改一个列时，包含该列的所有索引也要被更改。因此，在从表中检索数据的速度和更新表的速度之间有一个折衷。例如，如果一个表主要仅用于读，则索引多就有好处；如果一个表经常被更新，则索引不宜多建。

三.管理触发器
DM 是一个具有主动特征的数据库管理系统，其主动特征包括约束机制和触发器机制。约束机制主要用于对某些列进行有效性和完整性验证；触发器（TRIGGER）定义当某些与数据库有关的事件发生时，数据库应该采取的操作。通过触发器机制，用户可以定义、删除和修改触发器。DM 自动管理和运行这些触发器，从而体现系统的主动性，方便用户使用。触发器是一种特殊的存储过程，它在创建后就存储在数据库中。触发器的特殊性在于它是建立在某个具体的表或视图之上的，或者是建立在各种事件前后的，而且是自动激发执行的，如果用户在这个表上执行了某个 DML 操作（INSERT、DELETE、UPDATE），触发器就被激发执行。
触发器常用于自动完成一些数据库的维护工作。例如，触发器可以具有以下功能：

可以对表自动进行复杂的安全性、完整性检查；
可以在对表进行 DML 操作之前或者之后进行其它处理；
进行审计，可以对表上的操作进行跟踪；
实现不同节点间数据库的同步更新

3.1 触发器的使用
DM 是一个具有主动特征的数据库管理系统，其主动特征包括约束机制和触发器机制。约束机制主要用于对某些列进行有效性和完整性验证；触发器（TRIGGER）定义当某些与数据库有关的事件发生时，数据库应该采取的操作。通过触发器机制，用户可以定义、删除和修改触发器。DM 自动管理和运行这些触发器，从而体现系统的主动性，方便用户使用。触发器是一种特殊的存储过程，它在创建后就存储在数据库中。触发器的特殊性在于它是建立在某个具体的表或视图之上的，或者是建立在各种事件前后的，而且是自动激发执行的，如果用户在这个表上执行了某个 DML 操作（INSERT、DELETE、UPDATE），触发器就被激发执行。
触发器常用于自动完成一些数据库的维护工作。例如，触发器可以具有以下功能：

可以对表自动进行复杂的安全性、完整性检查；
可以在对表进行 DML 操作之前或者之后进行其它处理；
进行审计，可以对表上的操作进行跟踪；
实现不同节点间数据库的同步更新。

语法格式：

CREATE [OR REPLACE] TRIGGER [<模式名>.]<触发器名> [WITH ENCRYPTION] 
< 触发限制描述 > [REFERENCING <trig_referencing_list>][FOR EACH {ROW | 
STATEMENT}][WHEN (<条件表达式>)]<触发器体> 
<trig_referencing_list>::= <referencing_1>|<referencing_2> 
<referencing_1>::=OLD [ROW] [AS] <引用变量名> [ NEW [ROW] [AS] <引用变量名>] 
<referencing_2>::=NEW [ROW] [AS] <引用变量名> [ OLD [ROW] [AS] <引用变量名>] 
<触发限制描述>::=<触发限制描述 1> | <触发限制描述 2> 
<触发限制描述 1>::= <BEFORE|AFTER> <触发事件列表> [LOCAL] ON <触发表名> 
<触发限制描述 2>::= INSTEAD OF <触发事件列表> [LOCAL] ON <触发视图名> 
<触发表名>::=[<模式名>.]<基表名> 
<触发事件>::=INSERT|DELETE|{UPDATE|{UPDATE OF<触发列清单>}} 
<触发事件列表>::=<触发事件> | {<触发事件列表> OR <触发事件>}

3.2 触发器的类型
DM 提供了三种类型的触发器：

表级触发器：基于表中的数据进行触发；
事件触发器：基于特定系统事件进行触发；
时间触发器：基于时间而进行触发

四.管理视图序列和同义词
4.1管理视图
4.1.1 视图定义
视图是数据库技术中一个十分重要的功能。从系统实现的角度讲，视图是从一个或几个基表（或视图）导出的表，但它是一个虚表，即数据字典中只存放视图的定义（由视图名和查询语句组成），而不存放对应的数据，这些数据仍存放在原来的基表中。当对一个视图进行查询时，视图将查询其对应的基表，并且将所查询的结果以视图所规定的格式和次序进行返回。因此当基表中的数据发生变化时，从视图中查询出的数据也随之改变了。从用户的角度来讲，视图就像一个窗口，透过它可以看到数据库中用户感兴趣的数据和变化。当用户所需的数据是一张表的部分列、或部分行，或者数据是分散在多个表中，那么就可以创建视图来将这些满足条件的行和列组织到一个表，而不需要修改表的属性、甚至创建新的表。

4.1.2 创建视图

CREATE [OR REPLACE] VIEW 
[<模式名>.]<视图名>[(<列名> {,<列名>})] 
AS <查询说明> 
[WITH [LOCAL|CASCADED]CHECK OPTION]|[WITH READ ONLY]; 
<查询说明>::=<表查询> | <表连接> 
<表查询>::=<子查询表达式>[ORDER BY 子句]

例如，为了防止非公司人员查询供应商的具体信息，包括 email、phone 等，这时可以
建立视图，针对不用的用户建立查询供应商信息的视图。

CREATE VIEW normal_view AS SELECT name FROM person; 
CREATE VIEW special_view AS SELECT name, sex, email, phone FROM person;

4.1.2 视图的更新和编译
视图的更新：
视图数据的更新包括插入(INSERT)、删除(DELETE)和修改(UPDATE)三类操作。由于视图是虚表，并没有实际存放数据，因此对视图的更新操作均要转换成对基表的操作

例从视图 VENDOR_EXCELLENT 中将名称为人民邮电出版社的 ACTIVEFLAG 改为 0。
UPDATE PURCHASING.VENDOR_EXCELLENT
SET ACTIVEFLAG = 0 WHERE NAME = ‘人民邮电出版社’;
系统执行该语句时，首先从数据字典中取出视图 VENDOR_EXCELLENT 的定义，将其中的查询说明与对视图的修改语句结合起来，转换成对基表的修改语句，然后再执行这个转换后的更新语句。

UPDATE PURCHASING.VENDOR 
SET ACTIVEFLAG = 0 
WHERE NAME = '人民邮电出版社' AND CREDIT = 1;

视图的编译：
一个视图依赖于其基表或视图，如果基表定义发生改变，如增删一列，或者视图的相关权限发生改变，可能导致视图无法使用。在这种情况下，可对视图重新编译，检查视图的合法性。
语法格式

ALTER VIEW [<模式名>.]<视图名> COMPILE;

4.1.3 视图的删除
一个视图本质上是基于其他基表或视图上的查询，我们把这种对象间关系称为依赖。用
户在创建视图成功后，系统还隐式地建立了相应对象间的依赖关系。在一般情况下，当一个
视图不被其他对象依赖时可以随时删除视图。
语法格式

DROP VIEW [IF EXISTS] [<模式名>.]<视图名> [RESTRICT | CASCADE]

使用说明

删除不存在的视图会报错。若指定IF EXISTS关键字，删除不存在的视图，不会
报错；
视图删除有两种方式：RESTRICT/CASCADE 方式。其中 RESTRICT 为缺省值。当设置 dm.ini 中的参数 DROP_CASCADE_VIEW 值为 1 时，如果在该视图上建有其它视图，必须使用 CASCADE 参数才可以删除所有建立在该视图上的视图，否则删除视图的操作不会成功；当设置 dm.ini 中的参数 DROP_CASCADE_VIEW 值为 0 时，RESTRICT 和 CASCADE 方式都会成功，且只会删除当前视图，不会删除建立在该视图上的视图；
如果没有删除参考视图的权限，那么两个视图都不会被删除；
该视图删除后，用户在其上的权限也均自动取消，以后系统中再建的同名视图，是与他毫无关系的视图

4.2 管理序列
序列（sequence）是 DM 数据库中的数据库实体之一。通过使用序列，多个用户可以产生和使用一组不重复的有序整数值。比如可以用序列来自动地生成主关键字值。序列通过提供唯一数值的顺序表来简化程序设计工作。
当一个序列第一次被查询调用时，它将返回一个预定值，该预定值就是在创建序时所指定的初始值。默认情况下，对于升序序列，序列的缺省初始值为序列的最小值，对于降序序列，缺省初始值为序列的最大值。可以指定序列能生成的最大值，默认情况下，降序序列的最大值缺省为-1，升序序列的最大值为 2 63-1；也可以指定序列能生成的最小值，默认情况下，升序序列的最小值缺省为 1，降序序列的最小值为-2 63。序列的最大值和最小值可以指定为 longint（4 个字节）所能表示的最大和最小有符号整数。
在随后的每一次查询中，序列将产生一个按其指定的增量增长的值。增量可以是任意的正整数或负整数，但不能为 0。如果此值为负，序列是下降的，如果此值为正，序列是上升的。默认情况下，增加缺省为 1

语法格式

CREATE SEQUENCE [ <模式名>.] <序列名> [ <序列选项列表>]; 
<序列选项列表> ::= <序列选项>{<序列选项>} 
<序列选项> ::= 
INCREMENT BY <增量值>| 
START WITH <初值>| 
MAXVALUE <最大值>| 
NOMAXVALUE| 
MINVALUE <最小值>| 
NOMINVALUE| 
CYCLE| 
NOCYCLE| 
CACHE <缓存值>| 
NOCACHE| 
ORDER | 
NOORDER | 
GLOBAL | 
LOCAL

达梦技术社区地址：https://eco.dameng.com

disclipline

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
达梦数据库--DM8模式对象创建和管理（详解）

从系统实现的角度讲，视图是从一个或几个基表（或视图）导出的表，但它是一个虚表，即数据字典中只存放视图的定义（由视图名和查询语句组成），而不存放对应的数据，这些数据仍存放在原来的基表中。为提高数据库在大数据量读写操作和查询时的效率，达梦数据库提供了对表和索引进行分区的技术，把表和索引等数据库对象中的数据分割成小的单位，分别存放在一个个单独的段中，用户对表的访问转化为对较小段的访问，以改善大型应用系统的性能。范围分区非常适用于数据按时间范围组织的表，不同的时间段的数据属于不同的分区。
复制链接

扫一扫