DM数据库——模式、表、索引管理基础（超详细版）

  • 一、管理模式对象的空间
  • 1.DM8对普通表和索引提供的参数
  • 2.DM8对HUGE表提供的参数
  • 3.空间限制
  • 4.查看占用的空间
  • 二、管理表
  • 1.设计表
  • 2.创建表
  • 3.更改表
  • 4.删除表
  • 5.清空表（清除表中的数据）
  • 6.查看表的各种信息
  • 三、管理索引
  • 1.索引的类型
  • 2.建立索引的条件
  • 3.创建索引
  • 3.1 创建索引的条件
  • 3.2常见创建索引的方法
  • 4.删除索引
  • 5.查看索引信息


一、管理模式对象的空间

1.DM8对普通表和索引提供的参数

参数名

说明

INITIAL

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

NEXT

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

MINEXTENTS

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

FILLFACTOR

填充比例：指定插入数据时数据页的充满程度，取值范围从 0 到100。默认值为 0，等价于 100，表示全满填充

表空间名

在指定的表空间上建表或索引，表空间必须已存在

例如：
表 PERSON 建立在表空间 TS_PERSON 中，初始簇大小为 8，最小保留簇数目为 8，下次分配簇数目为 4，填充比例为 87。

CREATE TABLE PERSON.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 8, MINEXTENTS 8,
NEXT 4, ON TS_PERSON, FILLFACTOR 87);

2.DM8对HUGE表提供的参数

参数名

说明

SECTION

区大小，默认值为 65536 行

FILESIZE

文件大小，则默认为 64M

日志属性

LOG NONE：不做镜像；LOG LAST：做部分镜像；LOG ALL：全部做镜像。

例如：新建STUDENT 表的区大小为 65536 行，文件大小为 64M，指定所在的混合表空间为 TS1，做完整镜像，S_COMMENT 列指定的区大小为不做统计信息，其它列（默认）都做统计信息

CREATE HUGE TABLE STUDENT
(
S_NO INT, 
S_CLASS VARCHAR, 
S_COMMENT VARCHAR(79) STORAGE(STAT NONE)
)STORAGE(SECTION(65536) , FILESIZE(64), ON TS1) LOG ALL;

注意：DM8 中表和索引对象的所占用的簇要么是全满的状态要么是半满的状态，空闲的簇会被系统自动回收。

3.空间限制

（1）用户占用的空间是其下所有用户表对象占用空间的总和。如创建用户 TEST_USER 时可指定该用户使用的最大磁盘空间为 80M。

CREATE USER TEST_USER IDENTIFIED BY TEST_PASSWORD DISKSPACE LIMIT 80;

（2）修改用户 TEST_USER 的磁盘空间限制为无限制。

ALTER USER TEST_USER DISKSPACE UNLIMITED;

（3）表对象占用的空间是其上所有索引占用空间的总和。如创建表 TEST 时可指定该表对象可使用的最大磁盘空间为 500M。

CREATE TABLE TEST (SNO INT, MYINFO VARCHAR) DISKSPACE LIMIT 500;

（4）对表对象空间的限制也可进行更改，如修改表 TEST 的磁盘空间限制为 50M。

ALTER TABLE TEST MODIFY DISKSPACE LIMIT 50;

4.查看占用的空间

关键字

说明

USER_USED_SPACE

查看用户占用空间

TABLE_USED_SPACE

查看表占用空间

INDEX_USED_SPACE

查看索引占用空间

例如：

SELECT USER_USED_SPACE('TEST_USER');
#查看用户占用的空间；函数参数为用户名，返回值为占用的页的数目
SELECT TABLE_USED_SPACE('SYSDBA', 'TEST');
#查看表占用的空间；函数参数为模式名和表名，返回值为占用的页的数目
SELECT TABLE_USED_PAGES('SYSDBA', 'TEST');
#查看表使用的页数；函数参数为模式名和表名，返回值为实际使用页的数目
SELECT INDEX_USED_SPACE(33555463);
#查看索引占用的空间；函数参数为索引ID，返回值为占用的页的数目
SELECT INDEX_USED_PAGES(33555463);
#查看索引使用的页数；函数参数为索引ID，返回值为实际使用页的数目。

二、管理表

1.设计表

  • 估算校正表结构
  • 为每个列选择合适的数据类型
  • 建立合适的完整性约束
  • 建立合适的聚集索引

2.创建表

（1）建表举例

例如：在 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);

填充因子：指在新建和重组索引时，页面记录存储空间占页面总大小的百分比（默认为100）

