更新时间:2022-03-27 14:50:42
Oracle 数据库 19c 引入了自动索引功能,它可以让您将一些有关索引管理的决策交给数据库。
自动索引功能包括以下几个特性:
由于从未在
Exadata
上使用过此功能,因此无法评论其有效性。
此功能目前仅限于工程系统上的企业版,如此处所述。 有一种通过启用 _exadata_feature_on
初始化参数进行测试的解决方案。
export ORACLE_SID=cdb1 export ORAENV_ASK=NO . oraenv export ORAENV_ASK=YES sqlplus / as sysdba <<EOF alter system set "_exadata_feature_on"=true scope=spfile; shutdown immediate; startup; exit; EOF
此方式不建议在生产系统进行使用,仅用于测试所用。
DBMS_AUTO_INDEX
包用于管理自动索引功能, 基本管理如下所述。
CDB_AUTO_INDEX_CONFIG
视图显示当前的自动索引配置,以下查询使用 auto_index_config.sql 脚本。
column parameter_name format a40 column parameter_value format a15 select con_id, parameter_name, parameter_value from cdb_auto_index_config order by 1, 2; CON_ID PARAMETER_NAME PARAMETER_VALUE ---------- ---------------------------------------- --------------- 1 AUTO_INDEX_COMPRESSION OFF 1 AUTO_INDEX_DEFAULT_TABLESPACE 1 AUTO_INDEX_MODE OFF 1 AUTO_INDEX_REPORT_RETENTION 31 1 AUTO_INDEX_RETENTION_FOR_AUTO 373 1 AUTO_INDEX_RETENTION_FOR_MANUAL 1 AUTO_INDEX_SCHEMA 1 AUTO_INDEX_SPACE_BUDGET 50 3 AUTO_INDEX_COMPRESSION OFF 3 AUTO_INDEX_DEFAULT_TABLESPACE 3 AUTO_INDEX_MODE OFF 3 AUTO_INDEX_REPORT_RETENTION 31 3 AUTO_INDEX_RETENTION_FOR_AUTO 373 3 AUTO_INDEX_RETENTION_FOR_MANUAL 3 AUTO_INDEX_SCHEMA 3 AUTO_INDEX_SPACE_BUDGET 50 SQL>
如果我们切换到用户定义的可插拔数据库,我们就只能查看该容器的值。
alter session set container = pdb1; SQL> CON_ID PARAMETER_NAME PARAMETER_VALUE ---------- ---------------------------------------- --------------- 3 AUTO_INDEX_COMPRESSION OFF 3 AUTO_INDEX_DEFAULT_TABLESPACE 3 AUTO_INDEX_MODE OFF 3 AUTO_INDEX_REPORT_RETENTION 31 3 AUTO_INDEX_RETENTION_FOR_AUTO 373 3 AUTO_INDEX_RETENTION_FOR_MANUAL 3 AUTO_INDEX_SCHEMA 3 AUTO_INDEX_SPACE_BUDGET 50 SQL>
关于该参数的详细描述可参考官方文档:DBMS_AUTO_INDEX!
我们可以使用 DBMS_AUTO_INDEX
包的 CONFIGURE
过程配置来自动索引。
自动索引 的开关是使用 AUTO_INDEX_MODE
属性控制的,该属性具有以下几种模式:
可以使用以下命令进行模式间的切换:
exec dbms_auto_index.configure('AUTO_INDEX_MODE','IMPLEMENT'); exec dbms_auto_index.configure('AUTO_INDEX_MODE','REPORT ONLY'); exec dbms_auto_index.configure('AUTO_INDEX_MODE','OFF');
默认情况下,在默认永久表空间中创建自动索引。 如果想使用新的表空间来创建,可以使用 AUTO_INDEX_DEFAULT_TABLESPACE
属性指定一个表空间来保存它们。 下面我们创建一个表空间来保存自动索引,并相应地设置属性。
alter session set container = pdb1; create tablespace auto_indexes_ts datafile size 100m autoextend on next 100m; exec dbms_auto_index.configure('AUTO_INDEX_DEFAULT_TABLESPACE','AUTO_INDEXES_TS');
设置为 NULL
则代表使用默认的永久表空间:
Exec dbms_auto_index.configure('AUTO_INDEX_DEFAULT_TABLESPACE',NULL);
启用自动索引后,在尝试识别候选索引时会考虑所有用户。 您可以使用 AUTO_INDEX_SCHEMA
属性更改默认行为,这允许您维护包含/排除列表。
如果 ALLOW
参数设置为 TRUE
,则将指定的用户添加到包含列表中。 请注意,它构建了一个包含用户的谓词。
exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TEST', allow => TRUE); exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TEST2', allow => TRUE); SQL> CON_ID PARAMETER_NAME PARAMETER_VALUE ---------- ---------------------------------------- ---------------------------------------- 3 AUTO_INDEX_COMPRESSION OFF 3 AUTO_INDEX_DEFAULT_TABLESPACE AUTO_INDEXES_TS 3 AUTO_INDEX_MODE IMPLEMENT 3 AUTO_INDEX_REPORT_RETENTION 31 3 AUTO_INDEX_RETENTION_FOR_AUTO 373 3 AUTO_INDEX_RETENTION_FOR_MANUAL 3 AUTO_INDEX_SCHEMA schema IN (TEST, TEST2) 3 AUTO_INDEX_SPACE_BUDGET 50 SQL>
可以使用 NULL
参数值将包含列表清空:
exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', NULL, allow => TRUE); SQL> CON_ID PARAMETER_NAME PARAMETER_VALUE ---------- ---------------------------------------- ---------------------------------------- 3 AUTO_INDEX_COMPRESSION OFF 3 AUTO_INDEX_DEFAULT_TABLESPACE AUTO_INDEXES_TS 3 AUTO_INDEX_MODE IMPLEMENT 3 AUTO_INDEX_REPORT_RETENTION 31 3 AUTO_INDEX_RETENTION_FOR_AUTO 373 3 AUTO_INDEX_RETENTION_FOR_MANUAL 3 AUTO_INDEX_SCHEMA 3 AUTO_INDEX_SPACE_BUDGET 50 SQL>
如果 ALLOW
参数设置为 FALSE
,则将指定的用户添加到排除列表中:
exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TEST', allow => FALSE); exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TEST2', allow => FALSE); SQL> CON_ID PARAMETER_NAME PARAMETER_VALUE ---------- ---------------------------------------- ---------------------------------------- 3 AUTO_INDEX_COMPRESSION OFF 3 AUTO_INDEX_DEFAULT_TABLESPACE AUTO_INDEXES_TS 3 AUTO_INDEX_MODE IMPLEMENT 3 AUTO_INDEX_REPORT_RETENTION 31 3 AUTO_INDEX_RETENTION_FOR_AUTO 373 3 AUTO_INDEX_RETENTION_FOR_MANUAL 3 AUTO_INDEX_SCHEMA schema NOT IN (TEST, TEST2) 3 AUTO_INDEX_SPACE_BUDGET 50 SQL>
同样的,可以使用 NULL
参数值将排除列表清空:
exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', NULL, allow => FALSE); SQL> CON_ID PARAMETER_NAME PARAMETER_VALUE ---------- ---------------------------------------- ---------------------------------------- 3 AUTO_INDEX_COMPRESSION OFF 3 AUTO_INDEX_DEFAULT_TABLESPACE AUTO_INDEXES_TS 3 AUTO_INDEX_MODE IMPLEMENT 3 AUTO_INDEX_REPORT_RETENTION 31 3 AUTO_INDEX_RETENTION_FOR_AUTO 373 3 AUTO_INDEX_RETENTION_FOR_MANUAL 3 AUTO_INDEX_SCHEMA 3 AUTO_INDEX_SPACE_BUDGET 50 SQL>
如果需要了解其他参数,下面详细说明了这些参数:
OFF
31
天。373
天。NULL
时,不考虑删除手动创建的索引,默认为空。‼️ 做这个之前要仔细考虑,测试,测试,测试!
如果你真的勇气非凡,DROP_SECONDARY_INDEXES
过程将删除除用于约束的索引之外的所有索引。 这可以在表、模式或数据库级别完成。
-- Table-level exec dbms_auto_index.drop_secondary_indexes('MY_SCHEMA', 'MY_TABLE'); -- Schema-level exec dbms_auto_index.drop_secondary_indexes('MY_SCHEMA'); -- Database-level exec dbms_auto_index.drop_secondary_indexes;
这让您一清二楚,因此自动索引可以为您做出所有索引决策。
DROP_AUTO_INDEXES
过程允许我们删除自动创建的索引。根据参数值,我们可以删除指定的自动索引,也可以是用户的所有自动索引。
删除指定的自动索引,并确保它不会被重新创建。 请注意,索引名称是 双引号
的!
begin dbms_auto_index.drop_auto_indexes( owner => 'MY_SCHEMA', index_name => '"SYS_AI_512bd3h5nif1a"', allow_recreate => false); end; /
删除指定用户下的所有自动索引,但允许重新创建它们:
begin dbms_auto_index.drop_auto_indexes( owner => 'MY_SCHEMA', index_name => null, allow_recreate => true); end; /
删除当前用户的所有自动索引,但允许重新创建它们:
begin dbms_auto_index.drop_auto_indexes( owner => null, index_name => null, allow_recreate => true); end; /
在此功能的初始版本中,没有一种机制可以删除由自动索引功能创建的特定索引,或者首先阻止创建特定索引。 Franck Pachot
写了一些可以让你做到这一点的黑客。
有几个与自动索引功能相关的视图,如下所示:
select view_name from dba_views where view_name like 'DBA_AUTO_INDEX%' order by 1; VIEW_NAME -------------------------------------------------------------------------------- DBA_AUTO_INDEX_CONFIG DBA_AUTO_INDEX_EXECUTIONS DBA_AUTO_INDEX_IND_ACTIONS DBA_AUTO_INDEX_SQL_ACTIONS DBA_AUTO_INDEX_STATISTICS DBA_AUTO_INDEX_VERIFICATIONS SQL>
此外,{CDB|DBA|ALL|USER}_INDEXES
视图包括 AUTO
列,该列指示索引是否由自动索引功能创建。
以下查询可以使用 auto_indexes.sql 脚本:
column owner format a30 column index_name format a30 column table_owner format a30 column table_name format a30 select owner, index_name, index_type, table_owner, table_name table_type from dba_indexes where auto = 'YES' order by owner, index_name;
DBMS_AUTO_INDEX
包包含两个报告函数:
DBMS_AUTO_INDEX.REPORT_ACTIVITY ( activity_start IN TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP - 1, activity_end IN TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP, type IN VARCHAR2 DEFAULT 'TEXT', section IN VARCHAR2 DEFAULT 'ALL', level IN VARCHAR2 DEFAULT 'TYPICAL') RETURN CLOB; DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY ( type IN VARCHAR2 DEFAULT 'TEXT', section IN VARCHAR2 DEFAULT 'ALL', level IN VARCHAR2 DEFAULT 'TYPICAL') RETURN CLOB;
REPORT_ACTIVITY
函数允许您显示指定时间段内的活动,默认为最后一天。 REPORT_LAST_ACTIVITY
函数报告最后一次自动索引操作。 两者都允许您使用以下参数定制输出。
下面显示了从 SQL 中使用这些函数的一些示例。 注意 LEVEL 参数的引用。 在 SQL 调用中使用 this 时这是必需的,因此它理解这不是对 LEVEL 伪列的引用。
DBMS_AUTO_INDEX.REPORT_ACTIVITY ( activity_start IN TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP - 1, activity_end IN TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP, type IN VARCHAR2 DEFAULT 'TEXT', section IN VARCHAR2 DEFAULT 'ALL', level IN VARCHAR2 DEFAULT 'TYPICAL') RETURN CLOB; DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY ( type IN VARCHAR2 DEFAULT 'TEXT', section IN VARCHAR2 DEFAULT 'ALL', level IN VARCHAR2 DEFAULT 'TYPICAL') RETURN CLOB;
以下是在创建任何索引之前默认活动报告的输出示例: