1、它能做什么
自动索引功能执行以下操作。
2、先决条件
通过设置初始化参数“_exadata_feature_on=true”进行测试。注:请不要在生产系统中测试。
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
3、配置
使用 DBMS_AUTO_INDEX 包来管理自动索引特性。下面描述了基本管理。
3.1 显示配置
CDB_AUTO_INDEX_CONFIG视图显示当前的自动索引配置。
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;
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
---------- ---------------------------------------- ---------------
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>
3.2 启用/禁用自动索引
使用 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');
3.3 自动索引的表空间
默认情况下,自动索引是在默认的永久表空间中创建的。如果这是不可接受的,您可以使用 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);
3.4 模式级(Schema-Level)控制
一旦启用了自动索引,在尝试识别候选索引时会考虑所有模式。您可以使用AUTO_INDEX_SCHEMA 属性更改默认行为,该属性允许您维护 包含/排除 列表。
如果 ALLOW参数设置为true,则指定的模式(schema)将添加到包含列表中。注意:它构建了一个包含模式的谓词。
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'TEST', allow => TRUE);
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'TEST2', allow => TRUE);
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
---------- ---------------------------------------- ----------------------------------------
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);
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
---------- ---------------------------------------- ----------------------------------------
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);
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
---------- ---------------------------------------- ----------------------------------------
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);
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
---------- ---------------------------------------- ----------------------------------------
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>
4、其它配置
您可能希望考虑其他参数,这些都在此详细说明。
5、删除二级索引
在做这个之前,请仔细考虑,测试,测试,测试!
如果您感觉特别勇敢,DROP_SECONDARY_INDEXES过程将删除除用于约束的索引之外的所有索引。这可以在表、模式(Schema)、数据库级别完成。
-- 表级别
EXEC DBMS_AUTO_INDEX.drop_secondary_indexes('MY_SCHEMA', 'MY_TABLE');
-- 模式(Schema)级别
EXEC DBMS_AUTO_INDEX.drop_secondary_indexes('MY_SCHEMA');
-- 数据库级别
EXEC DBMS_AUTO_INDEX.drop_secondary_indexes;
6、视图
有几个与自动索引功能相关的视图,如下所示:
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列,该列指示索引是否由自动索引功能创建。
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;
7、活动报告
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调用中使用它时,这是必要的,因此这不是对LEVEL伪列的引用。
SET LONG 1000000 PAGESIZE 0
-- 过去24小时的默认TEXT报告。
SELECT DBMS_AUTO_INDEX.report_activity() FROM dual;
-- 最新活动的默认TEXT报告。
SELECT DBMS_AUTO_INDEX.report_last_activity() FROM dual;
-- 前天的HTML报告。
SELECT DBMS_AUTO_INDEX.report_activity(
activity_start => SYSTIMESTAMP-2,
activity_end => SYSTIMESTAMP-1,
type => 'HTML')
FROM dual;
-- 最新活动的HTML报告。
SELECT DBMS_AUTO_INDEX.report_last_activity(
type => 'HTML')
FROM dual;
-- 前天的XML报告包含所有信息。
SELECT DBMS_AUTO_INDEX.report_activity(
activity_start => SYSTIMESTAMP-2,
activity_end => SYSTIMESTAMP-1,
type => 'XML',
section => 'ALL',
"LEVEL" => 'ALL')
FROM dual;
-- 包含所有信息的最新活动的XML报告。
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>