原文地址：https://oracle-base.com/articles/19c/automatic-indexing-19c#drop-an-automatic-index
原文作者：Tim Hall
Oracle 數(shù)據(jù)庫 19c 引入了自動(dòng)索引功能，它可以讓您將一些有關(guān)索引管理的決策交給數(shù)據(jù)庫。
目錄
一、介紹
自動(dòng)索引功能包括以下幾個(gè)特性：
- 可以根據(jù)數(shù)據(jù)表中列使用情況識(shí)別潛在的自動(dòng)索引。 我們可以稱之為 “候選索引”。
- 將自動(dòng)索引創(chuàng)建為不可見索引，因此它們不會(huì)在執(zhí)行計(jì)劃中使用。 索引名稱包括“SYS_AI”前綴。
- 針對(duì) SQL 語句測(cè)試不可見的自動(dòng)索引以確保它們能夠提升性能。 如果它們確實(shí)可以提高性能，就會(huì)變成可見索引。反之，如果性能沒有得到改善，相關(guān)的自動(dòng)索引會(huì)被標(biāo)記為不可用并隨后被刪除。 針對(duì)失敗的自動(dòng)索引測(cè)試的 SQL 語句被列入阻止列表，因此將來不會(huì)考慮將它們用于自動(dòng)索引。 優(yōu)化器不會(huì)在第一次對(duì)數(shù)據(jù)庫運(yùn)行 SQL 時(shí)考慮自動(dòng)索引。
- 刪除未使用的索引。
由于從未在
Exadata上使用過此功能，因此無法評(píng)論其有效性。
二、先決條件
此功能目前僅限于工程系統(tǒng)上的企業(yè)版，如此處所述。 有一種通過啟用 _exadata_feature_on 初始化參數(shù)進(jìn)行測(cè)試的解決方案。
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
此方式不建議在生產(chǎn)系統(tǒng)進(jìn)行使用，僅用于測(cè)試所用。
三、配置方式
DBMS_AUTO_INDEX 包用于管理自動(dòng)索引功能， 基本管理如下所述。
1、檢查配置
CDB_AUTO_INDEX_CONFIG 視圖顯示當(dāng)前的自動(dòng)索引配置，以下查詢使用 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>
如果我們切換到用戶定義的可插拔數(shù)據(jù)庫，我們就只能查看該容器的值。
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>
關(guān)于該參數(shù)的詳細(xì)描述可參考官方文檔：DBMS_AUTO_INDEX！
2、啟用/禁用自動(dòng)索引
我們可以使用 DBMS_AUTO_INDEX 包的 CONFIGURE 過程配置來自動(dòng)索引。
自動(dòng)索引 的開關(guān)是使用 AUTO_INDEX_MODE 屬性控制的，該屬性具有以下幾種模式：
- IMPLEMENT：打開自動(dòng)索引，提高性能的新索引變得可見并可供優(yōu)化器使用。
- REPORT ONLY：打開自動(dòng)索引，但新索引仍然不可見。
- OFF: 關(guān)閉自動(dòng)索引。
可以使用以下命令進(jìn)行模式間的切換：
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、自動(dòng)索引的表空間
默認(rèn)情況下，在默認(rèn)永久表空間中創(chuàng)建自動(dòng)索引。 如果想使用新的表空間來創(chuàng)建，可以使用 AUTO_INDEX_DEFAULT_TABLESPACE 屬性指定一個(gè)表空間來保存它們。 下面我們創(chuàng)建一個(gè)表空間來保存自動(dòng)索引，并相應(yīng)地設(shè)置屬性。
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');
設(shè)置為 NULL 則代表使用默認(rèn)的永久表空間：
Exec dbms_auto_index.configure('AUTO_INDEX_DEFAULT_TABLESPACE',NULL);
4、用戶級(jí)控制
啟用自動(dòng)索引后，在嘗試識(shí)別候選索引時(shí)會(huì)考慮所有用戶。 您可以使用 AUTO_INDEX_SCHEMA 屬性更改默認(rèn)行為，這允許您維護(hù)包含/排除列表。
如果 ALLOW 參數(shù)設(shè)置為 TRUE，則將指定的用戶添加到包含列表中。 請(qǐng)注意，它構(gòu)建了一個(gè)包含用戶的謂詞。
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 參數(shù)值將包含列表清空：
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 參數(shù)設(shè)置為 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 參數(shù)值將排除列表清空：
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>
5、其他配置
如果需要了解其他參數(shù)，下面詳細(xì)說明了這些參數(shù)：
- AUTO_INDEX_COMPRESSION ：文檔中未說明，大概用于控制壓縮級(jí)別， 默認(rèn)
OFF - AUTO_INDEX_REPORT_RETENTION ：自動(dòng)索引日志的保留期。 報(bào)告基于這些日志，默認(rèn)
31天。 - AUTO_INDEX_RETENTION_FOR_AUTO ：未使用的自動(dòng)索引的保留期，默認(rèn)
373天。 - AUTO_INDEX_RETENTION_FOR_MANUAL ：未使用的手動(dòng)創(chuàng)建索引的保留期，當(dāng)設(shè)置為
NULL時(shí)，不考慮刪除手動(dòng)創(chuàng)建的索引，默認(rèn)為空。 - AUTO_INDEX_SPACE_BUDGET ：用于自動(dòng)索引存儲(chǔ)的默認(rèn)永久表空間的百分比，使用 AUTO_INDEX_DEFAULT_TABLESPACE 參數(shù)指定自定義表空間時(shí)，將忽略此參數(shù)。
四、刪除二級(jí)索引
?? 做這個(gè)之前要仔細(xì)考慮，測(cè)試，測(cè)試，測(cè)試！
如果你真的勇氣非凡，DROP_SECONDARY_INDEXES 過程將刪除除用于約束的索引之外的所有索引。 這可以在表、模式或數(shù)據(jù)庫級(jí)別完成。
-- 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;
這讓您一清二楚，因此自動(dòng)索引可以為您做出所有索引決策。
五、刪除自動(dòng)索引
DROP_AUTO_INDEXES 過程允許我們刪除自動(dòng)創(chuàng)建的索引。根據(jù)參數(shù)值，我們可以刪除指定的自動(dòng)索引，也可以是用戶的所有自動(dòng)索引。
刪除指定的自動(dòng)索引，并確保它不會(huì)被重新創(chuàng)建。 請(qǐng)注意，索引名稱是 雙引號(hào) 的！
begin
dbms_auto_index.drop_auto_indexes(
owner => 'MY_SCHEMA',
index_name => '"SYS_AI_512bd3h5nif1a"',
allow_recreate => false);
end;
/
刪除指定用戶下的所有自動(dòng)索引，但允許重新創(chuàng)建它們：
begin
dbms_auto_index.drop_auto_indexes(
owner => 'MY_SCHEMA',
index_name => null,
allow_recreate => true);
end;
/
刪除當(dāng)前用戶的所有自動(dòng)索引，但允許重新創(chuàng)建它們：
begin
dbms_auto_index.drop_auto_indexes(
owner => null,
index_name => null,
allow_recreate => true);
end;
/
在此功能的初始版本中，沒有一種機(jī)制可以刪除由自動(dòng)索引功能創(chuàng)建的特定索引，或者首先阻止創(chuàng)建特定索引。 Franck Pachot 寫了一些可以讓你做到這一點(diǎn)的黑客。
六、相關(guān)視圖
有幾個(gè)與自動(dòng)索引功能相關(guān)的視圖，如下所示：
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 列，該列指示索引是否由自動(dòng)索引功能創(chuàng)建。
以下查詢可以使用 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 包包含兩個(gè)報(bào)告函數(shù)：
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 函數(shù)允許您顯示指定時(shí)間段內(nèi)的活動(dòng)，默認(rèn)為最后一天。 REPORT_LAST_ACTIVITY 函數(shù)報(bào)告最后一次自動(dòng)索引操作。 兩者都允許您使用以下參數(shù)定制輸出。
- TYPE ：允許的值（文本、HTML、XML）。
- SECTION：允許值（SUMMARY、INDEX_DETAILS、VERIFICATION_DETAILS、ERRORS、ALL）。 您還可以使用“+”和“-”字符的組合來指示是否應(yīng)該包括或排除某些內(nèi)容。 例如“SUMMARY +ERRORS”或“ALL -ERRORS”。
- LEVEL ：允許值（基本、典型、全部）。
下面顯示了從 SQL 中使用這些函數(shù)的一些示例。 注意 LEVEL 參數(shù)的引用。 在 SQL 調(diào)用中使用 this 時(shí)這是必需的，因此它理解這不是對(duì) 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
以下是在創(chuàng)建任何索引之前默認(rèn)活動(dòng)報(bào)告的輸出示例：
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>
有關(guān)更多信息，請(qǐng)參閱：
「喜歡這篇文章，您的關(guān)注和贊賞是給作者最好的鼓勵(lì)」
關(guān)注作者
【版權(quán)聲明】本文為墨天輪用戶原創(chuàng)內(nèi)容，轉(zhuǎn)載時(shí)必須標(biāo)注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權(quán)追究責(zé)任。如果您發(fā)現(xiàn)墨天輪中有涉嫌抄襲或者侵權(quán)的內(nèi)容，歡迎發(fā)送郵件至：contact@modb.pro進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，墨天輪將立刻刪除相關(guān)內(nèi)容。
