AI 助理
备案控制台
开发者社区 数据库 文章 正文

Oracle Database 19c 中的自动索引 (DBMS_AUTO_INDEX)

2021-12-10 237
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： Oracle 数据库 19c 引入了自动索引功能，它可以让您将一些有关索引管理的决策交给数据库。

Oracle 数据库 19c 引入了自动索引功能，它可以让您将一些有关索引管理的决策交给数据库。


目录



一、介绍


自动索引功能包括以下几个特性：


  • 可以根据数据表中列使用情况识别潜在的自动索引。 我们可以称之为 “候选索引”。


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


  • 针对 SQL 语句测试不可见的自动索引以确保它们能够提升性能。 如果它们确实可以提高性能，就会变成可见索引。反之，如果性能没有得到改善，相关的自动索引会被标记为不可用并随后被删除。 针对失败的自动索引测试的 SQL 语句被列入阻止列表，因此将来不会考虑将它们用于自动索引。 优化器不会在第一次对数据库运行 SQL 时考虑自动索引。


  • 删除未使用的索引。


由于从未在 Exadata 上使用过此功能，因此无法评论其有效性。


二、先决条件


此功能目前仅限于工程系统上的企业版，如此处所述。 有一种通过启用 _exadata_feature_on 初始化参数进行测试的解决方案。


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


此方式不建议在生产系统进行使用，仅用于测试所用。


三、配置方式


DBMS_AUTO_INDEX 包用于管理自动索引功能， 基本管理如下所述。


1、检查配置


CDB_AUTO_INDEX_CONFIG 视图显示当前的自动索引配置，以下查询使用 auto_index_config.sql 脚本。


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;
SQL>@auto_index_config.sqlCON_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>


关于该参数的详细描述可参考官方文档：DBMS_AUTO_INDEX！


2、启用/禁用自动索引


我们可以使用 DBMS_AUTO_INDEX 包的 CONFIGURE 过程配置来自动索引。


自动索引 的开关是使用 AUTO_INDEX_MODE 属性控制的，该属性具有以下几种模式：


  • IMPLEMENT：打开自动索引，提高性能的新索引变得可见并可供优化器使用。


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


  • OFF: 关闭自动索引。


可以使用以下命令进行模式间的切换：


execdbms_auto_index.configure('AUTO_INDEX_MODE','IMPLEMENT');
execdbms_auto_index.configure('AUTO_INDEX_MODE','REPORT ONLY');
execdbms_auto_index.configure('AUTO_INDEX_MODE','OFF');


3、自动索引的表空间


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


altersessionsetcontainer=pdb1;
createtablespaceauto_indexes_tsdatafilesize100mautoextendonnext100m;
execdbms_auto_index.configure('AUTO_INDEX_DEFAULT_TABLESPACE','AUTO_INDEXES_TS');


设置为 NULL 则代表使用默认的永久表空间：


Execdbms_auto_index.configure('AUTO_INDEX_DEFAULT_TABLESPACE',NULL);


4、用户级控制


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


如果 ALLOW 参数设置为 TRUE，则将指定的用户添加到包含列表中。 请注意，它构建了一个包含用户的谓词。


execdbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TEST', allow=>TRUE);
execdbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TEST2', allow=>TRUE);
SQL>@auto_index_config.sqlCON_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);
SQL>@auto_index_config.sqlCON_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);
SQL>@auto_index_config.sqlCON_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);
SQL>@auto_index_config.sqlCON_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>


5、其他配置


如果需要了解其他参数，下面详细说明了这些参数：


  • AUTO_INDEX_COMPRESSION ：文档中未说明，大概用于控制压缩级别， 默认 OFF
  • AUTO_INDEX_REPORT_RETENTION ：自动索引日志的保留期。 报告基于这些日志，默认 31 天。
  • AUTO_INDEX_RETENTION_FOR_AUTO ：未使用的自动索引的保留期，默认 373 天。
  • AUTO_INDEX_RETENTION_FOR_MANUAL ：未使用的手动创建索引的保留期，当设置为 NULL 时，不考虑删除手动创建的索引，默认为空。
  • AUTO_INDEX_SPACE_BUDGET ：用于自动索引存储的默认永久表空间的百分比，使用 AUTO_INDEX_DEFAULT_TABLESPACE 参数指定自定义表空间时，将忽略此参数。


四、删除二级索引


‼️ 做这个之前要仔细考虑，测试，测试，测试！


如果你真的勇气非凡，DROP_SECONDARY_INDEXES 过程将删除除用于约束的索引之外的所有索引。 这可以在表、模式或数据库级别完成。


