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

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

2021-12-10 171
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 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数据库一键上云索引
Database oracle
Database 19c
Oracle数据库一键上云19c
Oracle数据库一键上云database
Lucifer三思而后行
目录
相关文章
小小工匠
|
4月前
|
SQL Oracle 关系型数据库
Oracle-index索引解读
Oracle-index索引解读
小小工匠
77 0
snowofsummer
|
1月前
|
Oracle 关系型数据库
19c 开启Oracle Database Vault
19c 开启Oracle Database Vault
snowofsummer
22 1
熊猫Jay节字之旅
|
18天前
|
存储 Oracle 关系型数据库
Oracle索引知识看这一篇就足够
Oracle索引知识看这一篇就足够
熊猫Jay节字之旅
19 2
熊猫Jay节字之旅
|
18天前
|
人工智能 Oracle 关系型数据库
一篇文章弄懂Oracle和PostgreSQL的Database Link
一篇文章弄懂Oracle和PostgreSQL的Database Link
熊猫Jay节字之旅
12 1
1941623231718325
|
24天前
|
存储 Oracle 关系型数据库
Oracle 12c的多重索引：数据的“多维导航仪”
【4月更文挑战第19天】Oracle 12c的多重索引提升数据查询效率，如同多维导航仪。在同一表上创建针对不同列的多个索引，加速检索过程。虽然过多索引会增加存储和维护成本，但合理选择和使用索引策略，结合位图、函数索引等高级特性，能优化查询，应对复杂场景。数据管理员应善用这些工具，根据需求进行索引管理，支持企业数据分析。
1941623231718325
23 1
snowofsummer
|
1月前
|
SQL Oracle 安全
Oracle Database Vault Access Control Components
Oracle Database Vault Access Control Components
snowofsummer
10 0
snowofsummer
|
1月前
|
Oracle 安全 关系型数据库
What Is Oracle Database Vault?
The Oracle Database Vault security controls protect application data from unauthorized access, and helps you to comply with privacy and regulatory requirements. You can deploy controls to block privileged account access to application data and control sensitive operations inside the database using
snowofsummer
11 0
肖宜
|
2月前
|
Oracle 关系型数据库 Linux
服务器Centos7 静默安装Oracle Database 12.2
服务器Centos7 静默安装Oracle Database 12.2
肖宜
113 0
进步`于辰
|
3月前
|
SQL Oracle 关系型数据库
[Oracle]索引
[Oracle]索引
进步`于辰
68 0
[Oracle]索引
snowofsummer
|
3月前
|
Oracle 关系型数据库
oracle 19c 搭建dataguard 简要命令
通过service 完成dg 搭建。
snowofsummer
54 0

热门文章

最新文章

  • 1
    数据库中的Schema和Database的区别
  • 2
    项目管理实践【六】自动同步数据库【Using Visual Studio with Source Control System to synchronize database automatically】
  • 3
    【Oracle Database 12c新特性】 In-Database Archiving数据库内归档
  • 4
    第 145 章 sqlmap - automatic SQL injection and database takeover tool
  • 5
    一步一步搭建oracle 11gR2 rac+dg之database安装(五)
  • 6
    解读Oracle Database 12.1新特性Pluggable Databases
  • 7
    RMAN 'Duplicate From Active Database' Feature in 11G [ID 452868.1]
  • 8
    第 3 章 Database Administration
  • 9
    7.1 Connecting to a MySql database from Python
  • 10
    Practice:Demonstrating Database User Objects, Roles and Permissions
  • 1
    服务器Centos7 静默安装Oracle Database 12.2
    113
  • 2
    SpringBoot整合Mybatis连接Oracle数据库
    72
  • 3
    避坑，Oracle中rownum与order by的执行顺序
    30
  • 4
    避坑，Oracle中left join 与 (+) 的区别
    31
  • 5
    Oracle临时表详解
    21
  • 6
    Oracle insert数据时字符串中有‘单引号问题
    56
  • 7
    【Bug记录】Oracle中小数点前面的0不显示的问题
    64
  • 8
    Oracle中merge Into的用法
    18
  • 9
    Flink CDC产品常见问题之使用cdc-Oracle连接器报错如何解决
    64
  • 10
    Oracle系列十八：Oracle RAC
    68

    • 相关电子书

    更多
  • ADAM产品助力企业业务快速云化数据库和应用迁移（ADAM)
  • ORACLE数据库和应用异构 迁移最佳实践
  • GTD GLOBAL TERRORISM DATABASE

    • 相关实验场景

    更多
  • 使用SQL语句管理索引

    • 推荐镜像

    更多
  • oracle
  • debian-archive
  • mysql
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）