Oracle 19c Automatic Indexing: DDL Statements With Auto Indexes (No Control)

 

I’ve had a number of questions in relation to DDL support for Automatic Indexes since my last post on how one can now drop Automatic Indexes, so decided to quickly discuss what DDL statements are supported with Automatic Indexes.

Many DDL commands are NOT supported with Automatic Indexes, such as making indexes (IN)VISIBLE and (UN)USABLE and changing storage attributes:

 

SQL> alter index "SYS_AI_600vgjmtqsgv3" invisible;

alter index "SYS_AI_600vgjmtqsgv3" invisible
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes


SQL> alter index "SYS_AI_600vgjmtqsgv3" unusable;

alter index "SYS_AI_600vgjmtqsgv3" unusable
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes


SQL> ALTER INDEX "SYS_AI_600vgjmtqsgv3" INITRANS 5;
ALTER INDEX "SYS_AI_600vgjmtqsgv3" INITRANS 5
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes

You also can’t drop indexes with the DDL statement:

SQL> drop index "SYS_AI_600vgjmtqsgv3";

drop index "SYS_AI_600vgjmtqsgv3"
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes

 

Although as discussed in my last post, you can now drop Automatic Indexes by using DBMS_AUTO_INDEX.DROP_AUTO_INDEXES.

 

You can however potentially improve the structure of an Automatic Index by using the REBUILD, COALESCE or SHRINK (SPACE) options:

 

SQL> alter index "SYS_AI_600vgjmtqsgv3" rebuild online;

Index altered.

SQL> alter index "SYS_AI_600vgjmtqsgv3" coalesce;

Index altered.

SQL> alter index "SYS_AI_600vgjmtqsgv3" shrink space;

Index altered.

 

Interestingly, if Oracle considers an Automatic Index but decides it’s not efficient enough to be created, the Automatic Indexing process can leave a new Automatic Index in UNUSABLE / INVISIBLE state (as previously discussed), which can be subsequently rebuilt:

SQL> select index_name, status, visibility from user_indexes where index_name='SYS_AI_600vgjmtqsgv3';

INDEX_NAME                     STATUS   VISIBILIT
------------------------------ -------- ---------
SYS_AI_600vgjmtqsgv3           UNUSABLE INVISIBLE

SQL> alter index "SYS_AI_600vgjmtqsgv3" rebuild online;

Index altered.

SQL> select index_name, status, visibility from user_indexes where index_name='SYS_AI_600vgjmtqsgv3';

INDEX_NAME                     STATUS   VISIBILIT
------------------------------ -------- ---------
SYS_AI_600vgjmtqsgv3           VALID    INVISIBLE

 

So the index is now VALID and actually physically created. But you can’t subsequently make it VISIBLE, which means it can’t ordinarily be used by the CBO:

SQL> alter index "SYS_AI_600vgjmtqsgv3" visible;
alter index "SYS_AI_600vgjmtqsgv3" visible
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes

 

When you rebuild an Automatic Index, you can however change the manner in which it’s compressed:

SQL> select index_name, status, visibility, compression from user_indexes
where index_name='SYS_AI_600vgjmtqsgv3';

INDEX_NAME                     STATUS   VISIBILIT COMPRESSION
------------------------------ -------- --------- -------------
SYS_AI_600vgjmtqsgv3           VALID    INVISIBLE ADVANCED LOW

SQL> alter index "SYS_AI_600vgjmtqsgv3" rebuild nocompress;

Index altered.

SQL> select index_name, status, visibility, compression from user_indexes
where index_name='SYS_AI_600vgjmtqsgv3';

INDEX_NAME                     STATUS   VISIBILIT COMPRESSION
------------------------------ -------- --------- -------------
SYS_AI_600vgjmtqsgv3           VALID    INVISIBLE DISABLED

 

And no, you can’t rename an Automatic Index:

 

SQL> alter index "SYS_AI_600vgjmtqsgv3" rename to BOWIE_INDEX;
alter index "SYS_AI_600vgjmtqsgv3" rename to BOWIE_INDEX
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes

 

So the answer is it depends on what one can and can’t do currently with an Automatic Index, which of course is subject to change in the future…