基础 | 管理表

基本概念

表是数据库中存储数据的基本单元

表 = 行1 + 行2 + … + 行n
行 = 列1 + 列2 + … + 列n
列 = 数据类型 + 长度

一行代表一个实体，所有的行（实体）加起来就是一张表
一行由一组列组成，每一列代表实体的属性
列由数据类型和长度组成

学生表（记录所有学生的信息）
一行就是一个学生，每列都描述了学生的一个属性

姓名	性别	身份证	班级
小明	男	1234	0501
小兰	女	2234	0601

管理表的准则

设计表

1. 控制列的数量，减少数据冗余（冗余字段的利与弊？）
2. 为列选择合适的数据类型（是否允许为空？加密？压缩？）
3. 建立合适的完整性约束
4. 建立合适的聚集索引
5. 根据需要，建立合适类型的表（DM支持普通表、临时表、水平分区表、堆表、列存储表）

创建表

创建普通表

CREATE TABLE dept(
DEPTNO	INT 		PRIMARY KEY,
DNAME	VARCHAR(20)	NOT NULL
);

CREATE TABLE employee(
EMPNO 	INT 		PRIMARY KEY,
ENAME	VARCHAR(15)	NOT NULL,
JOB		VARCHAR(10),
MGR 	INT,
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			main
);

指定表的聚集索引

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

我是这么理解的：

• 若不使用B+树索引结构管理表的话，那么表中行的顺序受插入/删除的影响（先插入的行，就在表的前面，后插入的行就在表的后面），查询起来就比较麻烦（得从头到尾找）

• 若使用B+数索引结构管理表，选择表的某一列作为聚集索引，那么表中行的顺序就是按该列进行排序的，查起来就比较快（利用聚集索引来查询，基本能确定数据大致位置）

DM中，指定聚集索引键的三种方式

关键字	描述
CLUSTER PRIMARY KEY	指定列为聚集索引键和主键，称为聚簇主键
CLUSTER KEY	指定列为聚集索引键，不过是非唯一的
CLUSTER UNIQUE KEY	指定列为聚集索引键，且是唯一的

DM默认的聚簇索引键是ROWID，即行默认以ROWID进行排序

创建student表，指定stu_no为聚簇主键
CREATE TABLE student(
STUDENT		INT			CLUSTER PRIMARY KEY,
STUNAME		VARCHAR(15)	NOT NULL,
TEANO		INT,
CLASSID		INT
);

聚簇索引键对查询和插入的影响

• 若查询条件含有聚簇索引键，可利用该索引，定位记录在B+树上的位置，从而提高查询性能（不用全表扫了！）
• 插入时，由于需要根据聚簇索引键定位插入位置，需要维护B+树，故需要耗费更多的时间
• 疑问：使用聚簇索引键，插入时可能会导致页分裂？

dm.ini参数中，PK_WITH_CLUSTER=1，可将建表时指定的主键自动转换为聚簇主键

指定表的填充因子

填充因子过低 >> 需要更多的页存储数据 >> 读取范围变大 >> 影响读取效率
填充因子过高 >> 更新数据时可能造成大量页拆分 >> 消耗CPU和I/O >> 影响更新效率

对只读表（或更新少的表），可设置较高的填充因子
对有大量更新的表，可降低填充因子的数值

查询建表

可使用CTAS（CREATE TABLE AS SELECT）命令，创建一个与已有表相同的新表（也可以是该表的子表）

CREATE TABLE new_emp AS SELECT * FROM employee;

注：
这种方式只拷贝数据，如需拷贝原始表上的约束，需将CTAB_SEL_WITH_CONS设置为1

创建临时表

可使用临时表提高复杂查询的效率（把过渡数据存在临时表中，再拼接成要的查询结果）

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

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

使用示例（待补充）

更改表（ALTER TABLE）

表结构层面的更改（而不是数据层面的更改），有以下几类

1. 添加 / 删除列，修改现有列定义（列名、数据类型、长度、默认值）
2. 添加、修改、删除与表相关的完整性约束
3. 表重命名
4. 启动 / 停用与表相关的完整性约束
5. 启动 / 停用与表相关的触发器
6. 修改表的SPACE LIMIT
7. 增 / 删自增列

删除表（DROP TABLE）

删除表会产生以下结果

1. 删除数据字典中的表结构信息，表数据将不可访问
2. 表上的索引和触发器将一并被清除
3. 基于表创建的同义词、视图、存储过程将变为无效的（INVAILD）
4. 所有分配给表的簇将被标记为空闲，以供其他数据库对象使用

删除employee表
DROP TABLE IF EXISTS employee;

若要删除的表被其他表引用了，即其他表的外键引用了要删除表的主键或唯一键时，需添加CASCADE选项
DROP TABLE employee CASCADE;

清空表

清空表数据，保留表结构

DM8删除表中所有行的方式如下：

1. DELETE
2. DROP + CREATE
3. TRUNCATE

DELETE

delete会产生大量REDO日志和UNDO记录

DELETE FROM employee

DROP + CREATE

删表再重建，也相当于清空表（索引、约束、触发器、权限会被删掉，需重建）

DROP TABLE employee
CREATE TABLE employee(...);

TRUNCATE

快速、有效清空表的方法，且由于TRUNCATE是DDL语句，不会产生任何回滚信息
执行TRUNCATE会立即提交，不得回滚
会立即释放空间（而不是光做标记，例如将数据页标记为"可覆盖"之类的）

TRUNCATE TABLE employee;

注：
若要清空的表被其他表引用，即其他表的外键引用了表的主键或唯一键，且子表不为空或子表的外键约束未被禁用，则不能TRUNCATE该表。

drop、delete、truncate删表或清空表时，哪个会释放空间？哪个执行速度快？（待实验）

查看表信息

查看表定义

返回建表语句

call sp_tabledef('SYSDBA','EMPLOYEE');

查看自增列信息

DM支持INT和BIGINT两种数据类型的自增列

查看自增列当前值、种子、增量的函数

• IDENT_CURRENT 获取表自增列的当前值
• IDENT_SEED 获取表自增列的种子信息（起始值）
• IDENT_INCR 获取表自增列的增量信息（步长）

create table ident_table(
c1	int identity(10, 100),
c2	int	
);
select ident_current('SYSDBA.IDENT_TABLE');
select ident_seed('SYSDBA.IDENT_TABLE');
select ident_incr('SYSDBA.IDENT_TABLE');

查看表的物理空间使用情况

相关函数如下：

• 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');