自学内容网 自学内容网
自学内容网 > 正文

Oracle 深入学习 Part12: Managing Indexes (管理索引)

🕗 发布于 2025-01-17 13:51 oracle  学习  数据库  

索引的分类

逻辑分类：

  • 单列和多列：

    • 单列索引（single column）：对单列数据建立索引。

    • 复合索引（concatenated）：对多列数据建立索引。

  • 唯一性：

    • 唯一性（unique）：建立的key值唯一。

    • 非唯一性（nonumique）：不唯一。

  • （function-based）:在列上应用函数或表达式创建的索引。

  • （domain）：用于用户定义的数据类型，通常需要自定义操作符或索引逻辑。

物理分类：

  • 分区：

    • 分区（partitioned）：对大表分区存储，并为每个分区分别创建索引。

    • 不分区（nonpartitioned）:

  • B树（B-tree）：最常用的索引类型，基于平衡 B-树结构存储，适合高基数（列中值较多）的数据。

  • 位图索引（Bitmap）:使用位图存储数据，适合低基数（列中值较少）的数据。

索引的结构

索引本身在oracle操作中不被直接引用。

索引本身是排序的，查找数据时先查找根节点，找到大致范围，再查询对应分支节点，再查询子节点找到rowid，确定数据的具体位置。

在底层的叶子节点间，组成了双向链表。

B-tree Index（B树索引）

B树是平衡树，root为根节点，branch为分支节点，leaf为叶子节点，每个节点相当于一个块。

所有index entry都存储在叶子节点中，每一个index entry对应着一条记录。

Key column length:key的长度

Key column value:key的值

ROWID：唯一值，相当于指针

Bitmap Index（位图索引）

适用条件：

        当某列取值只有固定的几个值时

每个叶子节点存储信息的固定格式：

        <key(j键值)， start ROWID(起始ROWID) , end ROWID(终止ROEID) , bitmap(01字符串，0代表不是，1代表是)

优点：

        计算速度快。

B树 (B-tree)位图 (Bitmap)
适合高基数列适合低基数列
更新键相对便宜更新键列非常昂贵
对使用OR谓词的查询效率低下对使用OR谓词的查询效率高
适用于OLTP适用于数据仓库

创建索引

索引独立于表，有自己单独的存储位置

CREATE [UNIQUE] [BITMAP] INDEX index_name
ON table_name(column1 [ASC|DESC], column2 [ASC|DESC], ...)
[TABLESPACE tablespace_name]
[PCTFREE n]
[INITRANS n]
[MAXTRANS n]
[STORAGE];

  • UNIQUE

    • 用于创建唯一索引，确保列中的值是唯一的。

    • 适用于主键（PRIMARY KEY）或唯一约束（UNIQUE CONSTRAINT）。

  • BITMAP

    • 指定创建位图索引，仅在支持位图索引的数据库（如 Oracle）中可用。

    • 适合低基数列。

  • index_name

    • 索引的名称，必须在同一模式下唯一。

    • 通常使用命名规则，如 表名_列名_idx。

  • table_name

    • 表的名称。

  • column1, column2

    • 指定索引列，可以单列或多列。

    • 每列可以指定 ASC（升序）或 DESC（降序），默认是升序。

  • TABLESPACE tablespace_name

    • 指定索引存储的表空间（仅在 Oracle 中需要）。

  • PCTFREE n

    • 定义索引块中的可用空间百分比。

      • 值范围为 0 到 99，默认是 10（表示索引块预留 10% 空间用于更新）。

  • INITRANS n

    • 定义索引块中初始分配的事务槽数量。

      • n 的默认值通常是 2。

  • MAXTRANS n

    • 定义索引块中允许的最大事务数。

      • 该值限制事务槽的最大数量。

  • [STORAGE]

    • 指定索引的存储参数。

      • 控制索引的物理存储特性，包括初始大小、增长方式等。

    • 常见参数：

      • INITIAL：分配的初始存储空间。

      • NEXT：每次扩展的存储空间。

      • PCTINCREASE：每次扩展时的增长百分比。

      • MINEXTENTS 和 MAXEXTENTS：指定最小和最大扩展数量。

重构索引

ALTER INDEX index_name STORAGE (
    INITIAL size
    NEXT size
    PCTINCREASE percentage
    MINEXTENTS n
    MAXEXTENTS n
);

重新构建索引（REBUILD）

普通重建：

ALTER INDEX index_name REBUILD [TABLESPACE tablespace_name];

  • 在普通重建过程中，索引及其对应的表会被完全锁定，不能进行任何 DML 操作（如 INSERT、UPDATE、DELETE）。

在线重建：

ALTER INDEX index_name REBUILD [TABLESPACE tablespace_name] ONLINE;

