这篇文章主要介绍“Oracle Database 19c中自动索引的功能介绍”，在日常操作中，相信很多人在Oracle Database 19c中自动索引的功能介绍问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”Oracle Database 19c中自动索引的功能介绍”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

1、它能做什么

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

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

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

根据SQL语句测试不可见的自动索引，以确保它们能提高性能。如果它们导致性能提高，则可以它们可见。如果性能未得到改善，则相关的自动索引将标记为不可用，稍后将被删除。针对失败的自动索引测试的SQL语句被列入黑名单，因此将来不会考虑将它们用于自动索引。第一次对数据库运行SQL时，优化程序不会考虑自动索引。

删除未使用的索引。

2、先决条件

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

exportORACLE_SID=cdb1exportORAENV_ASK=NO.oraenvexportORAENV_ASK=YESsqlplus/assysdba<<EOFaltersystemset"_exadata_feature_on"=truescope=spfile;shutdownimmediate;startup;exit;EOF

3、配置

使用DBMS_AUTO_INDEX包来管理自动索引特性。下面描述了基本管理。

3.1显示配置

CDB_AUTO_INDEX_CONFIG视图显示当前的自动索引配置。COLUMNparameter_nameFORMATA40COLUMNparameter_valueFORMATA15SELECTcon_id,parameter_name,parameter_valueFROMcdb_auto_index_configORDERBY1,2;CON_IDPARAMETER_NAMEPARAMETER_VALUE-----------------------------------------------------------------1AUTO_INDEX_COMPRESSIONOFF1AUTO_INDEX_DEFAULT_TABLESPACE1AUTO_INDEX_MODEOFF1AUTO_INDEX_REPORT_RETENTION311AUTO_INDEX_RETENTION_FOR_AUTO3731AUTO_INDEX_RETENTION_FOR_MANUAL1AUTO_INDEX_SCHEMA1AUTO_INDEX_SPACE_BUDGET503AUTO_INDEX_COMPRESSIONOFF3AUTO_INDEX_DEFAULT_TABLESPACE3AUTO_INDEX_MODEOFF3AUTO_INDEX_REPORT_RETENTION313AUTO_INDEX_RETENTION_FOR_AUTO3733AUTO_INDEX_RETENTION_FOR_MANUAL3AUTO_INDEX_SCHEMA3AUTO_INDEX_SPACE_BUDGET50SQL>

如果我们切换到用户定义的可插拔数据库，我们只获取该容器的值。

ALTERSESSIONSETCONTAINER=pdb1;COLUMNparameter_nameFORMATA40COLUMNparameter_valueFORMATA15SELECTcon_id,parameter_name,parameter_valueFROMcdb_auto_index_configORDERBY1,2;CON_IDPARAMETER_NAMEPARAMETER_VALUE-----------------------------------------------------------------3AUTO_INDEX_COMPRESSIONOFF3AUTO_INDEX_DEFAULT_TABLESPACE3AUTO_INDEX_MODEOFF3AUTO_INDEX_REPORT_RETENTION313AUTO_INDEX_RETENTION_FOR_AUTO3733AUTO_INDEX_RETENTION_FOR_MANUAL3AUTO_INDEX_SCHEMA3AUTO_INDEX_SPACE_BUDGET50SQL>

3.2启用/禁用自动索引

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

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

IMPLEMENT：打开自动索引。提高性能的新索引可见并可供优化程序使用。

REPORT ONLY：打开自动索引，但新索引仍然不可见。

OFF：关闭自动索引。

模式之间切换的命令示例如下：

3.3自动索引的表空间

默认情况下，自动索引是在默认的永久表空间中创建的。如果这是不可接受的，您可以使用AUTO_INDEX_DEFAULT_TABLESPACE属性指定一个表空间来保存它们。下面我们创建一个表空间来保存自动索引，并相应地设置属性。

ALTERSESSIONSETCONTAINER=pdb1;CREATETABLESPACEAUTO_INDEXES_TSDATAFILESIZE100MAUTOEXTENDONNEXT100M;EXECDBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','AUTO_INDEXES_TS');

如果要设置使用默认永久表空间，可以设置为NULL，如下命令所示：

3.4模式级（Schema-Level）控制

一旦启用了自动索引，在尝试识别候选索引时会考虑所有模式。您可以使用AUTO_INDEX_SCHEMA属性更改默认行为，该属性允许您维护包含/排除列表。

如果ALLOW参数设置为true，则指定的模式(schema)将添加到包含列表中。注意:它构建了一个包含模式的谓词。

EXECDBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA','TEST',allow=>TRUE);EXECDBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA','TEST2',allow=>TRUE);COLUMNparameter_nameFORMATA40COLUMNparameter_valueFORMATA15SELECTcon_id,parameter_name,parameter_valueFROMcdb_auto_index_configORDERBY1,2;CON_IDPARAMETER_NAMEPARAMETER_VALUE------------------------------------------------------------------------------------------3AUTO_INDEX_COMPRESSIONOFF3AUTO_INDEX_DEFAULT_TABLESPACEAUTO_INDEXES_TS3AUTO_INDEX_MODEIMPLEMENT3AUTO_INDEX_REPORT_RETENTION313AUTO_INDEX_RETENTION_FOR_AUTO3733AUTO_INDEX_RETENTION_FOR_MANUAL3AUTO_INDEX_SCHEMAschemaIN(TEST,TEST2)3AUTO_INDEX_SPACE_BUDGET50SQL>

可以使用NULL参数值消除包含列表，如下所示：

EXECDBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA',NULL,allow=>TRUE);COLUMNparameter_nameFORMATA40COLUMNparameter_valueFORMATA15SELECTcon_id,parameter_name,parameter_valueFROMcdb_auto_index_configORDERBY1,2;CON_IDPARAMETER_NAMEPARAMETER_VALUE------------------------------------------------------------------------------------------3AUTO_INDEX_COMPRESSIONOFF3AUTO_INDEX_DEFAULT_TABLESPACEAUTO_INDEXES_TS3AUTO_INDEX_MODEIMPLEMENT3AUTO_INDEX_REPORT_RETENTION313AUTO_INDEX_RETENTION_FOR_AUTO3733AUTO_INDEX_RETENTION_FOR_MANUAL3AUTO_INDEX_SCHEMA3AUTO_INDEX_SPACE_BUDGET50SQL>

如果ALLOW参数设置为FALSE，则指定的模式将添加到排除列表中。

EXECDBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA','TEST',allow=>FALSE);EXECDBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA','TEST2',allow=>FALSE);COLUMNparameter_nameFORMATA40COLUMNparameter_valueFORMATA15SELECTcon_id,parameter_name,parameter_valueFROMcdb_auto_index_configORDERBY1,2;CON_IDPARAMETER_NAMEPARAMETER_VALUE------------------------------------------------------------------------------------------3AUTO_INDEX_COMPRESSIONOFF3AUTO_INDEX_DEFAULT_TABLESPACEAUTO_INDEXES_TS3AUTO_INDEX_MODEIMPLEMENT3AUTO_INDEX_REPORT_RETENTION313AUTO_INDEX_RETENTION_FOR_AUTO3733AUTO_INDEX_RETENTION_FOR_MANUAL3AUTO_INDEX_SCHEMAschemaNOTIN(TEST,TEST2)3AUTO_INDEX_SPACE_BUDGET50SQL>

可以使用NULL参数值清除排除列表。

EXECDBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA',NULL,allow=>FALSE);COLUMNparameter_nameFORMATA40COLUMNparameter_valueFORMATA15SELECTcon_id,parameter_name,parameter_valueFROMcdb_auto_index_configORDERBY1,2;CON_IDPARAMETER_NAMEPARAMETER_VALUE------------------------------------------------------------------------------------------3AUTO_INDEX_COMPRESSIONOFF3AUTO_INDEX_DEFAULT_TABLESPACEAUTO_INDEXES_TS3AUTO_INDEX_MODEIMPLEMENT3AUTO_INDEX_REPORT_RETENTION313AUTO_INDEX_RETENTION_FOR_AUTO3733AUTO_INDEX_RETENTION_FOR_MANUAL3AUTO_INDEX_SCHEMA3AUTO_INDEX_SPACE_BUDGET50SQL>

4、其它配置

您可能希望考虑其他参数，这些都在此详细说明。

AUTO_INDEX_COMPRESSION：据推测用于控制压缩程度。默认为“OFF”。

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、删除二级索引

在做这个之前，请仔细考虑，测试，测试，测试！

如果您感觉特别勇敢，DROP_SECONDARY_INDEXES过程将删除除用于约束的索引之外的所有索引。这可以在表、模式（Schema）、数据库级别完成。--表级别EXECDBMS_AUTO_INDEX.drop_secondary_indexes('MY_SCHEMA','MY_TABLE');--模式（Schema）级别EXECDBMS_AUTO_INDEX.drop_secondary_indexes('MY_SCHEMA');--数据库级别EXECDBMS_AUTO_INDEX.drop_secondary_indexes;

6、视图

有几个与自动索引功能相关的视图，如下所示：

