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

分享于 

16分钟阅读

Oracle

  繁體

Oracle database 19c引入了自动索引功能,它允许你将有关索引管理的一些决策交给数据库。

前提条件

该功能目前仅限于工程系统上的企业版,通过启用" _ 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> @auto_index_config.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>

参数的详细解释>这里

Enable/Disable自动索引

自动索引是使用CONFIGURE过程DBMS_AUTO_INDEX软件包。

自动索引的开关使用AUTO_INDEX_MODE属性,该属性具有下列允许值。

IMPLEMENT:打开自动索引,优化器可显示和使用改进性能的新索引,REPORT ONLY:打开自动索引,但是新索引保持不可见,OFF关闭自动索引,

模式之间的切换示例如下所示。


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);

Schema-Level控件

启用自动索引后,在尝试标识候选索引时将考虑所有模式,你可以使用更改默认行为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> @auto_index_config.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> @auto_index_config.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> @auto_index_config.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> @auto_index_config.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>

其他配置

你可能还想考虑其他参数,它们的详细解释在这里

AUTO_INDEX_COMPRESSION未记录的,可能用于控制压缩级别,默认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-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;

这样一来,你就可以拥有一个干净的状态,因此自动索引可以为你做出所有索引决策。

删除自动索引

没有一种机制可以删除由自动索引功能创建的特定索引,或者防止从头开始创建特定索引。

如何删除由Oracle 19c Auto Indexing创建的索引

视图

有几个与自动索引功能相关的视图,如下所示,


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函数会报告上次自动索引操作,两者都允许你使用下列参数调整输出。

TYPE:允许的值(文本,HTML.XML ),SECTION:允许的值,还可以将组合与"+"和"-"字符一起使用来指示是否应该包含或排除某些内容,例如'SUMMARY ERRORS '或'ALL -ERRORS '。LEVEL:允许的值,

SQL中使用这些函数的一些示例如下所示,请注意LEVEL参数,在SQL调用中使用此参数是必需的,因此它理解这不是对LEVEL伪列。


SET LONG 1000000 PAGESIZE 0

-- Default TEXT report for the last 24 hours.
SELECT DBMS_AUTO_INDEX.report_activity() FROM dual;

-- Default TEXT report for the latest activity.
SELECT DBMS_AUTO_INDEX.report_last_activity() FROM dual;

-- HTML Report for the day before yesterday.
SELECT DBMS_AUTO_INDEX.report_activity(
 activity_start => SYSTIMESTAMP-2,
 activity_end => SYSTIMESTAMP-1,
 type => 'HTML')
FROM dual;

-- HTML report for the latest activity.
SELECT DBMS_AUTO_INDEX.report_last_activity(
 type => 'HTML')
FROM dual;

-- XML Report for the day before yesterday with all information.
SELECT DBMS_AUTO_INDEX.report_activity(
 activity_start => SYSTIMESTAMP-2,
 activity_end => SYSTIMESTAMP-1,
 type => 'XML',
 section => 'ALL',
 "LEVEL" => 'ALL')
FROM dual;

-- XML report for the latest activity with all information.
SELECT DBMS_AUTO_INDEX.report_last_activity(
 type => 'HTML',
 section => 'ALL',
 "LEVEL" => 'ALL')
FROM dual;

SET PAGESIZE 14

以下是在创建索引之前默认活动报告的输出示例。


SELECT DBMS_AUTO_INDEX.report_activity() FROM dual;

GENERAL INFORMATION
-------------------------------------------------------------------------------
 Activity start : 03-JUN-2019 21:59:21
 Activity end : 04-JUN-2019 21:59:21
 Executions completed : 2
 Executions interrupted : 0
 Executions with fatal error : 0
-------------------------------------------------------------------------------

SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
 Index candidates : 0
 Indexes created : 0
 Space used : 0 B
 Indexes dropped : 0
 SQL statements verified : 0
 SQL statements improved : 0
 SQL plan baselines created : 0
 Overall improvement factor : 0x
-------------------------------------------------------------------------------

SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
 Unused indexes : 0
 Space used : 0 B
 Unusable indexes : 0
-------------------------------------------------------------------------------

ERRORS
---------------------------------------------------------------------------------------------
No errors found.
---------------------------------------------------------------------------------------------

SQL>


数据  AUTO  index  Oracle  索引  DBM  
相关文章