Hello all,
Quickly walking through this, as it was new to me (as for everybody, I guess): So what if I’m not happy with Oracle algorithm and I want to drop and AUTO Index on 19c?
Should be easy, like “drop index XXXX;” right? Wrong. See the struggle:
SQL> drop index GREPORA."SYS_AI_9xu652x5fyu5i";
drop index GREPORA."SYS_AI_9xu652x5fyu5i"
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes
OK, so let’s have a look on my auto created indexes. You can see them with flag AUTO as YES on query below:
SQL> select owner, index_name, auto, tablespace_name from dba_indexes natural where auto='YES';
OWNER INDEX_NAME AUTO TABLESPACE_NAME
________ _______________________ _______ __________________
GREPORA SYS_AI_9xu652x5fyu5i YES GREPORA
GREPORA SYS_AI_few32swe423dw YES GREPORA
GREPORA SYS_AI_94osd824n202f YES GREPORA
Ok, so I cannot drop, let me alter it and set it as unusable, then, as it would archive my intend anyway:
SQL> alter index ADMIN."SYS_AI_9xu652x5fyu5i" unusable;
alter index ADMIN."SYS_AI_9xu652x5fyu5i" unusable
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes
Hmmm, so it means I cannot alter it as well?
Well, kind of, there is something I can do:
SQL> alter index ADMIN."SYS_AI_9xu652x5fyu5i" rebuild tablespace GREPORA2 online;
Index altered.
Noice, thanks for nothing. What is changed?
Well, you can use a workaround to move it to a new tablespace and then drop the tablespace:
SQL> drop tablespace GREPORA2 including contents;
Tablespace dropped.
And index will be gone:
SQL> select owner,index_name,auto,tablespace_name from dba_indexes natural where auto='YES';
OWNER INDEX_NAME AUTO TABLESPACE_NAME
________ _______________________ _______ __________________
GREPORA SYS_AI_few32swe423dw YES GREPORA
GREPORA SYS_AI_94osd824n202f YES GREPORA
OK, so that’s bad, right… What the worse part? If you simply drop it, Oracle will probably recreate it, based on algorithm, right?
Gee, thanks for nothing (again)!
This, though, is easier to change: Simply alter the Audo Indexing to report mode:
exec dbms_auto_index.configure('AUTO_INDEX_MODE','REPORT');
This is interesting right?
Ok, we have more options, we can even exclude specific schemas from AUTO_INDEX, choose a default tablespace, define retentions and a lot of things.
As usual, the best reference I could find: https://oracle-base.com/articles/19c/automatic-indexing-19c
Just one thing you might want to consider and it’s not on Tim Hall’s post. Specially if the index is bad for one specific query, is to avoid using it during that specific query. We can do it with session/system parameter:
“_optimizer_use_auto_indexes”=OFF
Well, hope it helps you. Cheers!
