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

Posted in: Oracle, Technical Track

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!

email

Interested in working with Matheus? Schedule a tech call.

About the Author

Lead Database Consultant
Well known in the Oracle community in Latin America and Europe where he participates regularly in technology events, Matheus is actually the youngest Oracle ACE Director in the world. Lead Database Consultant at Pythian, Matheus is a Computer Scientist by PUCRS and has been working as an Oracle DBA for the last 10 years.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *