Oracle 19c中的自动索引（DBMS_AUTO_INDEX）

刘泽宇

自动索引功能执行以下操作。

根据表列使用情况确定潜在的自动索引。文档称这些为“候选索引(candidate indexes)”。
将自动索引创建为不可见索引，因此不会在执行计划中使用它们。索引名称包括“SYS_AI”前缀。
根据SQL语句测试不可见的自动索引，以确保它们能提高性能。如果它们导致性能提高，则可以它们可见。如果性能未得到改善，则相关的自动索引将标记为不可用，稍后将被删除。针对失败的自动索引测试的SQL语句被列入黑名单，因此将来不会考虑将它们用于自动索引。第一次对数据库运行SQL时，优化程序不会考虑自动索引。
删除未使用的索引。
先决条件

Oracle 19c，此功能仅限于企业版。通过设置初始化参数“_exadata_feature_on=true”进行测试。

显示配置
CDB_AUTO_INDEX_CONFIG视图显示当前的自动索引配置。



SELECT con_id, parameter_name, parameter_value
FROM   cdb_auto_index_config
ORDER BY 1, 2;

    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
---------- ---------------------------------------- ---------------
         1 AUTO_INDEX_COMPRESSION                   OFF
         1 AUTO_INDEX_DEFAULT_TABLESPACE
         1 AUTO_INDEX_MODE                          OFF
         1 AUTO_INDEX_REPORT_RETENTION              31
         1 AUTO_INDEX_RETENTION_FOR_AUTO            373
         1 AUTO_INDEX_RETENTION_FOR_MANUAL
         1 AUTO_INDEX_SCHEMA
         1 AUTO_INDEX_SPACE_BUDGET                  50
         3 AUTO_INDEX_COMPRESSION                   OFF
         3 AUTO_INDEX_DEFAULT_TABLESPACE
         3 AUTO_INDEX_MODE                          OFF
         3 AUTO_INDEX_REPORT_RETENTION              31
         3 AUTO_INDEX_RETENTION_FOR_AUTO            373
         3 AUTO_INDEX_RETENTION_FOR_MANUAL
         3 AUTO_INDEX_SCHEMA
         3 AUTO_INDEX_SPACE_BUDGET                  50


启用/禁用自动索引
使用 DBMS_AUTO_INDEX 包的 CONFIGURE 存储过程配置自动索引。

使用 AUTO_INDEX_MODE 属性控制用于自动索引的开关，该属性具有以下允许值：

IMPLEMENT：打开自动索引。提高性能的新索引可见并可供优化程序使用。
REPORT ONLY：打开自动索引，但新索引仍然不可见。
OFF：关闭自动索引。

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

产生的自动索引命名为：SYS_AI_xxxx