oracle篇—19c新特性自动索引介绍

☘️博主介绍☘️：

✨又是一天没白过，我是奈斯，DBA一名✨

✌✌️擅长Oracle、MySQL、SQLserver、Linux，也在积极的扩展IT方向的其他知识面✌✌️

❣️❣️❣️大佬们都喜欢静静的看文章，并且也会默默的点赞收藏加关注❣️❣️❣️

经过一段时间的沉寂，我回来了！今天要给大家分享Oracle数据库在19c版本中引入的一个革命性的新特性：自动索引。

自动索引，如其名，是为了简化Oracle数据库的索引管理而生的。自动索引会根据应用程序工作负载的变化自动创建、重建和删除数据库中的索引，从而提高数据库性能。

现在，想象一下这样一个场景：数据库不再需要你手动干预，它可以根据应用程序的工作负载变化，智能地创建、重建或删除索引。这不仅大大减轻了DBA的工作负担，更重要的是，它能够根据实际的数据访问模式，自动优化数据库的性能。

那么，自动索引是如何工作的呢？它又是如何帮助我们提高数据库的性能呢？别着急，接下来我会详细为大家解读这一强大的新功能。

在开始今天的内容之前我先介绍一下索引的优势、缺点，还有索引在什么情况下效果不好，以便更好的去了解怎么正确的使用索引。

索引的优势：

1.索引是表的一个概念部分，用来提高检索数据的效率，ORACLE使用了一个复杂的自平衡B-tree结构.通常，通过索引查询数据比全表扫描要快.当ORACLE找出执行查询和Update语句的最佳路径时，ORACLE优化器将使用索引，同样在联结多个表时使用索引也可以提高效率。

2.另一个使用索引的好处是，它提供了主键(primary key)的唯一性验证。那些LONG或LONG RAW数据类型，你可以索引几乎所有的列。通常在大型表中使用索引特别有效，当然你也会发现在扫描小表时，使用索引同样能提高效率。

索引的缺点：

1.存储索引占用磁盘空间

2.执行DML操作时，产生索引维护索引是提高数据查询最有效的方法，也是最难全面掌握的技术，因为正确的索引可能使效率提高10000倍，而无效的索引可能是浪费了数据库空间，甚至大大降低查询性能。

在表上创建更多的索引不意味着更快的查询，在有索引的表上被提交的每个DML操作都意味着要必须更新所有的索引。表上的索引越多，每当有记录在表中增减或索引列被修改时，索引本身也会被修改，这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4、5次的磁盘1/O，因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。而且表越大，oracle服务器的影响越大。

3.表上有过多索引会严重影响insert插入性能

4.对delete操作，删除少量数据索引可以有效快速定位；如果删除大量数据就会有负面影响

5.对于update操作类似delete，而且如果更新的是非索引列则无影响

索引在什么情况下效果不好：

1.查询结果超过30%的表记录，意味着数据经过过滤后返回的数据仍然很多，那么使用索引过滤不一定好。但是随着优化器的优化固定的百分比不再决定是使用索引还是全表扫描。优化器现在更复杂了，它的估计基于其他因素，如表大小、行数和I/O块大小。

2.有时候索引比全表慢，但这是同一个数据量级的区别，小表。

One

Two

Three

开始今天的内容

官方文档对自动索引的详细介绍（19c）：

Managing Indexes

自动索引限制：

(1)自动索引只能创建本地B树索引。

(2)可以为分区表和非分区表创建自动索引。

(3)无法为临时表创建自动索引。

自动索引工作原理：

自动索引过程每15分钟在后台运行一次，并执行以下操作：
(1)识别自动索引候选：
自动索引候选项是根据SQL语句中表列的使用情况标识的。必须确保表统的计数据是最新的，没有统计信息的表不考虑自动索引，没有实时统计信息则不考虑使用陈旧统计信息的表进行自动索引。

(2)为自动索引候选创建不可见的自动索引：
自动索引候选项创建为看不见自动索引，即这些自动索引不能在SQL语句中使用。自动索引可以是单列的，也可以是多列的考虑的理由如下：
表列(包括虚拟列)
分区表和非分区表

(3)根据SQL语句验证不可见的自动索引：
不可见的自动索引根据SQL语句进行验证。如果通过使用这些索引来提高SQL语句的性能，则将索引配置为可见索引，以便它们可以在SQL语句中使用。
如果使用这些索引没有提高SQL语句的性能，则将索引配置为不可用索引和SQL语句是黑名单。之后，自动索引过程会删除不可用的索引。黑名单中的SQL语句以后不允许使用自动索引。数据库中首次运行的SQL语句不能使用自动索引，原因是Oracle无法对该SQL语句做出有效的判断。