--Table-levelexecdbms_auto_index.drop_secondary_indexes('MY_SCHEMA', 'MY_TABLE');
--Schema-levelexecdbms_auto_index.drop_secondary_indexes('MY_SCHEMA');
--Database-levelexecdbms_auto_index.drop_secondary_indexes;


这让您一清二楚，因此自动索引可以为您做出所有索引决策。


五、删除自动索引


DROP_AUTO_INDEXES 过程允许我们删除自动创建的索引。根据参数值，我们可以删除指定的自动索引，也可以是用户的所有自动索引。


删除指定的自动索引，并确保它不会被重新创建。 请注意，索引名称是 双引号 的！


begindbms_auto_index.drop_auto_indexes(
owner=>'MY_SCHEMA',
index_name=>'"SYS_AI_512bd3h5nif1a"',
allow_recreate=>false);
end;
/


删除指定用户下的所有自动索引，但允许重新创建它们：


begindbms_auto_index.drop_auto_indexes(
owner=>'MY_SCHEMA',
index_name=>null,
allow_recreate=>true);
end;
/


删除当前用户的所有自动索引，但允许重新创建它们：


begindbms_auto_index.drop_auto_indexes(
owner=>null,
index_name=>null,
allow_recreate=>true);
end;
/


在此功能的初始版本中，没有一种机制可以删除由自动索引功能创建的特定索引，或者首先阻止创建特定索引。 Franck Pachot 写了一些可以让你做到这一点的黑客。


六、相关视图


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


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 列，该列指示索引是否由自动索引功能创建。

以下查询可以使用 auto_indexes.sql 脚本：


columnownerformata30columnindex_nameformata30columntable_ownerformata30columntable_nameformata30selectowner,
index_name,
index_type,
table_owner,
table_nametable_typefromdba_indexeswhereauto='YES'orderbyowner, index_name;


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 ：允许的值（文本、HTML、XML）。


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


  • LEVEL ：允许值（基本、典型、全部）。


下面显示了从 SQL 中使用这些函数的一些示例。 注意 LEVEL 参数的引用。 在 SQL 调用中使用 this 时这是必需的，因此它理解这不是对 LEVEL 伪列的引用。


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;


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


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