（2）表的聚簇索引（3种方式）

索引方式

说明

CLUSTER PRIMARY KEY

指定列为聚集索引键，并同时指定为主键

CLUSTER KEY

指定列为聚集索引键，但是是非唯一的

CLUSTER UNIQUE KEY

指定列为聚集索引键，并且是唯一的

例如：创建 student 表，指定 stu_no 为聚集主键

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

（3）临时表的关键词

关键词

说明

ON COMMIT DELETE ROWS

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

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;

3.更改表

  • 添加或删除列
  • 添加、修改或删除与表相关的完整性约束
  • 重命名一个表
  • 启动或停用与表相关的完整性约束
  • 启动或停用与表相关的触发器
  • 修改表的SPACE LIMIT （空间限制）
  • 增删自增列

4.删除表

DROP TABLE 表名;           #删除普通表
DROP TABLE IF EXISTS 表名; #删除不存在的表
DROP TABLE 表名 CASCADE;   #联级删除

5.清空表（清除表中的数据）

  1. 使用 DELETE 语句，能删除表中的行
DELETE FROM employee;
  1. 使用 DROP 和 CREATE 语句；
DROP TABLE EMPLOYEE;
CREATE TABLE EMPLOYEE(…);
  1. 使用 TRUNCATE 语句；如其他表的外键引用了表的任何主键或唯一键，并且子表不为空或子表的外键约束未被禁用，则不能 TRUNCATE 该表
TRUNCATE TABLE EMPLOYEE;

6.查看表的各种信息

关键字

说明

SP_TABLEDEF

查看表的定义

IDENT_CURRENT

获得表上自增列的当前值

IDENT_SEED

获得表上自增列的种子信息

IDENT_INCR

获得表上自增列的增量信息

TABLE_USED_SPACE

已分配给表的页面数

TABLE_USED_PAGES

表已使用的页面数

例如：

CALL SP_TABLEDEF('SYSDBA', 'EMPLOYEE');
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');
CREATE TABLE SPACE_TABLE ( C1 INT,
C2 INT
);
SELECT TABLE_USED_SPACE('SYSDBA','SPACE_TABLE');
SELECT TABLE_USED_PAGES('SYSDBA','SPACE_TABLE');

三、管理索引

1.索引的类型

索引类型

说明

聚集索引

每一个普通表有且只有一个聚集索引

唯一索引

索引数据根据索引键唯一

函数索引

包含函数/表达式的预先计算的值

位图索引

对低基数的列创建位图索引

位图连接索引

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

全文索引

在表的文本列上而建的索引

2.建立索引的条件

  • 列中的值相对比较唯一 ；
  • 取值范围大，适合建立索引；
  • CLOB 和 TEXT 只能建立全文索引、BLOB 不能建立任何索引

3.创建索引

3.1 创建索引的条件
  1. 要被索引的表是在自己的模式中 ；
  2. 在要被索引的表上有 CREATE INDEX 权限；
  3. 具有 CREATE ANY INDEX 数据库权限。
  4. 要在其他模式中创建索引，用户必须具有 CREATE ANY INDEX 数据库权限。
3.2常见创建索引的方法

创建索引关键字

说明

CREATE INDEX

直接创建索引

CREATE CLUSTER INDEX

创建聚集索引

CREATE UNIQUE INDEX

创建唯一索引

例如：
1）在 emp 表的 ename 列上创建一个名为 emp_ename 的索引，该索引使用表空间 users

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

4.删除索引

（1）用 CREATE INDEX 语句创建该索引的，可以用 DROP INDEX 语句删除该索引

DROP INDEX emp_ename;

（2）删除不存在的索引会报错。要指定 IF EXISTS 关键字
如：

DROP INDEX IF EXISTS emp_ename;

（3）不能直接删除与已启用的外键或主键约束相关的索引。必须停用或删除该约束本身。如下面的语句删除主键约束pk_emp_name，同时删除其对应的索引。

ALTER TABLE emp DROP CONSTRAINT pk_emp_name;

5.查看索引信息

通过 INDEXDEF 函数查看索引：

INDEXDEF(INDEX_ID int, PREFLAG int);

INDEX_ID 为索引 ID，PREFLAG 表示返回信息中是否增加模式名前缀。如要查看索引 emp_name 的定义，假设其索引 ID 为 1547892，那么使用以下语句查看索引定义。

SELECT INDEXDEF(1547892, 0); 
或 SELECT INDEXDEF(1547892, 1);