一 自动化索引 Automatic Indexing

1.1 新特性概述

在 Oracle Database 19c 引进了新的特性。 Automatic Indexing 自动划索引功能，顾名思义就是数据库能够根据应用程序工作负载自动管理索引，比如说创建索引，删除索引，重构索引。 需要格外注意的是，目前自动索引只支持 Oracle Exadata 平台

关于自动索引的数据字典 :

DBA_AUTO_INDEX_CONFIG       - -19.1 新增视图，描述当前自动索引的配置 DBA_INDEXES/ALL_INDEXES/USER_INDEXES   -- 新增加的 AUTO 列标识是自动索引 (YES) 还是手动索引 (NO)

DBA_AUTO_INDEX_EXECUTIONS           -- 显示历史自动索引任务执行

DBA_AUTO_INDEX_STATISTICS             -- 显示与自动索引相关的统计信息

DBA_AUTO_INDEX_IND_ACTIONS          -- 显示在自动索引上执行的操作 DBA_AUTO_INDEX_SQL_ACTIONS   -- 显示在 SQL 上执行的验证自动索引的操作

( 特殊说明 : 当前版本的索引指的是 local B-tree  索引 )

1.2 参数展示

查看关于自动索引的参数 :

select * from DBA_AUTO_INDEX_CONFIG;

参数解析：

AUTO_INDEX_COMPRESSION

开启索引压缩，减少索引占用空间

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_COMPRESSION','ON');

AUTO_INDEX_DEFAULT_TABLESPACE

指定自动索引保存的表空间，方便管理

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE', 'MYAUTOTBS');

AUTO_INDEX_MODE ：

off 表示自动索引的状态是否开启

IMPLEMENT 表示自动创建创建、测试、并报告，最终索引是 visible 状态

REPORT ONLY 会创建索引但是 invisible, 不会影响 SQL ，只是意图生成报告

开启自动索引功能

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

AUTO_INDEX_REPORT_RETENTION:

自动索引报告历史保留的天数 默认 31 天

手动设置报告保留天数为 60 天

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_REPORT_RETENTION', '60');

AUTO_INDEX_RETENTION_FOR_AUTO:

自动创建的索引从上次使用后多少天不再使用的索引可以删除，默认 373 天

手动修改为 90 天

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', ' 90 ');

AUTO_INDEX_RETENTION_FOR_MANUAL:

手动创建的索引从上次使用后多少天不再使用的索引可以删除，默认永远

修改保存时间为 60 天

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_MANUAL', '60');

AUTO_INDEX_SCHEMA ：

指定 HR 能够使用自动索引的用户

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', NULL);

不允许 HR 用户使用自动索引

 EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', FALSE);

AUTO_INDEX_SPACE_BUDGET:

自动索引可以使用表空间大小的百分比，默认 50%

修改为 20%

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SPACE_BUDGET', '20');

二 测试

2.1 开启自动索引

切换到 testpdb

alter session set container=testpdb;

现在开启自动索引参数

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

开启失败，这是因为需要开启一个特殊参数并重启数据库，才能使用自动索引功能

alter system set “_exadata_feature_on”=true scope=spfile;

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

这时候可以看到，自动索引的状态为 IMPLEMENT 表示自动创建创建、测试、并报告，最终索引是 visible 状态

那如何关闭自动索引功能

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

此时自动索引功能已经被关闭

如果只想某个特定的用户使用自动索引功能

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'QY', NULL);

2.2 实验演示

先创建一张测试表，并且反复插入数据

插入后

update test set object_id=rownum;

commit;

多次执行以下语句，并静等 15 分钟查看相关试图是否有结果

select object_name from test where object_id=1;

select object_type from test where object_id=111;

select created from test where object_id=222;

select created from test where object_id=333;

select created from test where object_id=444;

select created from test where object_id=555;

等待 15 分钟以后，查看自动索引的执行情况

select * from dba_auto_index_executions;

可以看到 oracle 自动建立一个索引

在 dba_indexes 的视图中再次确认索引的确已经存在

select index_name from dba_indexes where table_name='test';

我们打开执行计划查看，可以清楚地看到走得是索引

测试完毕，19c 版本的数据库能够根据应用程序工作负载来自动创建索引，提高数据库的运行效率。