oracle 19C新特性——自动索引

一 自动化索引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版本的数据库能够根据应用程序工作负载来自动创建索引，提高数据库的运行效率。