(4)删除未使用的自动索引：
长期不使用的自动索引将被删除。默认情况下373天后将删除未使用的自动索引。在数据库中保留未使用的自动索引的时间段可以使用DBMS_AUTO_INDEX.CONFIGURE程序。

自动索引相关包：

dbms_auto_index.report_activity：返回在数据库中特定期间执行的自动索引操作的报告。

dbms_auto_index.report_last_activity：返回在数据库中执行的最后一次自动索引操作的报告。

dbms_auto_index.configure：配置与自动索引相关的设置。

dbms_auto_index.drop_secondary_indexes：从架构或表中删除所有索引(用于约束的索引除外)。

dbms_auto_index.drop_auto_indexes：此过程可用于手动删除覆盖保留参数设置的自动创建的索引。

自动索引相关视图：

select * from DBA_AUTO_INDEX_CONFIG;  ---显示用于自动索引的当前配置设置。如果处于cdb模式，会列出当前pdb的配置，如果用sqlplus / as sysdba则会显示CDB$ROOT的配置。



select * from DBA_INDEXES;     ---这个AUTO列指示索引是否为自动索引(YES)或不(NO).



select s.NAME,

       t.PARAMETER_NAME,

       t.PARAMETER_VALUE,

       t.LAST_MODIFIED,

       t.MODIFIED_BY

  from cdb_auto_index_config t, v$containers s

where t.CON_ID = s.CON_ID;              ---cdb模式下会列出每个pdb的配置情况

19c使用DBMS_AUTO_INDEX包需要先开启参数：

alter system set "_exadata_feature_on"=true scope=spfile; ---19c版本中此功能仅限于企业版。通过设置初始化参数“_exadata_feature_on=true”进行测试（19c需要设置参数才能使用DBMS_AUTO_INDEX包，不然报ORA-40216: feature not supported，需要重启生效，看19c之后的版本支不支持不设置参数就可以使用自动索引功能）

一、启动、禁用、配置自动索引（默认是禁用的）：

通过DBMS_AUTO_INDEX.CONFIGURE存储过程来启用、禁用、配置自动索引。

SQL> select * from DBA_AUTO_INDEX_CONFIG;

AUTO_INDEX_SCHEMA：要包含或排除使用自动索引的用户。
AUTO_INDEX_DEFAULT_TABLESPACE：用于存储自动索引的表空间。默认值是NULL，这意味着在创建数据库期间指定的默认永久表空间用于存储自动索引。
AUTO_INDEX_SPACE_BUDGET：空间大小用于自动索引的百分比
AUTO_INDEX_RETENTION_FOR_AUTO：在数据库中保留未使用的自动索引的天数，然后删除它们。默认值为373天。
AUTO_INDEX_RETENTION_FOR_MANUAL：在数据库中保留未使用的手动创建的索引(非自动索引)的天数，然后删除它们。当它被设置为NULL，自动索引过程不会删除手动创建的索引。默认值是NULL.。
AUTO_INDEX_MODE：自动索引的操作方式。

IMPLEMENT：在此模式下，新的自动索引创建为可见索引和任何现有的看不见自动索引也被设置为可见索引。在这种模式下，可以在SQL语句中使用自动索引。
REPORT ONLY：在此模式下，新的自动索引创建为看不见索引，不能在SQL语句中使用。
OFF：将模式设置为OFF防止考虑和创建新的自动索引。但是，它并不禁用现有的自动索引。
AUTO_INDEX_COMPRESSION：自动索引的高级索引压缩。默认值是OFF.
AUTO_INDEX_REPORT_RETENTION：在删除自动索引日志之前，在数据库中保留这些日志的天数。由于自动索引报告是根据这些日志生成的，因此不能在指定的值以外的时间内生成自动索引报告。AUTO_INDEX_REPORT_RETENTION。默认值为373天。

(1)库级别禁用：

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF'); ---禁用(现有的自动索引仍然启用)

(2)库级别启动：

默认情况下，如果启用了自动索引，那么数据库中的所有用户都可以使用自动索引。

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT'); ---启用，并将任何新的自动索引创建为可见索引，以便它们可以在SQL语句中使用

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY'); ---启用，但将任何新的自动索引创建为看不见索引，因此它们不能在SQL语句中使用