文章标签：
容器
SQL
关系型数据库
索引
数据库管理
Oracle
安全
数据格式
数据库
XML
关键词：
Oracle数据库一键上云索引
Oracle数据库一键上云19c
Oracle数据库一键上云database
oracle数据库一键上云database 19c
Database 19c
Lucifer三思而后行
目录
相关文章
bjcuug
|
2月前
|
SQL Oracle 关系型数据库
Oracle 19c OCP 认证考试 082 题库(第19题)- 2024年修正版
【优技教育】提供2024年修正版Oracle 19c OCP 082题库，共90题，需60%分数通过，考试时间为150分钟。文档包含详细解析，禁止未授权转载。OCP认证需在Oracle指定机构培训后参加考试，通过082（60%）和083（57%）两门科目方可获得证书。
bjcuug
65 8
bjcuug
|
2月前
|
Oracle 关系型数据库
【优技教育】Oracle 19c OCP 082题库(第8题)- 2024年修正版
这是2024年修正版的Oracle 19c OCP认证题库，包含1Z0-082考试的90道题目，通过分数为60%，考试时间为150分钟。第8题涉及创建表时的约束问题，正确答案为AC。非空约束需在列级声明，且一张表只能有一个LONG列。通过082和083两门考试后可获得OCP证书。
bjcuug
52 4
进步`于辰
|
21天前
|
SQL Oracle 关系型数据库
[Oracle]索引
本文介绍了数据库索引的基本概念、优化查询的原理及分类。索引是一种数据结构（如B树或B+树），通过排序后的`rowid`来优化查询性能。文章详细解释了索引的构建过程、B+树的特点及其优势，并介绍了五种常见的索引类型：唯一索引、组合索引、反向键索引、位图索引和基于函数的索引。每种索引都有其适用场景和限制，帮助读者更好地理解和应用索引技术。
进步`于辰
36 1
[Oracle]索引
小Tomkk
|
1月前
|
Oracle 关系型数据库 网络安全
Oracle 19c 安装教程学习
Oracle 19c 安装教程学习
小Tomkk
61 2
bjcuug
|
2月前
|
Oracle 关系型数据库 数据库
Oracle 19c OCP 认证考试 083 题库(第37题)- 2024年修正版
本文介绍Oracle 19c OCP认证题库中的1Z0-083科目，包含85道试题，需在150分钟内完成，通过分数为57%。重点解析了关于阈值、指标和警报的问题，并指出需通过Oracle指定的WDP机构培训后才能参加考试，考试科目包括082和083，通过后可获得OCP证书。CUUG作为金牌合作机构，提供详细咨询与帮助。
bjcuug
111 1
bjcuug
|
2月前
|
Oracle 关系型数据库
Oracle 19c OCP 认证考试 082 题库(第24题)- 2024年修正版
这是关于Oracle 19c OCP认证考试082题库的修正版，包含90道题目，通过分数为60%，考试时间为150分钟。本文由CUUG原创整理，解析了考试题目，并提供了正确答案和详细解释。通过该认证需完成两门科目考试，合格后可获得OCP证书。
bjcuug
50 4
bjcuug
|
2月前
|
SQL Oracle 关系型数据库
Oracle 19c OCP 认证考试 082 题库(第26题)- 2024年修正版
这是关于Oracle 19c OCP认证考试（1Z0-082）的题库，包含90道题目，通过分数为60%，考试时间为150分钟。本文由CUUG原创整理，重点解析了全球临时表的特点和相关操作，并提供了正确答案及详细解释，帮助考生更好地理解和备考。
bjcuug
43 2
bjcuug
|
2月前
|
Oracle 关系型数据库 数据库
Oracle 19c OCP 认证考试 082 题库(第22题)- 2024年修正版
这是【优技教育】提供的Oracle 19c OCP认证考试082题库修正版，包含90道题目，通过分数为60%，考试时间为150分钟。文章详细解析了第22题，并指出正确答案为B。OCP认证需在Oracle指定的WDP机构培训后参加考试，通过082和083两科方可获得证书。CUUG作为金牌合作机构，可助您快速通过考试。
bjcuug
69 5
bjcuug
|
2月前
|
Oracle 关系型数据库
Oracle 19c OCP 认证考试 082 题库(第18题)- 2024年修正版
这是优技教育提供的Oracle 19c OCP认证考试082题库的2024年修正版，包含90道题目，通过分数为60%，考试时间为150分钟。本文为CUUG原创整理，详细解析了第18题的选择题，并提供了正确答案与解释。需注意，OCP认证需在Oracle指定的WDP机构培训后才能参加考试。
bjcuug
69 7
bjcuug
|
2月前
|
SQL Oracle 关系型数据库
【优技教育】Oracle 19c OCP 082题库(第16题)- 2024年修正版
这是优技教育提供的Oracle 19c OCP认证考试082题库的2024年修正版，包含90道题目，通过分数为60%，考试时间为150分钟。本文解析了题库中的第16题，并详细解释了标量子查询的相关概念与用法，强调其在SQL语句中的嵌套应用及其优势。通过解析帮助考生更好地理解考试内容。原文链接：http://www.cuug.com/index.php?s=/home/article/detail/id/3397.html。
bjcuug
45 7

热门文章

最新文章

  • 1
    Oracle运维笔记之IMPDP导入数据报错ORA-00943
  • 2
    备份及恢复Oracle 10g
  • 3
    oracle11g监听网卡总断（一）
  • 4
    ORACLE union order by
  • 5
    探索ORACLE之RMAN_07 参数文件丢失恢复
  • 6
    登录Oracle数据库遇到ORA-01017密码错误的解决办法
  • 7
    oracle10g Error in invoking target &#39;install&#39; of makefile
  • 8
    oracle express 11g 创建表空间
  • 9
    误删Oracle数据文件的恢复方法
  • 10
    Oracle 用户、对象权限、系统权限
  • 1
    【Database】Sqlserver如何定时备份数据库和定时清除
    248
  • 2
    一篇文章弄懂Oracle和PostgreSQL的Database Link
    102
  • 3
    PostgreSQL 常见问题解决方案 - ERROR: database is being accessed by other users
    1106
  • 4
    Oracle Database Vault Access Control Components
    55
  • 5
    What Is Oracle Database Vault?
    42
  • 6
    19c 开启Oracle Database Vault
    165
  • 7
    Which two statements are true about Database Vault factors?
    63
  • 8
    导入 sql 文件，如果发生 ERROR 1046 (3D000) no database selected 错误
    101
  • 9
    the database has been closed
    77
  • 10
    服务器Centos7 静默安装Oracle Database 12.2
    272

    • 相关课程

    更多
  • Analytic DB MySql产品介绍

    • 相关电子书

    更多
  • PostgresChina2018_樊文凯_ORACLE数据库和应用异构迁移最佳实践
  • PostgresChina2018_王帅_从Oracle到PostgreSQL的数据迁移
  • Oracle云上最佳实践

    • 相关实验场景

    更多
  • 使用SQL语句实现数据表管理
  • 使用SQL语句管理索引
  • SQLServer数据库的创建
  • SQL进阶之约束、索引

    • 推荐镜像

    更多
  • oracle
  • mysql
  • postgresql
    • 下一篇
    无影云桌面