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

2 min read
Jun 8, 2020

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!

Get Email Notifications

No Comments Yet

Let us know what you think