DM达梦数据库的模式对象的相关概念及使用

模式

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

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

采用模式的原因有几点：

1. 允许多个用户使用一个数据库而不会干扰其它用户；

2. 把数据库对象组织成逻辑组，让它们更便于管理；

3. 第三方的应用可以放在不同的模式中，这样可以避免和其它对象的名字冲突。模式类似于操作系统层次的目录，只不过模式不能嵌套。

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

1. 表；

2. 视图；

3. 索引；

4. 触发器；

5. 存储过程/函数；

6. 序列；

7. 全文索引；

8. 包；

9. 同义词；

10. 类；

11. 外部链接。

在引用模式对象的时候，一般要在模式对象名前面加上模式名。具体格式如下：

[模式名].对象名

当然，在当前模式和要引用的模式对象所属的模式相同时，可以省略模式名。如果我们访问一个表时，没有指明该表属于哪一个模式，系统就会自动给我们在表前加上缺省的模式名。类似地，如果我们在创建对象时不指定该对象的模式，则该对象的模式为用户的缺省模式。

模式对象之外的其他对象统一称为非模式对象，非模式对象主要包括以下几种对象：

1. 用户；

2. 角色；

3. 权限；

4. 表空间。

在DM 数据库中，使用CREATE SCHEMA语句就可以创建一个空的模式对象，也可以在创建模式时，同时创建多个模式对象。详细语法规则此处先不深入学习。

下面的语句在创建模式test的同时，创建了属于模式test的一个序列和两张表。

CREATE SCHEMA TEST
    CREATE SEQUENCE ADDRESS_SEQ INCREMENT BY 1 
    CREATE TABLE ADDRESS(
          ADDRESSID INT,
            ADDRESS1 VARCHAR(60) NOT NULL,
            ADDRESS2 VARCHAR(60),
            CITY VARCHAR(30) NOT NULL,
            POSTALCODE VARCHAR(15) NOT NULL)
    CREATE TABLE ADDRESS_TYPE(
        ADDRESS_TYPEID INT PRIMARY KEY,
            NAME VARCHAR(50) NOT NULL);

可以用下面的格式来引用TEST模式中的ADDRESS表：TEST.ADDRESS，如向ADDRESS表中插入一条数据。

INSERT INTO TEST.ADDRESS VALUES(1,’武汉市关山一路特1号光谷软件园C6栋5层’,’上海市闸北区江场三路28号301室’,’上海市’,’200436’);

表

表是数据库中数据存储的基本单元，是对用户数据进行读和操纵的逻辑实体。表由列和行组成，每一行代表一个单独的记录。表中包含一组固定的列，表中的列描述该表所跟踪的实体的属性，每个列都有一个名字及各自的特性。

列的特性由两部分组成：数据类型 （dataType） 和长度 （length） 。对于 NUMERIC、DECIMAL以及那些包含秒的时间间隔类型来说，可以指定列的小数位及精度特性。在DM系统中，CHAR、CHARACTER、VARCHAR数据类型的最大长度由数据库页面大小决定，数据库页面大小在初始化数据库时指定。DM系统具有 SQL-92 的绝大部分数据类型，以及部分SQL-99、Oracle和SQL Server 的数据类型。

为了确保数据库中数据的一致性和完整性，在创建表时可以定义表的实体完整性、域完整性和参考完整性。实体完整性定义表中的所有行能唯一地标识，一般用主键、唯一索引、UNIQUE关键字及 IDENTITY属性来定义；域完整性通常指数据的有效性，限制数据类型、缺省值、规则、约束、是否可以为空等条件，域完整性可以确保不会输入无效的值；参考完整性维护表间数据的有效性、完整性，通常通过建立外键联系另一表的主键来实现。

创建表 

1 创建普通表

如果要在所属模式中创建新表，需要有CREATE TABLE数据库权限；而要在其他用户的模式中创建新表，则需要有CREATE ANY TABLE数据库权限。创建表时，应当为表指定一个表空间，否则，表将在MAIN创建。下面给出一个创建一个简单表的例子。 

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, 
  NEXT 			50, 
  MINEXTENTS 		10,
  FILLFACTOR		80,
  ON 				USERS);

在上述CREATE TABLE语句中，在users表空间上建立了employee表，并有几个完整性约束，其中包含定义在不同列上的一个主键和外键。

2 指定表的聚集索引 

表（列存储表和堆表除外）都是使用B+树(以下简称B树)索引结构管理的，每一个普通表都有一个聚集索引，数据通过聚集索引键排序，根据聚集索引键可以快速查询任何记录。

当建表语句未指定聚集索引键，DM的默认聚集索引键是ROWID，即记录默认以ROWID在页面中排序。很多情况下，以ROWID建的默认聚集索引并不能提高查询速度，因为实际情况下很少人根据ROWID来查找数据。

