管理表

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

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

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

如果用户在创建表时没有定义表的完整性和一致性约束条件,那么用户可以利用DM所提供的基表修改语句来进行补充或修改。DM系统提供基表修改语句,可对基表的结构进行全面的修改,包括修改基表名、列名、增加列、删除列、修改列类型、增加表级约束、删除表级约束、设置列缺省值、设置触发器状态等一系列修改功能。

本章描述管理表的几个方面,包括以下内容:

  1. 管理表的准则;
  2. 创建表;
  3. 更改表;
  4. 删除表;
  5. 清空表;
  6. 查看表信息。

9.1 管理表的准则

9.1.1 设计表

表是数据库设计过程中的基本构件,基于来自应用开发者的有关应用如何运作和所期望的数据类型,数据库管理员应与应用开发者一起工作,并认真规划每个表,具体需要做到以下几点:

  1. 规范化表,估算并校正表结构,使数据冗余达到最小;
  2. 为每个列选择合适的数据类型,是否允许为空等,并根据实际情况判断是否需要对列进行加密或压缩处理;
  3. 建立合适的完整性约束,管理约束可查看15章管理完整性约束的内容;
  4. 建立合适的聚集索引。每个表(列存储表,堆表除外)都含一个聚集索引,默认以ROWID建立,而建立合适的聚集索引,可以有效加快表的检索效率;
  5. 根据实际需要,建立合适类型的表。DM支持的表类型包括普通表、临时表、水平分区表、堆表和列存储表。本章只介绍普通表和临时表,其他类型表将在其他章节中重点介绍。

9.1.2 指定表的存储空间上限

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

9.1.3 指定表的存储位置

创建表时,在STORAGE子句中,可对表指定存储的表空间。如果没有指定,则该表将创建在用户的默认表空间中。

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

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

9.2 创建表

9.2.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表,并有几个完整性约束,其中包含定义在不同列上的一个主键和外键。约束将在15章中讨论。

创建表之后,可以使用INSERT命令插入数据或使用达梦数据导入导出工具装载数据,还可以直接使用CREATE TABLE AS SELECT创建一个表。

9.2.2 指定表的聚集索引

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

当建表语句未指定聚集索引键,DM的默认聚集索引键是ROWID,即记录默认以ROWID在页面中排序。ROWID是B树为记录生成的逻辑递增序号,表上不同记录的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
  );

指定聚簇索引键后,如果查询条件中含有聚簇索引键,可以定位记录在B树上的位置,使查询性能大大提高。然而,插入记录也需要根据聚簇索引键定位插入位置,有可能导致页面的分裂而影响插入性能。

在dm.ini配置文件中,可以指定配置项使表中的主键自动转化为聚簇主键,该配置项为PK_WITH_CLUSTER。默认情况下,PK_WITH_CLUSTER为1,即建表时指定的主键自动转化为聚簇主键;若为0,则主键不会自动变为聚簇主键。

9.2.3 指定表的填充因子

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

例如,上述的创建employee表语句中的STORAGE子句,指定了FILLFACTOR为80,即填充因子为80%。

当填充因子取值低,则需要更多的页来存储数据,因而读取范围大,会影响性能。而当填充因子取值高,则在更新数据时可能造成大量的页拆分,页拆分需要消耗较多CPU和I/O资源,同样会影响性能。原则上,在只读表上应该设置填充因子高,而有大量更新的表上应该设置较低的值。默认情况下,DM新建的表和索引的填充因子是100,可根据实际情况设置合适的填充因子大小。

9.2.4 查询建表

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

  CREATE TABLE NEW_EMP
  AS
  SELECT * FROM EMPLOYEE;

如果用户通过单表的全表查询进行建表操作,则可以通过将INI参数CTAB_SEL_WITH_CONS置为1进行原始表上约束的拷贝,列上能拷贝的约束包括默认值属性、自增属性、非空属性以及加密属性,表上能拷贝的约束包括唯一约束、PK约束以及CHECK约束。

9.2.5 创建临时表

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