在重建过程中，允许用户对表进行查询和数据修改（DML），仅在索引重建的开始和结束阶段有短暂锁定。

普通重构的过程

  • 1.锁定表。

  • 2.通过读取现有索引的内容创建一个新的临时索引。

  • 3.删除原始索引。

  • 4.重命名临时索引，使其看起来像原始索引。

  • 5.移除表锁。

在线重构的过程

  • 1.锁定表。

  • 2.创建一个新的、临时的空索引，并创建一个 IOT（索引组织表）来存储正在进行的 DML 操作。

  • 3.释放表锁。

  • 4.通过读取现有索引的内容填充临时索引。

  • 5.将 IOT 的内容合并到新索引中。

  • 6.锁定表。

  • 7.进行 IOT 的最终合并并删除原始索引。

  • 8.重命名临时索引，使其看起来像原始索引。

  • 9.移除表锁。

Coalescing Indexes（合并索引）

Coalescing Indexes（合并索引）是 Oracle 数据库中优化索引的一种方法，其目的是减少索引的碎片，优化空间利用，同时保持索引的可用性。

语法

ALTER INDEX index_name COALESCE;

合并索引 vs 重建索引

特性合并索引（Coalesce Index）重建索引（Rebuild Index）
影响可用性不影响（表和索引可用）可能锁定表（普通重建会锁定表）
操作范围仅合并叶块，无需重建整个索引重建整个索引
碎片清理减少碎片，但不清理整个索引清理整个索引
性能消耗较低较高
适用场景空间优化，尤其是分区索引索引整体性能优化或表空间调整

检查索引的有效性（Index Validity）

确保索引没有损坏并且能够正常工作。

ANALYZE INDEX index_name VALIDATE STRUCTURE;

  • 相关结果会存储在数据字典视图 INDEX_STATS 中。

执行完命令后，可以通过以下查询检查结果：

SELECT * FROM INDEX_STATS;

删除索引

DROP INDEX index_name;

  • 不会影响表数据：删除索引仅移除索引结构，表中的数据不会被删除。

  • 检查依赖性：

    • 索引删除时，与之相关的唯一性约束或主键约束也会被删除（需谨慎）。

    • 如果索引是自动创建的（如主键或唯一性约束的隐式索引），应先删除约束，再删除索引。

  • 表锁定：删除索引可能导致表的短暂锁定。

识别未使用的索引（Identifying Unused Indexes）

dentifying Unused Indexes（识别未使用的索引）是数据库优化的重要步骤，可以帮助减少存储空间、降低维护成本并提升整体性能。

1.启用索引监控

ALTER INDEX index_name MONITORING USAGE;

2,运行一段时间

  • 保持监控开启，通常建议运行几天到几周，确保监控期间涵盖了所有典型的业务操作。

3.查看索引使用情况

查询 V$OBJECT_USAGE 视图检查索引是否被访问。

SELECT INDEX_NAME, TABLE_NAME, MONITORING, USED 
FROM V$OBJECT_USAGE 
WHERE INDEX_NAME = 'index_name';

4.停止索引监控 一旦完成监控，可以关闭监控功能。

ALTER INDEX index_name NOMONITORING USAGE;

相关视图

DBA_INDEXES:

提供数据库中所有索引的相关统计信息，包括索引的类型、状态、存储位置和其他重要属性。

常用字段

  • INDEX_NAME：索引名称。

  • TABLE_NAME：索引所关联的表名。

  • INDEX_TYPE：索引类型（如 NORMAL、BITMAP、FUNCTION-BASED NORMAL）。

  • UNIQUENESS：是否唯一索引（UNIQUE 或 NONUNIQUE）。

  • STATUS：索引的状态（VALID 或 UNUSABLE）。

  • TABLESPACE_NAME：索引存储的表空间。

  • BLEVEL：索引的 B-树层级（越小性能越好）。

  • NUM_ROWS：索引所覆盖的表中行数。

DBA_IND_COLUMNS:

提供每个索引的列信息，用于详细描述索引的结构

常用字段

  • INDEX_NAME：索引名称。

  • TABLE_NAME：索引对应的表名。

  • COLUMN_NAME：索引使用的列名。

  • COLUMN_POSITION：列在索引中的位置（多列索引时列的顺序）。

  • DESCEND：列是否以降序存储（ASC 或 DESC）。

X$OBJECT_USAGE:

X$OBJECT_USAGE 是 Oracle 的内部动态性能表，用于跟踪索引的使用情况（与 V$OBJECT_USAGE 密切相关）。

常用字段

  • INDEX_NAME：索引名称。

  • OBJECT_ID：对象 ID。

  • USED：索引是否被使用（YES 或 NO）。

  • MONITORING：是否正在监控索引使用情况（YES 或 NO）。


原文地址：https://blog.csdn.net/2302_80755459/article/details/145187921

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2025