(3)用户级别：

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'LEI', FALSE);  ---除了用户LEI之外，其他用户使用自动索引

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'LEI', NULL);   ---只有LEI用户可使用自动索引

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', NULL, TRUE);  ---所有用户都可以使用自动索引

(4)指定未使用的自动索引的保留时间：

使用AUTO_INDEX_RETENTION_FOR_AUTO配置设置以指定用于在数据库中保留未使用的自动索引的保留时间。未使用的自动索引在指定的保留期后被删除。

默认情况下，373天后将删除未使用的自动索引。

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', '90'); ---未使用的自动索引的保留期设置为90天。

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', NULL); ---未使用的自动索引的保留期重置为373天的默认值。

(5)指定未使用的非自动索引（手动创建的索引）的保留时间：

使用AUTO_INDEX_RETENTION_FOR_MANUAL设置指定在数据库中未使用的非自动索引(手动创建的索引)的保留时间。未使用的非自动索引将在指定的保留期后删除。

默认情况下，自动索引包不会删除未使用的非自动索引（手动创建的索引）.

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_MANUAL', '60'); ---未使用的非自动索引（手动创建的索引）的保留期设置为60天。

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_MANUAL', NULL); ---未使用的非自动索引（手动创建的索引）的保留期设置为NULL，就不会被自动索引包删除。

(6)指定自动化索引日志的保留时间

使用AUTO_INDEX_REPORT_RETENTION配置设置以指定用于在数据库中保留自动索引日志的时间段。在指定的保留期之后，自动索引日志将被删除。

默认情况下，373天后会删除自动索引日志。

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_REPORT_RETENTION', '60'); ---将自动索引日志的保留期设置为60天。

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_REPORT_RETENTION', NULL); ---将自动索引日志的保留期重置为默认值373天。

ps：其他指定要存储自动索引的表空间、指定要分配给自动索引的表空间百分比、为自动索引配置高级索引压缩案例参考官方文档。

二、生成自动索引报告

使用REPORT_ACTIVITY和REPORT_LAST_ACTIVITY函数生成自动索引操作相关的报告。

dbms_auto_index.report_activity：返回在数据库中特定期间执行的自动索引操作的报告。

dbms_auto_index.report_last_activity：返回在数据库中执行的最后一次自动索引操作的报告

(1)dbms_auto_index.report_activity使用默认配置生成报告（默认是过去24小时自动索引操作的信息，以纯文本格式text生成）

SQL> declare

report clob := null;

begin

report := DBMS_AUTO_INDEX.REPORT_ACTIVITY();

end;

/



SQL> SELECT DBMS_AUTO_INDEX.report_activity(

type => 'HTML')      ---可以是html、text、xml。默认值是text

FROM dual;    ---查看默认24小时文本格式的HTML报告

(2)dbms_auto_index.report_activity指定时间间隔、报告类型、级别、内容生成报告

SQL> declare

report clob := null;

begin

report := DBMS_AUTO_INDEX.REPORT_ACTIVITY(

activity_start => TO_TIMESTAMP('2018-11-01', 'YYYY-MM-DD'),   ---开始时间

activity_end   => TO_TIMESTAMP('2018-12-01', 'YYYY-MM-DD'),   ---结束时间

  type         => 'HTML',       ---报告类型，默认值是TEXT

section       => 'SUMMARY',   ---报告内容

level         => 'BASIC');      ---报告级别

end;

/



SQL> SELECT DBMS_AUTO_INDEX.report_activity(

activity_start => SYSTIMESTAMP-2,

activity_end   => SYSTIMESTAMP-1,

type           => 'HTML')      ---可以是html、text、xml。默认值是text

FROM   dual;         ---查看前一天的THML格式的报告

(3)dbms_auto_index.report_last_activity生成上一次自动索引操作的报告

SQL> declare

report clob := null;

begin

report := DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY();

end;

/



SQL> SELECT DBMS_AUTO_INDEX.report_last_activity(

type => 'HTML')      ---可以是html、text、xml。默认值是text

FROM dual;    ---查看最后一次自动索引操作的HTML报告

(4)dbms_auto_index.report_last_activity生成包含有关上一次自动索引操作的信息

SQL> declare

report clob := null;

begin

report := DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY(

type    => 'HTML',

   section => 'SUMMARY +INDEX_DETAILS +ERRORS',

level   => 'BASIC');

