Oracle 21c 新特性 自动索引增强

Report Content

Issue: *
Copyright Infringment Spam Invalid Contents Broken Links

Your Name: *

Your Email: *

Details: *

Submit Report

自动索引介绍

自动索引功能可自动执行 Oracle 数据库中的索引管理任务。自动索引会根据应用程序工作负载的变化自动创建、重建和删除数据库中的索引，从而提高数据库性能。自动管理的索引称为自动索引。

索引结构是数据库性能的基本特征。索引对于使用大型数据集并每天运行数百万条 SQL 语句的 OLTP 应用程序至关重要。索引对于数据仓库应用程序也很重要，这些应用程序通常从非常大的表中查询相对少量的数据。如果您在应用程序工作负载发生变化时不更新索引，现有索引可能会导致数据库性能大幅下降。

自动索引通过根据应用程序工作负载的变化自动和动态地管理 Oracle 数据库中的索引来提高数据库性能。

自动索引提供以下功能：

• 以预定义的时间间隔定期在后台运行自动索引过程。
• 分析应用程序工作负载，并相应地创建新索引并删除现有性能不佳的索引以提高数据库性能。
• 重建由于表分区维护操作（例如 ALTER TABLE MOVE）而标记为不可用的索引。
• 提供 PL/SQL API 用于在数据库中配置自动索引并生成与自动索引操作相关的报告。

需要注意的是：

• 自动索引是本地 B 树索引。
• 可以为分区表和非分区表创建自动索引。
• 不能为临时表创建自动索引。

自动索引的工作原理

自动索引进程每 15 分钟在后台运行一次，并执行以下操作：

1. 识别自动索引候选

自动索引候选是根据 SQL 语句中表列的使用来识别的。

确保表统计信息是最新的。自动索引不考虑没有统计信息的表。如果实时统计信息不可用，则不考虑对具有陈旧统计信息的表进行自动索引。

2. 为自动索引候选创建不可见的自动索引

自动索引候选被创建为不可见的自动索引，即这些自动索引不能在 SQL 语句中使用。

自动索引可以是单列或多列。它们被考虑用于以下方面：

• 表列（包括虚拟列）
• 分区表和非分区表
• 选定的表达式（例如 JSON 表达式）
• 针对 SQL 语句验证不可见的自动索引

3. 不可见的自动索引根据 SQL 语句进行验证。

如果使用这些索引可以提高 SQL 语句的性能，则将这些索引配置为可见索引，以便在 SQL 语句中使用。

如果使用这些索引没有提高 SQL 语句的性能，那么这些索引仍然是不可见的。

4. 删除未使用的自动索引

长期不使用的自动索引将被删除。

注意：

默认情况下，未使用的自动索引会在 373 天后删除。 可以使用 DBMS_AUTO_INDEX.CONFIGURE 过程配置在数据库中保留未使用的自动索引的期限。

在 Oracle 数据库中配置自动索引

您可以使用 DBMS_AUTO_INDEX.CONFIGURE 过程在 Oracle 数据库中配置自动索引。

以下示例描述了可以使用 DBMS_AUTO_INDEX.CONFIGURE 过程指定的一些配置设置：

在数据库中启用和禁用自动索引

您可以使用 AUTO_INDEX_MODE 配置设置来启用或禁用数据库中的自动索引。

以下语句在数据库中启用自动索引并创建任何新的自动索引作为可见索引，以便它们可以在 SQL 语句中使用：

以下语句在数据库中启用自动索引，但会创建任何新的自动索引作为不可见索引，因此它们不能在 SQL 语句中使用：

以下语句禁用数据库中的自动索引，因此不会创建新的自动索引（现有的自动索引保持启用）：

指定可以使用自动索引的模式

您可以使用 AUTO_INDEX_SCHEMA 配置设置来指定可以使用自动索引的方案。

注意：

在数据库中启用自动索引时，默认情况下，数据库中的所有模式都可以使用自动索引。

以下语句将 SH 和 HR 模式添加到排除列表，以便 SH 和 HR 模式不能使用自动索引：

以下语句从排除列表中删除 HR 模式，以便 HR 模式可以使用自动索引：

以下语句从排除列表中删除所有模式，以便数据库中的所有模式都可以使用自动索引：

指定可以使用自动索引的表

您可以使用 AUTO_INDEX_TABLE 配置设置来指定可以使用自动索引的表。当您为方案启用自动索引时，该方案中的所有表都可以使用自动索引。但是，如果方案级别和表级别设置之间存在冲突，则表级别设置优先。

