印刻数据库

外观

DM 索引创建与维护 ​

索引创建的基本原则 ​

  • 基于查询模式：根据实际查询需求创建索引
  • 考虑数据分布：根据数据的分布情况选择合适的索引类型
  • 平衡查询和更新性能：索引可以提高查询性能，但会降低更新性能
  • 避免过度索引：过多的索引会增加存储开销和维护成本
  • 定期评估和调整：根据索引使用情况定期调整索引策略

索引创建方法 ​

使用CREATE INDEX语句创建 ​

创建单列索引 ​

sql
-- 创建单列B树索引
CREATE INDEX idx_emp_deptno ON emp(deptno);

-- 创建单列唯一索引
CREATE UNIQUE INDEX idx_emp_empno ON emp(empno);

-- 创建单列位图索引
CREATE BITMAP INDEX idx_emp_gender ON emp(gender);

创建复合索引 ​

sql
-- 创建复合B树索引
CREATE INDEX idx_emp_deptno_ename ON emp(deptno, ename);

-- 创建复合唯一索引
CREATE UNIQUE INDEX idx_emp_deptno_job ON emp(deptno, job);

创建函数索引 ​

sql
-- 创建基于函数的索引
CREATE INDEX idx_emp_hire_year ON emp(YEAR(hiredate));

-- 创建基于表达式的索引
CREATE INDEX idx_emp_salary_bonus ON emp(salary + NVL(bonus, 0));

-- 创建基于多函数的索引
CREATE INDEX idx_emp_name_upper ON emp(UPPER(ename), UPPER(job));

创建全文索引 ​

sql
-- 创建全文索引
CREATE FULLTEXT INDEX idx_article_content ON article(content);

-- 创建带有分词配置的全文索引
CREATE FULLTEXT INDEX idx_article_title ON article(title) WITH (tokenizer = 'chinese');

创建空间索引 ​

sql
-- 创建空间索引
CREATE SPATIAL INDEX idx_location_coords ON location(coords);

-- 创建带有参数的空间索引
CREATE SPATIAL INDEX idx_location_area ON location(area) WITH (srid = 4326);

使用DM管理工具创建 ​

  1. 启动DM管理工具，连接到数据库实例
  2. 在左侧导航树中选择目标表
  3. 右键点击表名，选择"创建索引"
  4. 在弹出的"创建索引"对话框中，设置以下参数：
    • 索引名称
    • 索引类型
    • 索引列
    • 索引参数
  5. 点击"确定"按钮，创建索引

使用ALTER TABLE语句创建 ​

sql
-- 使用ALTER TABLE语句创建索引
ALTER TABLE emp ADD INDEX idx_emp_job (job);

-- 使用ALTER TABLE语句创建唯一索引
ALTER TABLE emp ADD UNIQUE INDEX idx_emp_email (email);

索引维护操作 ​

查看索引信息 ​

查询数据字典 ​

sql
-- 查询用户所有索引
SELECT * FROM USER_INDEXES;

-- 查询表的所有索引
SELECT * FROM USER_INDEXES WHERE TABLE_NAME = 'EMP';

-- 查询索引列信息
SELECT * FROM USER_IND_COLUMNS WHERE TABLE_NAME = 'EMP';

-- 查询索引状态
SELECT INDEX_NAME, STATUS FROM USER_INDEXES WHERE TABLE_NAME = 'EMP';

使用DM管理工具查看 ​

  1. 启动DM管理工具，连接到数据库实例
  2. 在左侧导航树中选择目标表
  3. 展开表节点，点击"索引"
  4. 在右侧窗口中查看索引信息

修改索引 ​

重命名索引 ​

sql
-- 重命名索引
ALTER INDEX idx_emp_deptno RENAME TO idx_emp_department;

修改索引参数 ​

sql
-- 修改索引参数
ALTER INDEX idx_emp_deptno PARAMETERS ('FILLFACTOR = 80');

重建索引 ​

使用ALTER INDEX语句重建 ​

sql
-- 重建单个索引
ALTER INDEX idx_emp_deptno REBUILD;

-- 重建索引并修改参数
ALTER INDEX idx_emp_deptno REBUILD PARAMETERS ('FILLFACTOR = 80');

-- 在线重建索引（DM 8及以上版本）
ALTER INDEX idx_emp_deptno REBUILD ONLINE;

使用REBUILD INDEX语句重建 ​

sql
-- 重建所有索引
REBUILD INDEX;

-- 重建指定表的所有索引
REBUILD INDEX ON emp;

-- 重建指定用户的所有索引
REBUILD INDEX FOR USER scott;

使用DM管理工具重建 ​

  1. 启动DM管理工具，连接到数据库实例
  2. 在左侧导航树中选择目标表
  3. 展开表节点，点击"索引"
  4. 右键点击要重建的索引，选择"重建索引"
  5. 在弹出的对话框中设置重建参数
  6. 点击"确定"按钮，重建索引

重组索引 ​

使用ALTER INDEX语句重组 ​

sql
-- 重组单个索引
ALTER INDEX idx_emp_deptno COALESCE;

使用COALESCE INDEX语句重组 ​

sql
-- 重组所有索引
COALESCE INDEX;

-- 重组指定表的所有索引
COALESCE INDEX ON emp;

删除索引 ​

使用DROP INDEX语句删除 ​

sql
-- 删除单个索引
DROP INDEX idx_emp_deptno;

-- 级联删除依赖于该索引的对象
DROP INDEX idx_emp_deptno CASCADE;

使用DM管理工具删除 ​

  1. 启动DM管理工具，连接到数据库实例
  2. 在左侧导航树中选择目标表
  3. 展开表节点，点击"索引"
  4. 右键点击要删除的索引，选择"删除"
  5. 在弹出的确认对话框中点击"确定"按钮，删除索引

索引管理最佳实践 ​

1. 定期监控索引状态 ​

  • 监控索引碎片：定期检查索引碎片情况，碎片率超过20%时考虑重建或重组
  • 监控索引使用率：识别未使用或很少使用的索引，及时清理
  • 监控索引性能：比较索引创建前后的查询性能

2. 合理选择重建或重组 ​

  • 重建索引(REBUILD)：适用于索引碎片严重、索引结构需要优化的情况

    • 优点：可以重新组织索引结构，提高查询性能
    • 缺点：需要更多的存储空间和时间

  • 重组索引(COALESCE)：适用于索引碎片较轻的情况

    • 优点：不需要额外的存储空间，执行速度快
    • 缺点：只能合并相邻的索引块，无法完全优化索引结构

3. 选择合适的索引维护时机 ​

  • 选择系统负载较低的时段进行索引维护
  • 避免在业务高峰期进行索引重建
  • 考虑使用在线重建索引（DM 8及以上版本）

4. 备份索引相关信息 ​

  • 备份索引定义脚本，便于在需要时重新创建
  • 记录索引创建的原因和预期效果
  • 定期备份索引统计信息

5. 测试索引维护效果 ​

  • 在测试环境中测试索引维护效果
  • 比较维护前后的索引性能
  • 验证查询计划是否得到优化

6. 考虑分区索引维护 ​

  • 对于分区表，考虑只维护特定分区的索引
  • 使用局部索引减少维护开销
  • 考虑分区索引的并行维护

索引监控 ​

使用系统视图监控 ​

V$INDEX_USAGE ​

sql
-- 查看索引使用情况
SELECT * FROM V$INDEX_USAGE;

-- 查看特定表的索引使用情况
SELECT * FROM V$INDEX_USAGE WHERE TABLE_NAME = 'EMP';

-- 查看未使用的索引
SELECT * FROM V$INDEX_USAGE WHERE USAGE_COUNT = 0;

V$INDEX_STATS ​

sql
-- 查看索引统计信息
SELECT * FROM V$INDEX_STATS;

-- 查看索引碎片情况
SELECT INDEX_NAME, LEAF_BLOCKS, DEL_LF_ROWS, 
       ROUND((DEL_LF_ROWS / LEAF_BLOCKS) * 100, 2) AS FRAGMENTATION_RATE
FROM V$INDEX_STATS;

DBA_INDEXES ​

sql
-- 查看索引状态和参数
SELECT INDEX_NAME, STATUS, TABLE_NAME, UNIQUENESS, 
       INITIAL_EXTENT, NEXT_EXTENT, PCT_INCREASE
FROM DBA_INDEXES WHERE OWNER = 'SCOTT';

使用DM性能监控工具监控 ​

  1. 启动DM性能监控工具，连接到数据库实例
  2. 在左侧导航树中选择"性能" -> "索引监控"
  3. 在右侧窗口中查看索引使用情况、碎片情况等
  4. 设置索引监控参数和告警规则

索引统计信息 ​

收集索引统计信息 ​

使用ANALYZE语句 ​

sql
-- 收集表和索引的统计信息
ANALYZE TABLE emp COMPUTE STATISTICS;

-- 收集索引的统计信息
ANALYZE INDEX idx_emp_deptno COMPUTE STATISTICS;

-- 收集表的统计信息并估算索引统计信息
ANALYZE TABLE emp ESTIMATE STATISTICS SAMPLE 20 PERCENT;

使用DBMS_STATS包 ​

sql
-- 收集表和索引的统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMP');

-- 收集索引的统计信息
EXEC DBMS_STATS.GATHER_INDEX_STATS('SCOTT', 'IDX_EMP_DEPTNO');

-- 收集模式下所有对象的统计信息
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');

查看索引统计信息 ​

sql
-- 查看索引统计信息
SELECT * FROM USER_INDEXES WHERE TABLE_NAME = 'EMP';

-- 查看索引列统计信息
SELECT * FROM USER_IND_COLUMNS WHERE TABLE_NAME = 'EMP';

-- 查看索引直方图信息
SELECT * FROM USER_HISTOGRAMS WHERE TABLE_NAME = 'EMP';

版本差异说明 ​

版本主要变化
DM 7支持基本索引创建和维护操作
DM 8增强了索引功能，支持在线索引重建、更多索引类型
DM 8.1优化了索引维护性能，支持并行索引维护

常见问题（FAQ） ​

Q1: 如何选择重建或重组索引？ ​

A1: 重建或重组索引的选择取决于索引的碎片情况和维护需求：

  • 碎片率 < 20%：考虑使用COALESCE重组索引
  • 碎片率 ≥ 20%：考虑使用REBUILD重建索引
  • 需要修改索引参数：使用REBUILD重建索引
  • 系统资源有限：考虑使用COALESCE重组索引

Q2: 在线重建索引和离线重建索引有什么区别？ ​

A2: 在线重建索引和离线重建索引的主要区别：

  • 在线重建：允许在重建过程中对表进行DML操作，不阻塞业务
  • 离线重建：在重建过程中会锁定表，不允许DML操作
  • 资源消耗：在线重建需要更多的系统资源
  • 适用场景：在线重建适用于业务不能中断的场景，离线重建适用于维护窗口

Q3: 如何识别未使用的索引？ ​

A3: 可以通过以下方法识别未使用的索引：

  • 查询V$INDEX_USAGE视图，查看USAGE_COUNT为0的索引
  • 使用DM性能监控工具查看索引使用情况
  • 分析AWR报告中的索引使用统计
  • 在测试环境中删除索引并测试性能影响

Q4: 索引重建会影响数据库性能吗？ ​

A4: 索引重建会对数据库性能产生一定影响：

  • 消耗CPU和内存资源
  • 占用存储空间
  • 离线重建会锁定表，影响业务
  • 在线重建会生成大量重做日志

建议在系统负载较低时进行索引重建，并合理安排重建顺序。

Q5: 如何优化索引重建性能？ ​

A5: 可以通过以下方法优化索引重建性能：

  • 选择合适的重建时机（系统负载低时）
  • 使用并行重建（DM 8.1及以上版本）
  • 调整索引参数（如FILLFACTOR）
  • 考虑使用NOLOGGING选项减少日志生成（仅适用于非生产环境）

Q6: 如何备份索引定义？ ​

A6: 可以通过以下方法备份索引定义：

  • 使用DM管理工具导出索引脚本
  • 使用DBMS_METADATA包生成索引DDL
  • 编写SQL脚本查询数据字典生成索引DDL
sql
-- 使用DBMS_METADATA包生成索引DDL
SELECT DBMS_METADATA.GET_DDL('INDEX', INDEX_NAME, OWNER) FROM DBA_INDEXES WHERE OWNER = 'SCOTT';

Q7: 如何处理索引创建失败？ ​

A7: 索引创建失败的常见原因和解决方法：

  • 存储空间不足：扩展表空间或清理磁盘空间
  • 权限不足：授予足够的索引创建权限
  • 索引名称已存在：使用不同的索引名称
  • 数据重复：对于唯一索引，检查并处理重复数据
  • 语法错误：检查CREATE INDEX语句语法

Q8: 如何监控索引碎片？ ​

A8: 可以通过以下方法监控索引碎片：

  • 查询V$INDEX_STATS视图中的DEL_LF_ROWS和LEAF_BLOCKS列
  • 使用DM性能监控工具的索引碎片监控功能
  • 定期生成索引碎片报告

Q9: 如何批量重建索引？ ​

A9: 可以通过以下方法批量重建索引：

  • 使用REBUILD INDEX语句重建所有索引
  • 使用PL/SQL脚本批量重建特定条件的索引
  • 使用DM管理工具的批量重建功能
sql
-- 使用PL/SQL脚本批量重建索引
BEGIN
  FOR rec IN (SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'EMP') LOOP
    EXECUTE IMMEDIATE 'ALTER INDEX ' || rec.INDEX_NAME || ' REBUILD';
  END LOOP;
END;
/

Q10: 索引维护的最佳频率是多少？ ​

A10: 索引维护的最佳频率取决于以下因素：

  • 数据更新频率：更新频繁的表需要更频繁的维护
  • 数据量增长速度：数据增长快的表需要更频繁的维护
  • 索引类型：不同索引类型的维护频率不同
  • 业务需求：根据业务对性能的要求调整维护频率

一般建议：

  • 数据仓库：每月或每季度维护一次
  • OLTP系统：每季度或每半年维护一次
  • 频繁更新的表：每月维护一次

在进行索引维护时，DBA需要考虑维护时机、维护方法和维护效果，选择合适的重建或重组策略，并在测试环境中验证维护效果。同时，需要监控索引使用情况，及时清理未使用的索引，避免过度索引导致的性能问题。

通过遵循索引创建和维护的最佳实践，可以有效地提高DM数据库的性能，为业务提供高效、稳定的数据服务。