因此，DM提供三种方式供用户指定聚集索引键：

1. CLUSTER PRIMARY KEY：指定列为聚集索引键，并同时指定为主键，称为聚簇主键；

2. CLUSTER KEY：指定列为聚集索引键，但是是非唯一的；

3. CLUSTER UNIQUE KEY：指定列为聚集索引键，并且是唯一的。

例如，创建student表，指定stu_no为聚簇主键。

CREATE  TABLE  STUDENT( 
  STUNO 		INT 				CLUSTER PRIMARY KEY, 
  STUNAME 	VARCHAR(15) 	NOT NULL, 
  TEANO 		INT, 
  CLASSID 	INT
);

3 指定表的填充因子

指定表的填充因子，即指定聚集索引的填充因子。索引的填充因子指在新建和重组索引时，页面记录存储空间占页面总大小的百分比。而这部分预留空间是为更新字段时使用的，一个有效的填充因子可以大大减少由更新记录导致的页面拆分。

4 查询建表

为了创建一个与已有表相同的新表，或者为了创建一个只包含另一个表的一些行和列的新表，可以使用CREATE TABLE AS SELECT(CTAS)命令。使用该命令，可以通过使用WHERE条件将已有表中的一部分数据装载到一个新表中，或者可以通过SELECT * FROM子句将已有表的所有数据装载到创建的表中，如以下程序片段所示：

CREATE TABLE NEW_EMP AS SELECT * FROM EMPLOYEE;

5 创建临时表

当处理复杂的查询或事务时，由于在数据写入永久表之前需要暂时存储一些行信息或需要保存查询的中间结果，可能需要一些表来临时存储这些数据。DM允许创建临时表来保存会话甚至事务中的数据。在会话或事务结束时，这些表上的数据将会被自动清除。 并且，对于不同的会话，临时表上的数据是独享的，不会互相干扰，即会话A不能访问会话B临时表上的数据。

DM临时表支持以下功能：

1. 在临时表中，会话可以像普通永久表一样更新、插入和删除数据；

2. 临时表的DML操作产生较少的REDO日志；

3. 临时表支持建索引，以提高查询性能；

4. 在一个会话或事务结束后，数据将自动从临时表中删除；

5. 不同用户可以访问相同的临时表，每个用户只能看到自己的数据；

6. 临时表的数据量很少，意味着更高效的查询效率；

7. 临时表的表结构在数据删除后仍然存在，便于以后的使用；

8. 临时表的权限管理跟普通表一致。

临时表 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;

索引

索引是与表相关的可选的结构（聚簇索引除外），它能使对应于表的SQL语句执行得更快，因为有索引比没有索引能更快地定位信息。DM8索引能提供访问表的数据的更快路径，可以不用重写任何查询而使用索引，其结果与不使用索引是一样的，但速度更快。

DM8提供了几种最常见类型的索引，对不同场景有不同的功能，它们是：

1. 聚集索引：每一个普通表有且只有一个聚集索引；

2. 唯一索引：索引数据根据索引键唯一；

3. 函数索引：包含函数/表达式的预先计算的值；

4. 位图索引：对低基数的列创建位图索引；

5. 位图连接索引：针对两个或者多个表连接的位图索引，主要用于数据仓库中；

6. 全文索引：在表的文本列上而建的索引。

索引在逻辑上和物理上都与相关的表的数据无关，作为无关的结构，索引需要存储空间。 创建或删除一个索引，不会影响基本的表、数据库应用或其他索引。当插入、更改和删除相关的表的行时，DM8会自动管理索引。如果删除索引，所有的应用仍继续工作，但访问以前被索引了的数据时速度可能会变慢。 

创建索引

要在用户自己的模式中创建索引，至少要满足如下条件之一：

1. 要被索引的表是在自己的模式中 ；

2. 在要被索引的表上有CREATE INDEX 权限；

3. 具有 CREATE ANY INDEX 数据库权限。

要在其他模式中创建索引，用户必须具有 CREATE ANY INDEX 数据库权限。

1 明确地创建索引

可以用CREATE INDEX语句明确地创建索引。如下语句在emp表的 ename列上创建一个名为emp_ename的索引，该索引使用表空间users。

CREATE INDEX emp_ename ON emp(ename)
  STORAGE (
  INITIAL 	50,
  NEXT 	50,
  ON 		USERS);

注意，上述语句为该索引明确地指定了几个存储设置和一个表空间。如果没有给索引指定存储选项，则INITIAL和NEXT等存储选项会自动使用表空间的默认存储选项。

2 创建聚集索引

当建表语句未指定聚集索引键时，DM8的默认聚集索引键是ROWID。若指定索引键，表中数据都会根据指定索引键排序。建表后，DM8也可以用创建新聚集索引的方式来重建表数据，并按新的聚集索引排序。例如，可以对emp表以ename列新建聚集索引。