end;

/



SQL> SELECT DBMS_AUTO_INDEX.report_last_activity(

type     => 'HTML',      ---可以是html、text、xml。默认值是text

section => 'ALL',

"LEVEL" => 'ALL')

FROM   dual;     ---查看最后一次自动索引的html报告

案例开始：库级别开启自动索引（19c）

（1）19c使用自动索引开启参数（19c需要设置参数才能使用DBMS_AUTO_INDEX包，不然报ORA-40216: feature not supported，需要重启生效，看19c之后的版本支不支持不设置参数就可以使用自动索引功能）

SQL> alter system set "_exadata_feature_on"=true scope=spfile; ---19c版本中此功能仅限于企业版。通过设置初始化参数“_exadata_feature_on=true”进行测试

（2）开启库级别的自动索引

默认情况下，如果启用了自动索引，那么数据库中的所有用户都可以使用自动索引。

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

（3）查看库中是否有自动索引和自动索引的配置

SQL> select * from dba_indexes where auto='YES';   ---查看库中自动创建索引的索引



SQL> select s.NAME,

       t.PARAMETER_NAME,

       t.PARAMETER_VALUE,

       t.LAST_MODIFIED,

       t.MODIFIED_BY

  from cdb_auto_index_config t, v$containers s

where t.CON_ID = s.CON_ID;              ---cdb模式下会列出每个pdb的配置情况

AUTO_INDEX_SCHEMA：要包含或排除使用自动索引的用户。
AUTO_INDEX_DEFAULT_TABLESPACE：用于存储自动索引的表空间。默认值是NULL，这意味着在创建数据库期间指定的默认永久表空间用于存储自动索引。
AUTO_INDEX_SPACE_BUDGET：空间大小用于自动索引的百分比
AUTO_INDEX_RETENTION_FOR_AUTO：在数据库中保留未使用的自动索引的天数，然后删除它们。默认值为373天。
AUTO_INDEX_RETENTION_FOR_MANUAL：在数据库中保留未使用的手动创建的索引(非自动索引)的天数，然后删除它们。当它被设置为NULL，自动索引过程不会删除手动创建的索引。默认值是NULL.。
AUTO_INDEX_MODE：自动索引的操作方式。
IMPLEMENT：在此模式下，新的自动索引创建为可见索引和任何现有的看不见自动索引也被设置为可见索引。在这种模式下，可以在SQL语句中使用自动索引。
REPORT ONLY：在此模式下，新的自动索引创建为看不见索引，不能在SQL语句中使用。
OFF：将模式设置为OFF防止考虑和创建新的自动索引。但是，它并不禁用现有的自动索引。
AUTO_INDEX_COMPRESSION：自动索引的高级索引压缩。默认值是OFF.
AUTO_INDEX_REPORT_RETENTION：在删除自动索引日志之前，在数据库中保留这些日志的天数。由于自动索引报告是根据这些日志生成的，因此不能在指定的值以外的时间内生成自动索引报告。AUTO_INDEX_REPORT_RETENTION。默认值为373天。

（4）执行一条全表扫的sql，耗时31秒

数据库中首次运行的SQL语句不能使用自动索引，原因是Oracle无法对该SQL语句做出有效的判断。所以多执行几次

SQL>

select b.logtime, a.name, a.cardid, b.amount, b.goods, b.score

from itpux_member a,itpux_sales b

where a.region = '省份'

    and a.recommend = 'lf'

    and b.logtime > to_date('2016-12-15', 'yyyy-mm-dd')

and a.cardid = b.cardid

order by b.logtime;



SQL> select * from dba_indexes where auto='YES';   ---自动创建了SYS_AI_3cymkgsdnarfp的自动索引



SQL>

select b.logtime, a.name, a.cardid, b.amount, b.goods, b.score

from itpux_member a,itpux_sales b

where a.region = '省份'

    and a.recommend = 'lf'

    and b.logtime > to_date('2016-12-15', 'yyyy-mm-dd')

and a.cardid = b.cardid

order by b.logtime;

执行时间降到0.01秒，速度快了N倍

（5）生成自动索引报告

SQL> declare

report clob := null;

begin

report := DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY();

end;

/

SQL> SELECT DBMS_AUTO_INDEX.report_last_activity(

type => 'HTML') ---可以是html、text、xml。默认值是text

FROM dual; ---查看最后一次自动索引操作的HTML报告