19c Auto-Indexing: ORA-65532: Cannot Alter or Drop Automatically Created Indexes
Tags:
Technical Track,
Business Insights
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 indexesThat'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 PYTHIANSo, 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 indexesIt 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 PYTHIANOkay, 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”=OFFI hope this helps. Cheers!