临时表中的数据不能像在其它永久表中的数据那样进行备份,当事务结束或会话断开时,数据就会被清空。在临时表创建过程中,不会像永久表和索引那样自动分配数据段,而是仅当第一次执行DML语句时,才会为临时表在临时表空间中分配空间。并且,对于不同的会话,临时表上的数据是独享的,不会互相干扰,即会话A不能访问会话B临时表上的数据。

对复杂查询的传统响应方式之一是使用一个视图,使复杂查询更易于操作。但是,视图在每次访问时都需要执行,因而大大降低了性能。而通过AS SELECT子句建立的临时表是将复杂查询的结果通过临时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;

9.3 更改表

想更改的表如果在所属的模式中,用户必须具有ALTER TABLE数据库权限;若在其他模式中,用户必须有ALTER ANY TABLE的数据库权限。

通过更改表,用户可以对数据库中的表作如下修改:

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

9.4 删除表

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

  1. 表的结构信息从数据字典中删除,表中的数据不可访问;
  2. 表上的所有索引和触发器被一起清除;
  3. 所有建立在该表上的同义词、视图和存储过程变为无效;
  4. 所有分配给表的簇标记为空闲,可被分配给其他的数据库对象。

一般情况下,普通用户只能删除自己模式下的表。若要删除其他模式下的表,则必须具有DROP ANY TABLE数据库权限。

以下语句可删除employee表:

  DROP TABLE employee;

删除不存在的表会报错。若指定IF EXISTS关键字,删除不存在的表,不会报错,如:

  DROP TABLE IF EXISTS employee;

如果要删除的表被其他表引用,即其他表的外键引用了表的任何主键或唯一键,则需要在DROP TABLE 语句中包含CASCADE 选项,如:

  DROP TABLE employee CASCADE;

9.5 清空表

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

  1. 使用DELETE语句;
  2. 使用DROP和CREATE 语句;
  3. 使用TRUNCATE语句。

9.6.1 使用DELETE

使用DELETE语句能删除表中的行。例如,下面的语句删除employee表中的所有行:

  DELETE FROM employee;

但是,使用DELETE清空表,当表有很多行时,会消耗很多系统资源。因为,DELETE操作需要CPU时间,并且会产生大量的REDO日志和UNDO记录。另外,如果表上关联了元组级触发器,每删除一行,就会启动一次触发器。这都需要大量的系统资源。

9.6.2 使用DROP和CREATE

使用DROP删除一个表,然后创建一个同名的表,也可以达到清空表的效果。例如,下面的语句先删除employe表,之后再重新创建。

  DROP TABLE employee;
  CREATE TABLE employee(…);

当删除和重新创建表时,所有与之相关联的索引、完整性约束和触发器也被删除。同样,所有针对被删除表的授权也会被删除。

9.6.3 使用TRUNCATE

使用TRUNCATE 语句能删除表中的所有行。例如,下面的语句清空employee表。

  TRUNCATE TABLE employee;

TRUNCATE语句为我们提供了一种快速、有效地删除表所有行的方法。并且TRUNCATE是一个DDL语句,不会产生任何回滚信息。执行TRUNCATE会立即提交,而且不能回滚。

TRUNCATE语句并不影响与被删除的表相关联的任何结构、约束、触发器或者授权。另外,DM数据库TRUNCATE表后,原来分配给该表的空间会被释放,供其他数据库对象使用,大大提高空间的利用效率。

一般情况下,普通用户只能TRUNCATE自己模式下的表。若要TRUNCATE其他模式下的表,则必须具有DROP ANY TABLE数据库权限。

如果要清空的表被其他表引用,即其他表的外键引用了表的任何主键或唯一键,并且子表不为空或子表的外键约束未被禁用,则不能TRUNCATE该表。

9.6 查看表信息

9.6.1 查看表定义

创建表后,可以通过SP_TABLEDEF系统过程查看表的定义。

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

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

9.6.2 查看自增列信息

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

  1. IDENT_CURRENT:获得表上自增列的当前值;
  2. IDENT_SEED:获得表上自增列的种子信息;
  3. 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');

9.6.3 查看表的空间使用情况

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

  1. TABLE_USED_SPACE:已分配给表的页面数;
  2. 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');
微信扫码
分享文档
扫一扫
联系客服