一、管理索引的准则

1. 在表中插入数据后创建索引
2. 索引正确的表和列
3. 为性能而安排索引列
4. 限制每个表的索引的数量
5. 估计索引大小和设置存储参数
6. 为每个索引指定表空间

以上准则详细内容参考数据库安装路径doc目录下的《DM8系统管理员手册.pdf》。

二、管理约束

约束类型

• NOT NULL：非空
• UNIQUE：唯一
• PRIMARY KEY：一个表只能由一个主键，主键唯一且非空。
• FOREIGN KEY：外键引用另一张表的主键或者唯一键。
• CHECK：数据检验

添加约束

（1）添加非空

ALTER TABLE TEST.TESTTB MODIFY NAME NOT NULL;

（2）添加主键

ALTER TABLE TEST.TESTTB ADD CONSTRAINT "PK_TB_ID" PRIMARY KEY(ID);

（3）添加唯一键

ALTER TABLE TEST.TESTTB ADD CONSTRAINT "UQ_TB_EM" UNIQUE(EMAIL);

（4）添加数据检验

ALTER TABLE TEST.TESTTB ADD CONSTRAINT "CK_TB_SL" CHECK(SALARY>3000);

（5）添加外键

ALTER TABLE TEST.TESTFK ADD CONSTRAINT "FK_TF_ID" FOREIGN KEY(ID) REFERENCES TEST.TESTTB(ID);

三、管理索引

索引分类：

• 聚簇索引
• 二级索引（B 树索引）
• 位图索引
• 函数索引
• 全文索引
• 复合索引
• 二级普通索引

索引是一种数据库对象，通过指针加速查询速度，通过快速定位数据的方法，减少磁盘I/O，索引与表相互独立，索引和表都占用存储空间，DML 操作服务器自动使用和维护索引。
大表中要查询少量的数据，在查询列上创建索引。
多表关联，关联列可以创建索引。
创建索引是为了加快查询速度。

收集统计信息：DBMS_STATUS 系统包

--收集指定用户下所有索引的统计信息：
DBMS_STATS.GATHER_SCHEMA_STATS('USENAME',1.0,TRUE,'FOR ALL INDEXED SIZE AUTO');
--或 收集单个索引统计信息：
DBMS_STATS.GATHER_INDEX_STATS('USERNAME','INDEXNAME');
--表上所有的索引生成统计信息
CALL SP_TAB_INDEX_STAT_INIT ('USERNAME', 'TABLENAME');

索引的重建

ALTER INDEX TEST.IND_EMP_NAME REBUILD ONLINE;

索引的监控

ALTER INDEX TEST.IND_EMP_NAME MONITORING USAGE;
ALTER INDEX TEST.IND_EMP_NAME NOMONITORING USAGE;
--监控查询
SELECT * FROM V$OBJECT_USAGE;

四、其他SQL

（1）查询表的索引信息

SELECT
	TABLE_OWNER AS "表归属",
	TABLE_NAME AS "表名",
	OWNER AS "索引归属",
	INDEX_NAME AS "索引名",
	INDEX_TYPE AS "索引类型",
	TABLESPACE_NAME AS "索引所在表空间",
	JOIN_INDEX AS "是否组合索引",
	DECODE(VISIBILITY, 'VISIBLE', '可见', 'INVISIBLE', '不可见') AS "索引是否可见"
FROM
	DBA_INDEXES
WHERE
	OWNER = '模式名';

（2）导出用户下所有索引的定义

SELECT DISTINCT
	'SELECT DBMS_METADATA.GET_DDL(' || CHR(39) || 'INDEX' || CHR(39) || ',' || CHR(39) || INDEX_NAME || CHR(39) || ',' || CHR(39) || OWNER || CHR(39) || ') FROM DUAL UNION'
FROM
	DBA_INDEXES
WHERE
	DBA_INDEXES.OWNER NOT IN (
		'SYS',
		'SYSAUDITOR',
		'SYSSSO',
		'CTISYS',
		'SYSDBA',
		'SYSJOB'
	);

使用结果集生成的SQL查询所有的索引定义并UNION ALL，再导出。

（3）查询用户下所有的外键

SELECT
	R.OWNER AS "模式名",
	R.CONSTRAINT_NAME AS "约束名",
	R.CONSTRAINT_TYPE AS "约束类型",--R 表示外键
	R.TABLE_NAME AS "子表(从表)",
	F.TABLE_NAME AS "父表(主表)",
	C.COLUMN_NAME
FROM
	DBA_CONSTRAINTS R
	JOIN DBA_CONS_COLUMNS C ON R.CONSTRAINT_NAME = C.CONSTRAINT_NAME
	JOIN DBA_CONSTRAINTS F ON R.R_CONSTRAINT_NAME = F.CONSTRAINT_NAME
WHERE
	R.OWNER NOT IN (
		'SYS',
		'SYSAUDITOR',
		'SYSSSO',
		'CTISYS',
		'SYSDBA',
		'SYSJOB'
	)
	AND R.CONSTRAINT_TYPE = 'R';

（4）批量禁用或启用外键

WITH
	T AS (
		SELECT
			R.OWNER,
			R.CONSTRAINT_NAME,
			R.CONSTRAINT_TYPE, --R 表示外键
			R.TABLE_NAME AS "CHILD_TABLE_NAME",
			F.TABLE_NAME AS "PARENT_TABLE_NAME",
			C.COLUMN_NAME
		FROM
			DBA_CONSTRAINTS R
			JOIN DBA_CONS_COLUMNS C ON R.CONSTRAINT_NAME = C.CONSTRAINT_NAME
			JOIN DBA_CONSTRAINTS F ON R.R_CONSTRAINT_NAME = F.CONSTRAINT_NAME
		WHERE
			R.OWNER NOT IN (
				'SYS',
				'SYSAUDITOR',
				'SYSSSO',
				'CTISYS',
				'SYSDBA',
				'SYSJOB'
			)
			AND R.CONSTRAINT_TYPE = 'R'
	)
SELECT
	'ALTER TABLE ' || T.OWNER || '.' || T.CHILD_TABLE_NAME || ' DISABLE CONSTRAINT ' || T.CONSTRAINT_NAME || ';' AS "SQL" --批量禁用外键
	--'ALTER TABLE ' || T.OWNER || '.' || T.CHILD_TABLE_NAME || ' ENABLE CONSTRAINT ' || T.CONSTRAINT_NAME || ';' AS "SQL" --批量启用外键
FROM T;

使用结果集生成的SQL进行批量禁用/启用。

（5）查询列上的所有索引

SELECT
	INDEX_NAME,
	COLUMN_NAME
FROM
	DBA_IND_COLUMNS
WHERE
	TABLE_NAME = 'TABLE_NAME'
	AND COLUMN_NAME = 'COLUMN_NAME';

（6）自定义函数使用索引

函数加上DETERMINISTIC修改成确定性函数

CREATE OR REPLACE FUNCTION …
RETURN …
DETERMINISTIC
AS
BEGIN
…
END;

然后就可以使用上索引了。

五、相关数据字典

约束的相关数据字典

• DBA_CONSTRAINTS
• DBA_CONS_COLUMNS

索引的相关数据字典

• DBA_INDEXES
• DBA_IND_COLUMNS