Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the function introduction of automatic index in Oracle Database 19c". In the daily operation, I believe that many people have doubts about the function introduction of automatic index in Oracle Database 19c. The editor has consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the questions of "introduction to the function of automatic index in Oracle Database 19c". Next, please follow the editor to study!

1. What can it do

The automatic indexing function does the following.

Determine potential automatic indexes based on table column usage. The documentation calls these "candidate indexes (candidate indexes)."

Automatic indexes are created as invisible indexes, so they are not used in the execution plan. The index name includes the "SYS_AI" prefix.

Test invisible automatic indexes against SQL statements to ensure that they improve performance. If they cause performance improvements, they can be visible. If performance is not improved, the associated automatic index is marked as unavailable and will be deleted later. SQL statements for failed automatic indexing tests are blacklisted, so they will not be considered for automatic indexing in the future. The first time you run SQL against a database, the optimizer does not consider automatic indexing.

Delete unused indexes.

2. Prerequisites

Test by setting the initialization parameter "_ exadata_feature_on=true". Note: please do not test in the production system.

Export ORACLE_SID=cdb1export ORAENV_ASK=NO. Oraenvexport ORAENV_ASK=YESsqlplus / as sysdba

3.2 enable / disable automatic indexing

Use the CONFIGURE stored procedure for the DBMS_AUTO_INDEX package to configure automatic indexing.

Use the AUTO_INDEX_MODE property to control the switch for automatic indexing, which has the following allowed values:

IMPLEMENT: turns on automatic indexing. New indexes that improve performance are visible and available to the optimizer.

REPORT ONLY: automatic indexing is turned on, but the new index is still not visible.

OFF: turn off automatic indexing.

An example of a command to switch between modes is as follows:

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 automatically indexed tablespaces

By default, automatic indexes are created in the default permanent tablespace. If this is unacceptable, you can use the AUTO_INDEX_DEFAULT_TABLESPACE property to specify a tablespace to hold them. Let's create a tablespace to hold the automatic index and set the properties accordingly.

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

If you want to set to use the default permanent tablespace, you can set it to NULL, as shown in the following command:

EXEC DBMS_AUTO_INDEX.CONFIGURE ('AUTO_INDEX_DEFAULT_TABLESPACE',NULL)

3.4 Mode level (Schema-Level) control

Once automatic indexing is enabled, all patterns are taken into account when trying to identify candidate indexes. You can use the AUTO_INDEX_SCHEMA property to change the default behavior, which allows you to maintain the include / exclude list.

If the ALLOW parameter is set to true, the specified mode (schema) is added to the include list. Note: it builds a predicate that contains the pattern.

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 A40COLUMN parameter_value FORMAT A15SELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_configORDER 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 50SQL >

You can use NULL parameter values to eliminate the inclusion list, as shown below:

EXEC DBMS_AUTO_INDEX.CONFIGURE ('AUTO_INDEX_SCHEMA', NULL, allow = > TRUE); COLUMN parameter_name FORMAT A40COLUMN parameter_value FORMAT A15SELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_configORDER 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 50SQL >

If the ALLOW parameter is set to FALSE, the specified mode is added to the exclusion list.

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 A40COLUMN parameter_value FORMAT A15SELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_configORDER 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 50SQL >

You can use the NULL parameter value to clear the exclusion list.

EXEC DBMS_AUTO_INDEX.CONFIGURE ('AUTO_INDEX_SCHEMA', NULL, allow = > FALSE); COLUMN parameter_name FORMAT A40COLUMN parameter_value FORMAT A15SELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_configORDER 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 50SQL >

4. Other configurations

You may want to consider other parameters, which are described in detail here.

AUTO_INDEX_COMPRESSION: presumably used to control the degree of compression. The default is "OFF".

AUTO_INDEX_REPORT_RETENTION: the retention period of the automatic indexing log. The default is 31 days.

AUTO_INDEX_RETENTION_FOR_AUTO: the retention period of unused automatic indexes. The default is 373 days.

AUTO_INDEX_RETENTION_FOR_MANUAL: unused retention period for manually created indexes. When set to NULL, manually created indexes are not considered. The default is NULL.

AUTO_INDEX_SPACE_BUDGET: the percentage of default permanent tablespaces used for automatic indexing storage. This parameter is ignored when you specify a custom tablespace using the AUTO_INDEX_DEFAULT_TABLESPACE parameter.

5. Delete the secondary index

Before you do this, please think carefully and test!

If you feel particularly brave, the DROP_SECONDARY_INDEXES procedure deletes all indexes except the one used for the constraint. This can be done at the table, schema (Schema), database level. -- 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

6. View

There are several views related to automatic indexing, as follows:

SELECT view_nameFROM dba_viewsWHERE view_name LIKE 'DBA_AUTO_INDEX%'ORDER BY 1 VIEW_NAME----DBA_AUTO_INDEX_CONFIGDBA_AUTO_INDEX_EXECUTIONSDBA_AUTO_INDEX_IND_ACTIONSDBA_AUTO_INDEX_SQL_ACTIONSDBA_AUTO_INDEX_STATISTICSDBA_AUTO_INDEX_VERIFICATIONSSQL >

In addition, the {CDB | DBA | ALL | USER} _ INDEXES view contains an AUTO column indicating whether the index is created by the automatic indexing feature.

COLUMN owner FORMAT A30COLUMN index_name FORMAT A30COLUMN table_owner FORMAT A30COLUMN table_name FORMAT A30SELECT owner, index_name, index_type, table_owner, table_name table_typeFROM dba_indexesWHERE auto = 'YES'ORDER BY owner, index_name

7. Activity report

The DBMS_AUTO_INDEX package contains two reporting functions.

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

The REPORT_ACTIVITY function allows you to display activities for a specified period of time, which defaults to the last day. The REPORT_LAST_ACTIVITY function reports the last automatic indexing operation. Both allow you to customize the output with the following parameters.

TYPE: allowable value (TEXT,HTML,XML).

SECTION: allowable value (SUMMARY,INDEX_DETAILS,VERIFICATION_DETAILS,ERRORS,ALL). You can also use a combination of "+" and "-" characters to indicate whether something should be included or excluded. For example, 'SUMMARY + ERRORS'' or 'ALL-ERRORS'.

LEVEL: allowable value (BASIC,TYPICAL,ALL).

Some examples of using these functions from SQL are shown below. Note that the LEVEL parameter is referenced. This is necessary when you use it in a SQL call, so this is not a reference to a LEVEL pseudo column.

SET LONG 1000000 PAGESIZE 0 TEXT-default TEXT report for the past 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;-- 's HTML report the day before yesterday. SELECT DBMS_AUTO_INDEX.report_activity (activity_start = > SYSTIMESTAMP-2, activity_end = > SYSTIMESTAMP-1,type = > 'HTML') HTML report on the latest activities of FROM dual;--. SELECT DBMS_AUTO_INDEX.report_last_activity (type = > 'HTML') FROM dual;-- 's XML report the day before yesterday contains all the information. SELECT DBMS_AUTO_INDEX.report_activity (activity_start = > SYSTIMESTAMP-2, activity_end = > SYSTIMESTAMP-1, type = > 'XML',section = >' ALL', "LEVEL" = > 'ALL') FROM dual;-- contains the latest active XML report of all information. SELECT DBMS_AUTO_INDEX.report_last_activity (type = > 'HTML', section = >' ALL', "LEVEL" = > 'ALL') FROM dual;SET PAGESIZE 14

The following is an example of the output of the default activity report before any indexes are created.

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 > so far The study on "introduction to automatic indexing in Oracle Database 19c" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.