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!
.svg){kind=small}
.svg){kind=small}