Oracle Database 19c中自动索引的功能介绍

1.它能做什么

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

根据表列的使用情况确定潜在的自动索引。该文档将这些称为“候选索引”。

将自动索引创建为不可见索引，这样它们就不会在执行计划中使用。索引名称包含“SYS_AI”前缀。

针对 SQL 语句测试不可见的自动索引，以确保它们提高性能。他们如果它们能带来性能改进，那么它们就会是可见的。如果性能没有得到改善，关联的自动索引将被标记为不可用，稍后将被删除。针对失败的自动索引进行测试的 SQL 语句将被列入黑名单，因此将来不会考虑将它们用于自动索引。第一次对数据库运行 SQL 时，优化器不会考虑自动索引。

删除未使用的索引。

2.先决条件

通过设置初始化参数“_exadata_feature_on=true”进行测试。注意：请勿在生产系统上进行测试。

导出 ORACLE_SID=cdb1export ORAENV_ASK=NO。 oraenvexport ORAENV_ASK=YESsqlplus / as sysdba <
3.配置< /strong>
使用DBMS_AUTO_INDEX包来管理自动索引功能。该底座被描述为低这个管理。 
3.1 显示配置
CDB_AUTO_INDEX_CONFIG视图显示当前的自动索引配置。列参数名称格式 A40列参数值格式 A15SELECT con_id、参数名称、参数值 FROM cdb_auto_index_configORDER BY 1, 2; CON_ID PARAMETER_NAME PARAMETER_ VALUE---------- ------------------ ------------------ --- --------------- 1 AUTO_INDEX_COMPRESSIONOFF1 AUTO_INDEX_REPORT_RETENTION 311 AUTO_INDEX_RETENTION_FOR_AUTO 3731 AUTO_INDEX_RETENTION_FOR_MANUAL1 AUTO_INDEX_SCHEMA1 AUTO_INDEX_SPACE_BUDGET 503 AUTO_INDEX_COMPRESSION 313 AUTO_INDEX_RETENTION_F OR_AUTO     3733 AUTO_INDEX_RETENTION_FOR_MANUAL3 AUTO_INDEX_SCHEMA     3 AUTO_INDEX_SPACE_BUDGET       50SQL>
如果我们切换到用户定义的可插拔数据库，我们只获取该容器的值。 
ALTER SESSION SET CONTAINER = pdb1;COLUMN 参数名称 FORMAT A40COLUMN 参数值 FORMAT A15SELECT con_id, 参数ter_name,parameter_value FROM cdb_auto_index_configORDER BY 1, 2; CON_ID PARAMETER_NAME                                                                                                                                                                            ------------------------------------------------ ---- -   3 AUTO_INDEX_COMPRESSION 3 AUTO_INDEX_DEFAULT_TABLESPACE 3 AUTO_INDEX_MODE 关闭 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_BUD GET 50SQL>
3.2 启用< strong>/禁用自动索引
使用DBMS_AUTO_INDEX包的CONFIGURE存储过程配置自动索引。 
使用AUTO_INDEX_MODE属性控制自动索引的开关，该属性有以下允许值：
IMPLMENT：打开关于自动索引索引。提高性能的新索引对优化器来说是可见且可用的。 
REPORT ONLY：自动索引已打开，但新索引仍然不可见。 
关闭：关闭自动索引。 
模式切换命令示例如下：
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLMENT');EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE' ,'REPORT ONLY');EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');
3.3 自动索引表空间< /p>
默认情况下，自动索引是在默认永久表空间中创建的。如果这是不可接受的，您可以使用 AUTO_INDEX_DEFAULT_TABLESPACE 属性来指定一个表空间来保存它们。接下来我们创建一个表空间来保存自动索引并相应地设置属性。 
更改会话设置容器 = 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');
 pre>
