20

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

 3 years ago
source link: https://richardfoote.wordpress.com/2020/09/01/oracle-19c-automatic-indexing-ddl-statements-with-auto-indexes-no-control/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Oracle 19c Automatic Indexing: DDL Statements With Auto Indexes (No Control) September 1, 2020

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Data Warehouse, Autonomous Database, Autonomous Transaction Processing, Drop Automatic Indexing, Drop Index, Index Coalesce, Index Rebuild, Index Shrink, Invisible Indexes, Online DDL, Oracle Indexes.
trackback

outside2.jpg?w=300&h=300

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…

Comments»

2f311c24de89777c38735392c27a465f?s=32&d=identicon&r=G 1. RobK - September 3, 2020

Small mistake: “Many DDL commands are NOT supported with Invisible Indexes” -> “… with Automatic Indexes”

Thanks for the article!
RobK

Liked by 1 person

d665f0bedd1b771fb94defcaae359a7e?s=32&d=identicon&r=GRichard Foote - September 3, 2020

Hi Robk

Thanks heaps for the heads up, now fixed.

It was of course an intentional error designed to test the observational skills of the reader 🙂

Regards

Richard

Leave a Reply Cancel reply


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK