管理索引（二）-优快云博客

本文链接：https://blog.youkuaiyun.com/yuanymnl/article/details/144669293

离线索引重建

步骤

锁上表
通过读取现有索引的内容来创建一个新的临时索引。
删除原始索引。
重命名临时索引，使其看起来像原始索引。
移除表锁。

在下列情况下重建索引：

必须将现有索引移动到不同的表空间。如果索引与表位于相同的表空间中，或者需要跨磁盘重新分发对象，则可能需要这样做。
索引包含许多已删除的条目。这是滑动索引的典型问题，例如订单表的订单号索引，其中已完成的订单被删除，而编号更高的新订单被添加到表中。
如果有几个旧订单未完成，则可能有几个索引叶块，其中除了几个已删除的条目外，其他条目都已删除。已存在的正常索引必须转换为反向键索引。从Oracle服务器的早期版本迁移应用程序时可能会出现这种情况。
使用ALTER命令将索引表移动到另一个表空间表格。 ALTER TABLE ... MOVE TABLESPACE

在线重建索引

索引可以用最小的表锁定来重建。
ALTER INDEX orders_id_idx REBUILD ONLINE;

步骤

1.锁上表。
2.创建一个新的、临时的空索引和一个IOT来存储正在进行的DML
3.释放表锁。
4.通过读取现有索引的内容来填充临时索引。
5.将物联网的内容与新索引合并。
6.锁上表。
7.最终从IOT合并并删除原始索引。
8.重命名临时索引，使其看起来像the original index.
9.移除表锁。

合并碎片化索引

ALTER INDEX orders_id_idx COALESCE;

1.沿着索引的底部扫描。
2.如果相邻节点可以合并为单个节点，则这样做。

重建和融合索引的选用：
如果大多数索引都没什么碎片化，使用融合。基本上都有碎片，使用重建。

检查索引有效性

使用ANALYZE INDEX语句
- 在Oracle中，可以使用ANALYZE INDEX语句来验证索引结构的完整性。语法如下：
```
ANALYZE INDEX index_name VALIDATE STRUCTURE;
```
- 执行此操作后，Oracle会检查索引的结构是否正确，包括索引块之间的链接、索引条目是否符合排序规则等。如果索引存在问题，Oracle会在INDEX_STATS视图中记录相关信息（再次使用命令会覆盖）。可以通过查询这个视图来获取详细的检查结果。例如：
```
SELECT * FROM INDEX_STATS;
```
- 不过需要注意的是，从Oracle 11g开始，ANALYZE INDEX语句主要用于向后兼容，更推荐使用DBMS_STATS包来收集和分析统计信息。
使用DBMS_STATS包
- DBMS_STATS包提供了更强大和灵活的统计信息收集和验证功能。可以使用DBMS_STATS.GATHER_INDEX_STATS函数来收集索引的统计信息，同时也会对索引的有效性进行一定程度的检查。语法如下：
```
BEGIN
  DBMS_STATS.GATHER_INDEX_STATS(ownname => 'schema_name', indname => 'index_name');
END;   # `schema_name`是索引所属的模式（用户）名称，`index_name`是要检查的索引名称。
# eg:
BEGIN
  DBMS_STATS.GATHER_INDEX_STATS(ownname => 'HR', indname => 'employee_idx');
END;
```
- 这个函数会收集索引的相关统计数据，如索引的深度、叶子节点数等信息，并将这些数据存储在数据字典中。如果在收集统计信息的过程中发现索引结构有问题，会记录相应的错误信息。可以通过查询数据字典视图（如DBA_INDEXES等）来查看索引的状态和相关统计信息，以判断索引是否有效。
查询数据字典视图DBA_INDEXES或USER_INDEXES
- DBA_INDEXES视图（需要有相应的权限才能查询）和USER_INDEXES视图可以提供有关索引的基本信息，包括索引的状态。可以通过检查索引的状态列（如STATUS）来初步判断索引是否有效。例如：
```
SELECT index_name, status FROM DBA_INDEXES WHERE owner = 'schema_name';
```
- 正常情况下，索引的状态应该是VALID。如果索引状态显示为UNUSABLE，则表示索引可能由于某些原因（如在创建过程中出错、数据损坏等）无法正常使用，需要进一步检查和修复。
检查索引使用情况（间接判断有效性）
- 可以通过查询V$OBJECT_USAGE视图来检查索引是否被实际使用。虽然这不能直接证明索引是有效的，但如果一个索引从未被使用，可能存在一些问题（比如索引创建错误或者已经不再需要）。
  - 首先需要启用索引使用情况的跟踪，使用如下语句：
```
ALTER INDEX index_name MONITORING USAGE;
```
  - 经过一段时间后，可以查询V$OBJECT_USAGE视图来查看索引是否被使用。例如：
```
SELECT * FROM V$OBJECT_USAGE WHERE INDEX_NAME = 'index_name';
```
  - 如果USED列的值为YES，则表示索引在跟踪期间被使用过；如果为NO，则需要进一步分析是否真的需要这个索引。不过这种方法只是一个辅助手段，不能完全替代前面的结构完整性检查方法。

删除索引

在批量加载之前删除并重新创建索引。
删除不经常需要的索引，并在必要时构建索引。
删除并重新创建无效索引。
重建索引比删除冰重新创建索引快。
DROP INDEX hr.departments_name_idx；

监控索引

SELECT * FROM v$object_usage;  # 查询v$object_usage动态性能视图的所有列
ALTER INDEX t_idx1 MONITORING USAGE;  # 启用名为t_idx1的索引的使用情况跟踪

SELECT * FROM v$object_usage;  # 索引t_idx1的使用情况。如果在执行第二条语句后，有涉及到使用索引t_idx1的 SQL 操作，那么在第三次查询v$object_usage视图时，就可能会看到该索引的使用记录。

结果列说明（v$object_usage视图）

INDEX_NAME列：
- 显示被跟踪的索引名称。在这个例子中，如果一切正常，应该会看到t_idx1出现在这个列中。
TABLE_NAME列：
- 显示索引所属的表名称。这有助于确定索引是用于哪个表的操作。
MONITORING列：
- 用于显示是否启用了对该索引的使用情况跟踪。在执行了ALTER INDEX t_idx1 MONITORING USAGE;之后，这个列的值对于索引t_idx1应该是YES。
USED列：
- 这是最关键的一列，用于显示索引是否被使用。如果在启用跟踪后，有SQL操作使用了索引t_idx1，这个列的值会是YES；如果没有使用，这个列的值会是NO。
START_MONITORING列：
- 记录开始跟踪索引使用情况的时间戳。可以用于确定跟踪的起始时间，以便结合业务操作时间来分析索引使用情况。
END_MONITORING列：
- 当停止跟踪索引使用情况（通过ALTER INDEX... NOMONITORING USAGE语句）时，这个列会记录停止跟踪的时间戳。如果还在跟踪中，这个列的值通常是NULL。

获取索引信息

以下是关于可以获取索引信息的几个视图的详细介绍：

DBA_INDEXES视图

概述：
“DBA_INDEXES”视图是获取Oracle数据库中索引相关信息的重要途径，它涵盖了数据库内所有索引的元数据信息，数据库管理员常借此管理并深入了解索引结构。
常用查询列及其作用：
- 索引名称（INDEX_NAME）：该列展示的是索引的具体名称。例如，若针对某表的主键创建了索引，那这个索引的名称就会在此列显示出来。
- 表名（TABLE_NAME）：用于指明索引所属的表名称，方便明确索引的应用场景。比如，对于“员工（employees）”表上的某个索引，这里就会显示“employees”字样，让人清楚该索引是关联哪个表的。
- 索引类型（INDEX_TYPE）：告知索引的类型，常见的有B树（B - Tree，最为常用的一种类型，适用于基于范围的查询以及等值查找操作）、位图（Bitmap，更适合应用在列的基数较低，也就是列中不同取值数量较少的情况）等。
- 状态（STATUS）：索引状态分为“VALID（有效）”和“UNUSABLE（不可用）”两种。有效状态意味着该索引可被查询优化器调用，用以加速查询过程；而不可用状态通常是由于索引在创建或维护操作时出现错误导致的，这时就需要对其进行重建或修复等操作了。

示例查询：

SELECT INDEX_NAME, TABLE_NAME, INDEX_TYPE, STATUS
  FROM DBA_INDEXES
  WHERE OWNER = 'HR';

此查询能够检索出属于“HR”模式下所有索引的索引名称、相关联的表名、索引类型以及索引状态等信息。

DBA_IND_COLUMNS视图

概述：
“DBA_IND_COLUMNS”视图主要提供了构成索引的各列的详细信息，有助于理解索引的具体结构，比如清楚知晓是哪些列参与构建了索引以及这些列的先后顺序等情况。
常用查询列及其作用：
- 索引名称（INDEX_NAME）：和“DBA_INDEXES”视图里的作用类似，用于标识具体的索引。
- 表名（TABLE_NAME）：显示索引及其列所属的表名称，便于将索引与对应表的操作关联起来分析。
- 列名（COLUMN_NAME）：这是很关键的一个列，它会列出参与构建索引的各列名称。例如，针对“员工（employees）”表创建了一个索引，用到了“员工编号（employee_id）”和“部门编号（department_id）”这两个列，那这里就会分别显示这两个列名。
- 列位置（COLUMN_POSITION）：该列体现了列在索引中的位置顺序。在复合索引（即由多个列构成的索引）中，列的顺序十分重要，此列能让人清晰了解到在索引里各列是如何排序的。比如，若“员工编号（employee_id）”的列位置显示为1，“部门编号（department_id）”显示为2，那就意味着索引先是依据“员工编号”进行排序，对于“员工编号”相同的行，再按照“部门编号”进一步排序。

示例查询：

SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME, COLUMN_POSITION
  FROM DBA_IND_COLUMNS
  WHERE TABLE_NAME = 'EMPLOYEES';

通过这个查询，可以获取针对“员工（employees）”表所创建的各个索引的相关信息，包含索引名称、表名、被索引的列名以及各列在索引中的具体位置情况，从而清晰掌握索引的详细构建情况。

V$OBJECT_USAGE视图

概述：
“V$OBJECT_USAGE”视图重点提供索引使用情况方面的相关信息，借助它可以了解某个索引在数据库实际运行期间是否真正被使用，这对于数据库管理人员判断索引的有效性、决定是否继续保留该索引等工作有着重要的参考价值，有助于优化数据库的索引结构。
常用查询列及其作用：
- 索引名称（INDEX_NAME）：用来明确具体是哪个索引的使用情况被记录在此处，便于快速定位和识别相应的索引对象。
- 表名（TABLE_NAME）：显示该索引所属的表，方便结合表的相关业务操作来综合分析索引的使用场景。
- 监测情况（MONITORING）：该列的值表明是否正在对索引的使用情况进行监测，若执行了开启监测的相关操作（例如使用“ALTER INDEX… MONITORING USAGE”语句），此列的值就会显示为“YES”，意味着正在进行监测；反之则为“NO”。
- 是否使用（USED）：这是最为关键的一列，当值为“YES”时，表示在监测期间该索引被实际使用了；若值为“NO”，则代表在相应时间段内该索引未被启用，据此可初步判断索引在实际业务中有没有发挥作用。
- 开始监测时间（START_MONITORING）：记录了开始对索引使用情况进行监测的时间戳，有助于结合业务操作的时间范围等因素，全面分析索引的使用规律和频率等情况。
- 结束监测时间（END_MONITORING）：当执行了停止监测的操作（比如通过“ALTER INDEX… NOMONITORING USAGE”语句）后，该列会记录停止监测的时间戳；要是仍处于监测状态，此列的值通常为“NULL”。

示例查询：

SELECT INDEX_NAME, TABLE_NAME, MONITORING, USED, START_MONITORING, END_MONITORING
  FROM V$OBJECT_USAGE
  WHERE TABLE_NAME = '某表名称';

通过以上查询，可以获取针对名为“某表名称”的表上相关索引的使用情况信息，进而分析这些索引在实际业务场景中是否经常被使用、有没有真正起到优化查询的作用等情况，以便后续根据分析结果来决定是保留、优化还是删除相应的索引。

一些命令

SELECT object_name, object_type FROM user_objects; # 查询对象，可以查询存在的视图

SELECT count(*) FROM index_stats;
ANALYZE INDEX t_idx 1 VALIDATE STRUCTURE; 
SELECT height,name,lf_rows,lf_blks,del_lf_rows FROM index_stats;  # 通过对比总行和删除的行，判断索引的碎片化程度。