19c Auto-Indexing: ORA-65532: Cannot Alter or Drop Automatically Created Indexes

Hello!

I recently noticed that: What if I’m not happy with the Oracle algorithm and I want to drop and auto-index on 19c?

It should be easy, I initially thought. For example, something like “drop index XXXX;” right? Wrong. However, it’s not that simple. See this example:

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

That’s a new error, as you can see.
Let’s have a detailed look at my auto-created indexes. You can see them with the AUTO flag set to YES on the query below:

SQL> select owner, index_name, auto, tablespace_name from dba_indexes natural where auto='YES';
OWNER              INDEX_NAME    AUTO    TABLESPACE_NAME
________ _______________________ _______ __________________
PYTHIAN  SYS_AI_9xu652x5fyu5i    YES     PYTHIAN
PYTHIAN  SYS_AI_few32swe423dw    YES     PYTHIAN
PYTHIAN  SYS_AI_94osd824n202f    YES     PYTHIAN

So, I can’t drop the auto-created indexes. Let’s try altering it and set it as unusable then. This would achieve my intent  and cause Oracle to not use the index anyway.

SQL> alter index PYTHIAN."SYS_AI_9xu652x5fyu5i" unusable;
alter index PYTHIAN."SYS_AI_9xu652x5fyu5i" unusable
 *
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes

It appears I can’t alter it either. However, there’s something I can do:

SQL> alter index PYTHIAN."SYS_AI_9xu652x5fyu5i" rebuild tablespace PYTHIAN2 online;
Index altered.

Not exactly what I wanted in the first place, but now I can use a workaround to move it to a new tablespace and then drop the tablespace:

SQL> drop tablespace PYTHIAN2 including contents;
Tablespace dropped.

So the index is gone indeed:

SQL> select owner,index_name,auto,tablespace_name from dba_indexes natural where auto='YES';
OWNER              INDEX_NAME    AUTO    TABLESPACE_NAME
________ _______________________ _______ __________________
PYTHIAN  SYS_AI_few32swe423dw    YES     PYTHIAN
PYTHIAN  SYS_AI_94osd824n202f    YES     PYTHIAN

Okay, so that’s a workaround. However, if you simply drop it, Oracle probably recreates it, based on the algorithm, correct?

For this then, we have an easier method. Simply alter auto-indexing to report mode:

exec dbms_auto_index.configure('AUTO_INDEX_MODE','REPORT');

That’s is interesting, right? Oracle automates the creation but makes it difficult to manage once created.

There are more options, though instead of changing all to report. You can exclude specific schemas from AUTO_INDEX, choose a default tablespace, define retention, and more.

Here is the reference documentation: https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_AUTO_INDEX.html

Lastly, here’s a quick tip for investigations: If you’re wondering whether an auto-index is good (or bad) for your query, try running it after disabling auto-indexes (on a session-level) by using:

“_optimizer_use_auto_indexes”=OFF

I hope this helps. Cheers!