如果要设置使用默认永久表空间可以设置为NULL ，如以下命令所示：
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE',NULL);
< p>3.4 < /strong>架构级（架构级）控制
一旦启用自动索引，在尝试识别候选索引时会考虑所有模式。您可以使用 AUTO_INDEX_SCHEMA 属性更改默认行为，该属性允许您维护包含/排除列表。 
如果ALLOW参数设置为true，则指定的架构将添加到包含列表中。注意：它构建一个包含模式的谓词。 
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', '测试',allow => TRUE);EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA','TEST2',allow => TRUE);COLUMN 参数名称 FORMAT A40COLUMN 参数值 FORMAT A15SELECT con_id、parameter_name、parameter_value FROM cdb_auto_index_configORDER BY 1, 2; CON_ID PARAMETER_NAME PARAMETER_VALUE------------------------------------------------------------ ----------- ------------------------------------------ ------------ 3 AUTO_INDEX_COMPRESSIONTo_index_default_tableSpace Auto_INDEXES_TS 3 Auto_index_mode 实施 3 Auto_index_report_retention 31 3 AUTO_INDEX_RETENTIN 373 3 Auto_index _Retention_FOR_MANUAL 3 Auto_INDEX_SCHEMA schema IN (TEST, TEST2) 3 AUTO_INDEX_SPACE_BUDGET 50SQL>
可以使用NULL参数值来消除包含列表，如下：
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', NULL ，允许 => TRUE);列参数名称格式 A40列参数值格式 A15SELECT con_id、参数名称、参数值 FROM cdb_auto_index_configORDER BY 1, 2; CON_ID参数METER_NAME PARAMETER_VALUE---------- ----------- ------------------------------ ---------------------------- -------------------   3 AUTO_INDEX_COMPRESSION 关闭IMPLMENT 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 50SQL>
如果 ALLOW 参数设置为 FALSE，则指定模式将添加到排除列表中。 
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'TEST',allow => FALSE);EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA','TEST2',allow => FALSE);COLUMN 参数名称 FORMAT A40COLUMN 参数_value FORMAT A15SELECT con_id,parameter_name,parameter_value FROM cdb_auto_index_configORDER BY 1, 2; CON_ID PARAMETER_NAME   PARAMETER_VALUE------------------------------------------------------------ --- ---------------------------------------------------------- ---- 3 AUTO_INDEX_COMPRESSION                             3 AUTO_INDEX_DEFAULT_TABLESPACEAUTO_INDEX_TS     3 AUTO_INDEX_MODE IMPLMENT 3 AUTO_INDEX_REPORT_RETENTION 31   3 AUTO_INDEX_RETENTION_FOR_AUTO 373 3 AUTO_INDEX_RETENTION_FOR_MANUAL 3 AUTO_INDEX_SCHEMA 架构不在（TEST、TEST 2）中 3 AUTO_INDEX_SPACE_BUDGET 50SQL>
您可以使用NULL参数值来清除排除列表。 
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', NULL, allowed => FALSE);COLUMN 参数名称 FORMAT A40COLUMN 参数值 FORMAT A15SELECT con_id, 参数名称, 参数值FROM cdb_auto_index_configORDER BY 1, 2; CON_ID PARAMETER_NAME PARAMETER_VALUE-------------------------------------------------------- ----- ------------------------------------------ 3 Auto_index_压缩关闭 3 Auto_index_default_tableSpace Auto_InDexes_TS 3 Auto_index_mode实施 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 50SQL>
4. 其他r 配置
 
您可能希望考虑其他参数，此处详细介绍了这些参数。 
AUTO_INDEX_COMPRESSION：大概用于控制压缩程度。默认为“关闭”。 
AUTO_INDEX_REPORT_RETENTION：自动索引日志的保留期限。默认值为 31 天。 
AUTO_INDEX_RETENTION_FOR_AUTO：未使用的自动索引的保留期限。默认值为 373 天。 
AUTO_INDEX_RETENTION_FOR_MANUAL：未使用的手动创建的索引的保留期限。当设置为 NULL 时，不考虑手动创建的索引。默认值为 NULL。 
AUTO_INDEX_SPACE_BUDGET：用于自动索引存储的默认永久表空间的百分比。使用 AUTO_INDEX_DEFAULT_TABLESPACE 参数指定自定义表空间时，将忽略此参数。 
5. 删除二级索引
执行此操作之前s，请仔细思考，测试，测试，测试！ 
如果您感觉特别勇敢，DROP_SECONDARY_INDEXES 过程将删除除用于约束的索引之外的所有索引。这可以在表、模式或数据库级别完成。 -- 表级 EXEC DBMS_AUTO_INDEX.drop_secondary_indexes('MY_SCHEMA', 'MY_TABLE'); -- 模式级别 EXEC DBMS_AUTO_INDEX.drop_secondary_indexes('MY_SCHEMA'); -- 数据库级 EXEC DBMS_AUTO_INDEX.drop_secondary_indexes;
6. 视图
与自动索引功能相关的视图有几个，如下：
SELECT view_nameFROM dba_viewsWHERE view_name LIKE 'DBA_AUTO_INDEX%'ORDER BY 1；VIEW_NAME-------------------------------------------------------- - -------------------------------------------DBA_AUTO_INDEX_CONFIGDBA_AUTO_INDEX_EXECUTIONSDBA_AUTO_INDEX_IND_ACTIONSDBA_AUTO_INDEX_SQL_ACTIONSDBA_AUTO_INDEX_STATISTICSSDBA_AUTO_INDEX_VERIFICATIONSSQL>
< p>此外，{CDB|DBA|ALL|USER}_INDEXES 视图包含 AUTO 列，该列指示索引是否是由自动索引功能创建的。 。 
列所有者 FORMAT A30COLUMN 索引名称 FORMAT A30COLUMN 表所有者 FORMAT A30COLUMN 表名称 FORMAT A30SELECT 所有者、index_name、index_type、table_owner、table_name table _typeFROM dba_indexesWHERE auto = 'YES'ORDER BY 所有者、index_name;
 7.活动报告
