一、管理索引的准则

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;

				

					
				
				

					
				
				

					
				
				

					
				
			

索引监控的应用

可以使用索引监控功能，将长期未使用的索引删除。

（1）开启对某个索引监控

ALTER INDEX INDEX_NAME MONITORING USAGE;

				

					
				
				

					
				
				

					
				
				

					
				
			

（2）系统运行一段时间后，查看系统视图

SELECT * FROM SYS.V$OBJECT_USAGE; --USED列为NO，表示没有使用过，可考虑删除

				

					
				
				

					
				
				

					
				
				

					
				
			

（3）若索引的确有使用，则关闭索引监控

ALTER INDEX INDEX_NAME NOMONITORING 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 进行批量禁用 / 启用。

方法二：

--禁用
SELECT
    'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' DISABLE CONSTRAINT ' || CONSTRAINT_NAME || ';'
FROM
    SYS.DBA_CONSTRAINTS
WHERE
    OWNER NOT LIKE 'SYS%'
    AND CONSTRAINT_TYPE = 'R';
--启用
SELECT
    'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' ENABLE CONSTRAINT ' || CONSTRAINT_NAME || ';'
FROM
    SYS.DBA_CONSTRAINTS
WHERE
    OWNER NOT LIKE 'SYS%'
    AND CONSTRAINT_TYPE = 'R';

				

					
				
				

					
				
				

					
				
				

					
				
			

（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