以下语句包括用于自动索引的 SH 方案中的 PRODUCTS 表：

以下语句将 SH 方案中的 SALES 和 PRODUCTS 表添加到排除列表中，以便这些表不能使用自动索引：

以下语句从排除列表中删除 SH.SALES 表，以便该表可以使用自动索引：

以下语句从排除列表中删除所有表，以便数据库中的所有表都可以使用自动索引：

以下语句检查当前配置设置：

为未使用的自动索引指定保留期

您可以使用 AUTO_INDEX_RETENTION_FOR_AUTO 配置设置来指定在数据库中保留未使用的自动索引的时间段。未使用的自动索引在指定的保留期后被删除。

注意：

默认情况下，未使用的自动索引会在 373 天后删除。

以下语句将未使用的自动索引的保留期设置为 90 天。

以下语句将自动索引的保留期重置为默认值 373 天。

为未使用的非自动索引指定保留期

您可以使用 AUTO_INDEX_RETENTION_FOR_MANUAL 配置设置来指定在数据库中保留未使用的非自动索引（手动创建的索引）的时间段。未使用的非自动索引在指定的保留期后被删除。

注意：

默认情况下，自动索引过程永远不会删除未使用的非自动索引。

以下语句将未使用的非自动索引的保留期设置为 60 天。

以下语句将未使用的非自动索引的保留期设置为 NULL，以便它们永远不会被自动索引进程删除。

指定自动索引日志的保留期

您可以使用 AUTO_INDEX_REPORT_RETENTION 配置设置来指定在数据库中保留自动索引日志的时间段。自动索引日志在指定的保留期后被删除。

注意：默认情况下，自动索引日志会在 373 天后删除。

以下语句将自动索引日志的保留期设置为 60 天。

以下语句将自动索引日志的保留期重置为默认值 373 天。

注意：自动索引报告是根据自动索引日志生成的。因此，无法在超过使用 AUTO_INDEX_REPORT_RETENTION 配置设置指定的自动索引日志的保留期的时间段内生成自动索引报告。

指定一个表空间来存储自动索引

您可以使用 AUTO_INDEX_DEFAULT_TABLESPACE 配置设置来指定一个表空间来存储自动索引。请注意，您不能将 Oracle 拥有的表空间（例如 SYSAUX）指定为默认表空间。

注意：默认情况下，创建数据库时指定的永久表空间用于存储自动索引。

以下语句指定 TBS_AUTO 的表空间来存储自动索引：

指定为自动索引分配的表空间百分比

您可以使用 AUTO_INDEX_SPACE_BUDGET 配置设置来指定为自动索引分配的表空间百分比。只有当用于存储自动索引的表空间是创建数据库时指定的默认永久表空间，即没有为 AUTO_INDEX_DEFAULT_TABLESPACE 配置设置指定值时，才可以指定此配置设置。

以下语句为自动索引分配 5% 的表空间：

为自动索引配置高级索引压缩

您可以使用 AUTO_INDEX_COMPRESSION 配置设置来指定高级索引压缩是否必须与自动索引一起使用。高级索引压缩是 Oracle Advanced Compression 选项的一部分。

以下示例在创建自动索引时启用高级索引压缩：

生成自动索引报告

您可以使用 DBMS_AUTO_INDEX 包的 REPORT_ACTIVITY 和 REPORT_LAST_ACTIVITY 函数生成与 Oracle 数据库中的自动索引操作相关的报告。

生成特定时期的自动索引操作报告

以下示例生成一个报告，其中包含有关过去 24 小时内自动索引操作的典型信息。默认情况下以纯文本格式生成报告。

以下示例生成一个报告，其中包含有关 2018 年 11 月的自动索引操作的基本信息。该报告以 HTML 格式生成，并且仅包含自动索引操作的摘要。

生成最后一次自动索引操作的报告

以下示例生成一个报告，其中包含有关上一次自动索引操作的典型信息。默认情况下以纯文本格式生成报告。

以下示例生成一个报告，其中包含有关上次自动索引操作的基本信息。该报告包括上一次自动索引操作的摘要、索引详细信息和错误信息。报告以 HTML 格式生成。

官网链接

https://docs.oracle.com/en/database/oracle/oracle-database/21/admin/managing-indexes.html#GUID-D1285CD5-95C0-4E74-8F26-A02018EA7999