DBMS_AUTO_INDEX包包含两个报告函数。 
DBMS_AUTO_INDEX.REPORT_ACTIVITY (activity_start IN TIMESTAMP with TIME ZONE DEFAULT SYSTIMESTAMP - 1, Activity_end IN TIMESTAMP with TIME ZONE DEFAULT SYSTIMESTAMP, type IN VARCHAR2 DEFAULT 'TEXT', section IN VARCHAR2 DEFAULT 'ALL', level IN VARCHAR2 DEFAULT 'TYPICAL')RETURN CLOB;DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY (键入 varchar2 默认 'text',section 为 varchar2 默认 'ALL',level IN VARCHAR2 DEFAULT 'TYPICAL')RETURN CLOB;
 REPORT_ACTIVITY 函数分配您可以显示指定时间段内的活动，默认为最后一天。 REPORT_LAST_ACTIVITY 函数报告最后一次自动索引操作。两者都允许您使用以下参数自定义输出。 
TYPE：允许的值（TEXT、HTML、XML）。 
SECTION：允许的值（SUMMARY、INDEX_DETAILS、VERIFICATION_DETAILS、ERRORS、ALL）。您还可以使用“+”和“-”字符的组合来指示是否应包含或排除某些内容。例如“摘要 + 错误”或“所有错误”。 
LEVEL：允许的值（BASIC、TYPICAL、ALL）。 
下面显示了从 SQL 使用这些函数的一些示例。请注意对 LEVEL 参数的引用。在 SQL 调用中使用它时这是必需的，因此这不是对 LEVEL 伪列的引用。 
SET LONG 1000000 PAGESIZE 0 --过去24小时的默认文本报告。选择 DBMS_AUTO_INDEX.report_activity() FROMDual;-- 最新活动的默认文本报告。 SELECT DBMS_AUTO_INDEX.report_last_activity() FROM Dual;--前天的HTML报告。 SELECT DBMS_AUTO_INDEX.report_activity(activity_start => SYSTIMESTAMP-2, Activity_end => SYSTIMESTAMP-1,type => 'HTML')FROM Dual;-- 最新活动的 HTML 报告。 SELECT DBMS_AUTO_INDEX.report_last_activity(type => 'HTML')FROM Dual;--前天的XML报告包含了所有信息。 SELECT DBMS_AUTO_INDEX.report_activity( Activity_start => SYSTIMESTAMP-2, Activity_end => SYSTIMESTAMP-1, type => 'XML',section => 'ALL',"LEVEL" => ' ALL')FROM Dual;-- XML ​​包含所有信息的最新活动报告。 SELECT DBMS_AUTO_INDEX.report_last_activity(type => 'HTML', section => 'ALL', "LEVEL" => 'ALL')FROM Dual;SET PAGESIZE 14
以下是任何索引之前的默认活动创建了报告的示例输出。 
从双中选择 DBMS_AUTO_INDEX.report_activity();一般信息-------------------------------------------- ------------------- ---------------------------- 活动开始 : 2023 年 6 月 3 日 21:59:21 活动结束 : 2023 年 6 月 4 日 21 :59:21 执行完成 : 2 执行中断 : 执行出现致命错误 : ---------------------------------- -------- ----------------------------------摘要（自动索引）--- ---- ---------------------------------------------------------- ---- ---------------------- 候选索引 : 创建的索引 : 使用的空间 : 0 B 删除的索引 : SQL 语句 验证 : SQL 语句 改进 : SQL创建的计划基线：总体改进系数：0x-------------------------------------------------------- --------------------------------------------------------摘要（手册索引）- ------- ------------------------------------------- ------- --------------------- 未使用的索引：已用空间：0 B 不可用索引：---------- -------- ------------------------------------------ -------- ----------错误-------------------------------------------- ------- -------------------------------------------------- -----未发现错误。--------- ---------------------------------------------------- ---------- ---------------------------------------------------- --SQL>
《Oracle Database 19c 中自动索引功能简介》的学习到此结束。希望能够解答大家的疑惑。理论与实践相结合，能够更好的帮助大家学习，去尝试吧！如果您想继续了解更多相关知识，请继续关注网站。小编会继续努力，给大家带来更多实用的文章！