CREATE CLUSTER INDEX clu_emp_name ON emp(ename);

新建聚集索引会重建这个表以及其所有索引，包括二级索引、函数索引，是一个代价非常大的操作。因此，最好在建表时就确定聚集索引键，或在表中数据比较少时新建聚集索引，而尽量不要对数据量非常大的表建立聚集索引。

创建聚集索引的约束条件：

1. 每张表中只允许有一个聚集索引，如果之前已经指定过CLUSTER INDEX或者指定了CLUSTER PK，则用户新建立CLUSTER INDEX时系统会自动删除原先的聚集索引。但如果新建聚集索引时指定的创建方式（列，顺序）和之前的聚集索引一样，则会报错；

2. 指定CLUSTER INDEX 操作需要重建表上的所有索引，包括PK索引；

3. 删除聚集索引时，缺省以ROWID排序，自动重建所有索引；

4. 若聚集索引是默认的ROWID索引，不允许删除；

5. 聚集索引不能应用到函数索引中；

6. 不能在列存储表上新建/删除聚集索引；

7. 建聚集索引语句不能含有partition_clause子句；

8. 在临时表上增删索引会使当前会话上临时b树数据丢失。

3 明确地创建唯一索引 

索引可以是唯一的或非唯一的。唯一索引可以保证表上不会有两行数据在键列上具有相同的值，非唯一索引不会在键列上施加这个限制。

可用CREATE UNIQUE INDEX语句来创建唯一索引，如下例子创建一个唯一索引：

CREATE UNIQUE INDEX dept_unique_index ON dept (dname) STORAGE (ON users);

4 自动创建与约束相关的唯一索引

DM8通过在唯一键或主键上创建一个唯一索引来在表上实施UNIQUE KEY或PRIMARY KEY 完整性约束。当启用约束时DM8自动创建该索引。如下面的语句会自动在表emp的name列上创建一个唯一索引。

ALTER TABLE emp ADD CONSTRAINT pk_emp_name PRIMARY KEY (name);

 5 创建基于函数的索引

基于函数的索引促进了限定函数或表达式的返回值的查询，该函数或表达式的值被预先计算出来并存储在索引中。例如，考虑如下WHERE 子句中的表达式

CREATE INDEX idx ON example_tab(column_a + column_b);
SELECT * FROM example_tab WHERE column_a + column_b < 10;

因为该索引是建立在column_a + column_b之上的，所以优化器可以为该查询使用范围扫描。优化器根据该索引计算查询代价，如果代价最少，优化器就会选择该函数索引，column_a + column_b就不会重复计算。

创建函数索引有以下约束条件：

1. 函数索引表达式可以由多列组成，不同的列不能超过63个；

2. 函数索引表达式里面不允许出现大字段和时间间隔类型列；

3. 不支持建立分区函数索引；

4. 函数索引表达式的长度理论值不能超过816个字符（包括生成后的指令和字符串）；

5. 函数索引不能为CLUSTER或PRIMARY KEY类型；

6. 表达式不支持集函数和不确定函数，不确定函数为每次执行得到的结果不确定，系统中不确定函数包括：RAND、SOUNDEX、CURDATE、CURTIME、CURRENT_DATE、CURRENT_TIME、CURRENT_TIMESTAMP、GETDATE、NOW、SYSDATE、CUR_DATABASE、DBID、EXTENT、PAGE、SESSID、UID、USER、VSIZE、SET_TABLE_OPTION、SET_INDEX_OPTION、UNLOCK_LOGIN、CHECK_LOGIN、GET_AUDIT、CFALGORITHMSENCRYPT、SF_MAC_LABEL_TO_CHAR、CFALGORITHMSDECRYPT、BFALGORITHMSENCRYPT、SF_MAC_LABEL_FROM_CHAR、BFALGORITHMSDECRYPT、SF_MAC_LABEL_CMP；

7. 快速装载不支持含有函数索引的表；

8. 当表中含有行前触发器并且该触发器会修改函数索引涉及列的值时，不能建立函数索引；

9. 若函数索引中要使用用户自定义的函数，则函数必须是指定了DETERMINISTIC属性的确定性函数；

10. 若函数索引中使用的确定性函数发生了变更或删除，用户需手动重建函数索引；

11. 若函数索引中使用的确定性函数内有不确定因素，会导致前后计算结果不同的情况。在查询使用函数索引时，使用数据插入函数索引时的计算结果为KEY值；修改时可能会导致在使用函数索引过程中出现根据聚集索引无法在函数索引中找到相应记录的情况，对此进行报错处理。

 6 创建位图索引

