合 Oracle 19c中的自动索引（DBMS_AUTO_INDEX）
👉 本文共约2197个字，系统预计阅读时间或需9分钟。
它能做什么
自动索引功能执行以下操作。
- 根据表列使用情况确定潜在的自动索引。文档称这些为“候选索引(candidate indexes)”。
- 将自动索引创建为不可见索引，因此不会在执行计划中使用它们。索引名称包括“SYS_AI”前缀。
- 根据SQL语句测试不可见的自动索引，以确保它们能提高性能。如果它们导致性能提高，则可以它们可见。如果性能未得到改善，则相关的自动索引将标记为不可用，稍后将被删除。针对失败的自动索引测试的SQL语句被列入黑名单，因此将来不会考虑将它们用于自动索引。第一次对数据库运行SQL时，优化程序不会考虑自动索引。
- 删除未使用的索引。
先决条件
Oracle 19c，此功能仅限于企业版。通过设置初始化参数“_exadata_feature_on=true”进行测试。
1 2 3 4 5 6 7 8 9 10 | export ORACLE_SID=cdb1 export ORAENV_ASK=NO . oraenv export ORAENV_ASK=YES sqlplus / as sysdba <<EOF alter system set "_exadata_feature_on"=true scope=spfile; shutdown immediate; startup; exit; EOF |
这不受支持，不应在实际系统上使用。
配置
使用 DBMS_AUTO_INDEX 包来管理自动索引特性。下面描述了基本管理。
显示配置
CDB_AUTO_INDEX_CONFIG视图显示当前的自动索引配置。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | COLUMN parameter_name FORMAT A40 COLUMN parameter_value FORMAT A15 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 SQL> |
如果我们切换到用户定义的可插拔数据库，我们只获取该容器的值。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | ALTER SESSION SET CONTAINER = pdb1; COLUMN parameter_name FORMAT A40 COLUMN parameter_value FORMAT A15 SELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_config ORDER BY 1, 2; CON_ID PARAMETER_NAME PARAMETER_VALUE ---------- ---------------------------------------- --------------- 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 SQL> |
启用/禁用自动索引
使用 DBMS_AUTO_INDEX 包的 CONFIGURE 存储过程配置自动索引。
使用 AUTO_INDEX_MODE 属性控制用于自动索引的开关，该属性具有以下允许值：
- IMPLEMENT：打开自动索引。提高性能的新索引可见并可供优化程序使用。
- REPORT ONLY：打开自动索引，但新索引仍然不可见。
- OFF：关闭自动索引。
模式之间切换的命令示例如下：
1 2 3 | 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'); |
自动索引的表空间
默认情况下，自动索引是在默认的永久表空间中创建的。如果这是不可接受的，您可以使用 AUTO_INDEX_DEFAULT_TABLESPACE 属性指定一个表空间来保存它们。下面我们创建一个表空间来保存自动索引，并相应地设置属性。
1 2 3 | ALTER SESSION SET CONTAINER = pdb1; CREATE TABLESPACE AUTO_INDEXES_TS DATAFILE SIZE 100M AUTOEXTEND ON NEXT 100M; EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','AUTO_INDEXES_TS'); |
如果要设置使用默认永久表空间，可以设置为 NULL，如下命令所示：
1 | EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE',NULL); |
模式级（Schema-Level）控制
一旦启用了自动索引，在尝试识别候选索引时会考虑所有模式。您可以使用 AUTO_INDEX_SCHEMA 属性更改默认行为，该属性允许您维护 包含/排除 列表。
如果 ALLOW 参数设置为true，则指定的模式(schema)将添加到包含列表中。注意:它构建了一个包含模式的谓词。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'TEST', allow => TRUE); EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'TEST2', allow => TRUE); COLUMN parameter_name FORMAT A40 COLUMN parameter_value FORMAT A15 SELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_config ORDER BY 1, 2; CON_ID PARAMETER_NAME PARAMETER_VALUE ---------- ---------------------------------------- ---------------------------------------- 3 AUTO_INDEX_COMPRESSION OFF 3 AUTO_INDEX_DEFAULT_TABLESPACE AUTO_INDEXES_TS 3 AUTO_INDEX_MODE IMPLEMENT 3 AUTO_INDEX_REPORT_RETENTION 31 3 AUTO_INDEX_RETENTION_FOR_AUTO 373 3 AUTO_INDEX_RETENTION_FOR_MANUAL 3 AUTO_INDEX_SCHEMA schema IN (TEST, TEST2) 3 AUTO_INDEX_SPACE_BUDGET 50 SQL> |
可以使用 NULL 参数值消除包含列表，如下所示：
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
