Automatic Indexing - Part XI

In this blog post we will see about how to effectively drop an Automatic Indexes (AI). For a number of reason we can easily left with an AI that we might get in the way of things and we might want to drop it.
 
We can’t drop an AI. The only “supported” manner to drop an AI is to wait for AI retention period to be exceeded (which is by default some 373 days and assume that index is not used during this period)
 
By the way of demo we got the following AI in my schema.
 
ai_demo@PDB19> select index_name,table_name,status,visibility,auto,constraint_index
  2  from user_indexes
  3  where auto ='YES';
 
INDEX_NAME                TABLE_NAME      STATUS   VISIBILIT AUT CON
------------------------- --------------- -------- --------- --- ---
SYS_AI_7mx8mdmfk2saq      DEMO009B        UNUSABLE INVISIBLE YES NO
SYS_AI_3vq6p6haf4kc0      DEMO009C        UNUSABLE INVISIBLE YES NO
SYS_AI_9dj7yy4b1w2ss      DEMO010         VALID    VISIBLE   YES NO
 
If we try to turn on an “invisible” index back to visible we may end up with error.
 
ai_demo@PDB19> alter index "SYS_AI_7mx8mdmfk2saq" visible;
alter index "SYS_AI_7mx8mdmfk2saq" visible
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes
 
Because we can’t alter an AI to be visible/invisible.
 
If we try to drop an AI
 
ai_demo@PDB19> drop index "SYS_AI_7mx8mdmfk2saq" ;
drop index "SYS_AI_7mx8mdmfk2saq"
           *
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes
 
Again, we can’t simply drop an AI.
 
However, we are allowed to rebuild or coalesce or shrink an AI
 
ai_demo@PDB19> select index_name,table_name,status,visibility,auto,constraint_index
  2  from user_indexes
  3  where auto ='YES';
 
INDEX_NAME                TABLE_NAME      STATUS   VISIBILIT AUT CON
------------------------- --------------- -------- --------- --- ---
SYS_AI_7mx8mdmfk2saq      DEMO009B        UNUSABLE INVISIBLE YES NO
SYS_AI_3vq6p6haf4kc0      DEMO009C        UNUSABLE INVISIBLE YES NO
SYS_AI_9dj7yy4b1w2ss      DEMO010         VALID    VISIBLE   YES NO
 
ai_demo@PDB19> alter index "SYS_AI_7mx8mdmfk2saq" rebuild ;
 
Index altered.
 
ai_demo@PDB19> select index_name,table_name,status,visibility,auto,constraint_index
  2  from user_indexes
  3  where auto ='YES';
 
INDEX_NAME                TABLE_NAME      STATUS   VISIBILIT AUT CON
------------------------- --------------- -------- --------- --- ---
SYS_AI_7mx8mdmfk2saq      DEMO009B        VALID    INVISIBLE YES NO
SYS_AI_3vq6p6haf4kc0      DEMO009C        UNUSABLE INVISIBLE YES NO
SYS_AI_9dj7yy4b1w2ss      DEMO010         VALID    VISIBLE   YES NO
 
ai_demo@PDB19> alter index "SYS_AI_7mx8mdmfk2saq" rebuild coalesce;
 
Index altered.
 
ai_demo@PDB19> alter index "SYS_AI_7mx8mdmfk2saq" rebuild shrink space;
 
Index altered.
 
Thankfully, there is now (introduced in Oracle 20c but backported to Oracle 19.5) an API option to easily drop AI when desired.
 
ai_demo@PDB19> exec dbms_auto_index.drop_auto_indexes(user,'"SYS_AI_7mx8mdmfk2saq"');
 
PL/SQL procedure successfully completed.
 
ai_demo@PDB19> select index_name,table_name,status,visibility,auto,constraint_index
  2  from user_indexes
  3  where auto ='YES';
 
INDEX_NAME                TABLE_NAME      STATUS   VISIBILIT AUT CON
------------------------- --------------- -------- --------- --- ---
SYS_AI_3vq6p6haf4kc0      DEMO009C        UNUSABLE INVISIBLE YES NO
SYS_AI_9dj7yy4b1w2ss      DEMO010         VALID    VISIBLE   YES NO
 
Similarly, to drop all AI in a specific schema – it should be like this
 
ai_demo@PDB19> exec dbms_auto_index.drop_auto_indexes(user,null);
 
PL/SQL procedure successfully completed.
 
ai_demo@PDB19> select index_name,table_name,status,visibility,auto,constraint_index
  2  from user_indexes
  3  where auto ='YES';
 
no rows selected