SELECTview_nameFROMdba_viewsWHEREview_nameLIKE'DBA_AUTO_INDEX%'ORDERBY1;VIEW_NAME--------------------------------------------------------------------------------DBA_AUTO_INDEX_CONFIGDBA_AUTO_INDEX_EXECUTIONSDBA_AUTO_INDEX_IND_ACTIONSDBA_AUTO_INDEX_SQL_ACTIONSDBA_AUTO_INDEX_STATISTICSDBA_AUTO_INDEX_VERIFICATIONSSQL>

此外，{CDB|DBA|ALL|USER}_INDEXES 视图包含AUTO列，该列指示索引是否由自动索引功能创建。

COLUMNownerFORMATA30COLUMNindex_nameFORMATA30COLUMNtable_ownerFORMATA30COLUMNtable_nameFORMATA30SELECTowner,index_name,index_type,table_owner,table_nametable_typeFROMdba_indexesWHEREauto='YES'ORDERBYowner,index_name;

7、活动报告

DBMS_AUTO_INDEX包中包含两个报告功能。

DBMS_AUTO_INDEX.REPORT_ACTIVITY(activity_startINTIMESTAMPWITHTIMEZONEDEFAULTSYSTIMESTAMP-1,activity_endINTIMESTAMPWITHTIMEZONEDEFAULTSYSTIMESTAMP,typeINVARCHAR2DEFAULT'TEXT',sectionINVARCHAR2DEFAULT'ALL',levelINVARCHAR2DEFAULT'TYPICAL')RETURNCLOB;DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY(typeINVARCHAR2DEFAULT'TEXT',sectionINVARCHAR2DEFAULT'ALL',levelINVARCHAR2DEFAULT'TYPICAL')RETURNCLOB;

REPORT_ACTIVITY函数允许您显示指定时间段内的活动，默认为最后一天。REPORT_LAST_ACTIVITY函数报告上次自动索引操作。两者都允许您使用以下参数定制输出。

TYPE：允许值（TEXT，HTML，XML）。

SECTION：允许值（SUMMARY，INDEX_DETAILS，VERIFICATION_DETAILS，ERRORS，ALL）。您还可以使用带有“+”和“-”字符的组合来指示是否应包含或排除某些内容。例如'SUMMARY + ERRORS'或'ALL -ERRORS'。

LEVEL：允许值（BASIC，TYPICAL，ALL）。

从SQL中使用这些函数的一些示例如下所示。注意引用LEVEL参数。在SQL调用中使用它时，这是必要的，因此这不是对LEVEL伪列的引用。

SETLONG1000000PAGESIZE0--过去24小时的默认TEXT报告。SELECTDBMS_AUTO_INDEX.report_activity()FROMdual;--最新活动的默认TEXT报告。SELECTDBMS_AUTO_INDEX.report_last_activity()FROMdual;--前天的HTML报告。SELECTDBMS_AUTO_INDEX.report_activity(activity_start=>SYSTIMESTAMP-2,activity_end=>SYSTIMESTAMP-1,type=>'HTML')FROMdual;--最新活动的HTML报告。SELECTDBMS_AUTO_INDEX.report_last_activity(type=>'HTML')FROMdual;--前天的XML报告包含所有信息。SELECTDBMS_AUTO_INDEX.report_activity(activity_start=>SYSTIMESTAMP-2,activity_end=>SYSTIMESTAMP-1,type=>'XML',section=>'ALL',"LEVEL"=>'ALL')FROMdual;--包含所有信息的最新活动的XML报告。SELECTDBMS_AUTO_INDEX.report_last_activity(type=>'HTML',section=>'ALL',"LEVEL"=>'ALL')FROMdual;SETPAGESIZE14

以下是在创建任何索引之前默认活动报告的输出示例。

SELECTDBMS_AUTO_INDEX.report_activity()FROMdual;GENERALINFORMATION-------------------------------------------------------------------------------Activitystart:03-JUN-201921:59:21Activityend:04-JUN-201921:59:21Executionscompleted:2Executionsinterrupted:Executionswithfatalerror:-------------------------------------------------------------------------------SUMMARY(AUTOINDEXES)-------------------------------------------------------------------------------Indexcandidates:Indexescreated:Spaceused:0BIndexesdropped:SQLstatementsverified:SQLstatementsimproved:SQLplanbaselinescreated:Overallimprovementfactor:0x-------------------------------------------------------------------------------SUMMARY(MANUALINDEXES)-------------------------------------------------------------------------------Unusedindexes:Spaceused:0BUnusableindexes:-------------------------------------------------------------------------------ERRORS---------------------------------------------------------------------------------------------Noerrorsfound.---------------------------------------------------------------------------------------------SQL>

到此，关于“Oracle Database 19c中自动索引的功能介绍”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！