溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹“Oracle Database 19c中自動索引的功能介紹”，在日常操作中，相信很多人在Oracle Database 19c中自動索引的功能介紹問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”Oracle Database 19c中自動索引的功能介紹”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！
1、它能做什么
自動索引功能執行以下操作。
根據表列使用情況確定潛在的自動索引。文檔稱這些為“候選索引(candidate indexes)”。
將自動索引創建為不可見索引，因此不會在執行計劃中使用它們。索引名稱包括“SYS_AI”前綴。
根據SQL語句測試不可見的自動索引，以確保它們能提高性能。如果它們導致性能提高，則可以它們可見。如果性能未得到改善，則相關的自動索引將標記為不可用，稍后將被刪除。針對失敗的自動索引測試的SQL語句被列入黑名單，因此將來不會考慮將它們用于自動索引。第一次對數據庫運行SQL時，優化程序不會考慮自動索引。
刪除未使用的索引。
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 屬性控制用于自動索引的開關，該屬性具有以下允許值：
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');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、其它配置
您可能希望考慮其他參數，這些都在此詳細說明。
AUTO_INDEX_COMPRESSION：據推測用于控制壓縮程度。默認為“OFF”。
AUTO_INDEX_REPORT_RETENTION：自動索引日志的保留期。默認31天。
AUTO_INDEX_RETENTION_FOR_AUTO：未使用的自動索引的保留期。 默認373天。
AUTO_INDEX_RETENTION_FOR_MANUAL：未使用的手動創建索引的保留期。設置為NULL時，不考慮手動創建的索引。默認為NULL。
AUTO_INDEX_SPACE_BUDGET：用于自動索引存儲的默認永久表空間的百分比。使用 AUTO_INDEX_DEFAULT_TABLESPACE 參數指定自定義表空間時，將忽略此參數。
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 函數報告上次自動索引操作。兩者都允許您使用以下參數定制輸出。
TYPE：允許值（TEXT，HTML，XML）。
SECTION：允許值（SUMMARY，INDEX_DETAILS，VERIFICATION_DETAILS，ERRORS，ALL）。您還可以使用帶有 “+” 和 “-” 字符的組合來指示是否應包含或排除某些內容。 例如'SUMMARY + ERRORS'或'ALL -ERRORS'。
LEVEL：允許值（BASIC，TYPICAL，ALL）。
從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 : Executions with fatal error : ------------------------------------------------------------------------------- SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : Indexes created : Space used : 0 B Indexes dropped : SQL statements verified : SQL statements improved : SQL plan baselines created : Overall improvement factor : 0x ------------------------------------------------------------------------------- SUMMARY (MANUAL INDEXES) ------------------------------------------------------------------------------- Unused indexes : Space used : 0 B Unusable indexes : ------------------------------------------------------------------------------- ERRORS --------------------------------------------------------------------------------------------- No errors found. --------------------------------------------------------------------------------------------- SQL>
到此，關于“Oracle Database 19c中自動索引的功能介紹”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