位图索引主要针对含有大量相同值的列而创建。位图索引被广泛引用到数据仓库中，创建方式和普通索引一致，对低基数（不同的值很少）的列创建位图索引，能够有效提高基于该列的查询效率。且执行查询语句的where子句中带有AND和OR谓词时，效率更加明显。

如下例子创建一个位图索引：

CREATE BITMAP INDEX S1 ON PURCHASING.VENDOR (VENDORID);

位图索引具有以下约束：

1. 支持普通表、堆表和水平分区表创建位图索引；

2. 不支持对大字段创建位图索引；

3. 不支持对计算表达式列创建位图索引；

4. 不支持在UNIQUE列和PRIMARY KEY上创建位图索引；

5. 不支持对存在CLUSTER KEY的表创建位图索引；

6. 仅支持单列或者不超过63个组合列上创建位图索引；

7. MPP环境下不支持位图索引的创建；

8. 不支持快速装载建含有位图索引的表；

9. 不支持全局位图索引；

10. 包含位图索引的表不支持并发的插入、删除和更新操作。

7 创建位图连接索引 

位图连接索引是一种提高通过连接实现海量数据查询效率的有效方式，主要用于数据仓库环境中。区别于上一节所说的建立在单表上的位图索引，位图连接索引是针对两个或者多个表的连接而建立的位图索引，同时保存了连接的位图结果。对于索引列中的每一个值，位图连接索引在索引表中保存了对应行的ROWID。

如下例子创建一个位图连接索引：

create bitmap index SALES_CUSTOMER_NAME_IDX   on SALES.SALESORDER_HEADER(SALES.CUSTOMER.PERSONID)
from   SALES.CUSTOMER, SALES.SALESORDER_HEADER  where  SALES.CUSTOMER.CUSTOMERID = SALES.SALESORDER_HEADER.CUSTOMERID;

使用说明

1. 适用于常规索引的基本限制也适用于位图连接索引；

2. 用于连接的列必须是维度表中的主键或存在唯一约束；如果是复合主键，则必须使用复合主键中的所有列；

3. 当多个事务同时使用位图连接索引时，同一时间只允许更新一个表；

4. 连接索引创建时，基表只允许出现一次；

5. 不允许对存在cluster key的表创建位图连接索引；

6. 位图连接索引表（内部辅助表，命名为BMJ$_索引名）仅支持select操作，其他操作都不支持：如insert、delete、update、alter、drop和建索引等；

7. 不支持对位图连接索引所在事实表和维度表的备份还原，不支持位图连接索引表的表级备份还原；

8. 不支持位图连接索引表、位图连接索引以及虚索引的导出导入；

9. 位图连接索引及其相关表不支持快速装载；

10. 位图连接索引名称的长度限制为：事实表名的长度+索引名称长度+6<128；

11. 仅支持普通表、堆表和HUGE表；

12. WHERE条件只能是列与列之间的等值连接，并且必须含有所有表；

13. 事实表上聚集索引和位图连接索引不能同时存在；

14. 不支持对含有位图连接索引的表中的数据执行DML，如需要执行DML,则先删除该索引；

15. 含有位图连接索引的表不支持下列DDL操作：删除、修改表约束，删除、修改列，更改表名。另外，含位图连接索引的堆表不支持添加列操作；

16. 不允许对含有位图连接索引的表并发操作；

17. 创建位图连接索引时，在存储参数中可指定存储位图的字节数，有效值为：1~128，服务器自动校正为4的倍数，默认值为48。如STORAGE(SECTION(4))，表示使用4个字节存储位图信息。

视图

视图是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制，它简化了用户数据模型，提供了逻辑数据独立性，实现了数据共享和数据的安全保密。视图是数据库技术中一个十分重要的功能。从系统实现的角度讲，视图是从一个或几个基表（或视图）导出的表，但它是一个虚表，即数据字典中只存放视图的定义（由视图名和查询语句组成），而不存放对应的数据，这些数据仍存放在原来的基表中。当对一个视图进行查询时，视图将查询其对应的基表，并且将所查询的结果以视图所规定的格式和次序进行返回。因此当基表中的数据发生变化时，从视图中查询出的数据也随之改变了。当用户所需的数据是一张表的部分列、或部分行，或者数据是分散在多个表中，那么就可以创建视图来将这些满足条件的行和列组织到一个表，而不需要修改表的属性、甚至创建新的表。这样不仅简化了用户的操作，还可以提高数据的逻辑独立性，实现数据的共享和保密。 

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

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

然后把视图normal_view的权限授予普通用户，只允许他们查询供应商的姓名信息，把special_view视图的权限授予高级用户，允许他们查看供应商的email、电话等信息，这样就较好地实现了供应商信息的安全与保密。

更多达梦资讯请上达梦社区了解 https://eco.dameng.com