How to drop an index created by Oracle 19c Auto Indexing?

ORA-65532: cannot alter or drop automatically created indexes

Franck Pachot
Aug 13, 2019 · 5 min read

Oracle 19c Automatic Indexing is not like the autonomous features that happen without your control. You can decide to enable it (if you are on a platform that allows it) or not, and in report-only or implementation mode.

But when you have enabled it to create new indexes, you are not supposed to revert its effect. What if you want to drop those indexes?

DROP INDEX

If I want to drop an index that has been created automatically (i.e with the AUTO=’YES’ in DBA_INDEXES) I get the following error:

SQL> select owner,index_name,auto,tablespace_name from dba_indexes natural where auto='YES';

ALTER INDEX

I get the same error if I try to make it invisible (so that at least it is not used by the queries) or unusable (so that it is not maintained by the DML):

SQL> alter index ADMIN."SYS_AI_8u25mzzr6xw1v" invisible;

IND$.PROPERTY unsupported hack

In ?/rdbms/admin/cdcore_ind.sql the definition for DBA_INDEXES defines AUTO as:

decode(bitand(i.property, 8), 8, 'YES', 'NO'),
...
from ... sys.ind$ i ...

In ?/rdbms/admin/dcore.bsq the comment for this IND$ flag is probably wrong (probably an old flag being re-used for the Auto-Index feature):

property number not null,/* immutable flags for life of the index */
/* unique : 0x01 */
/* partitioned : 0x02 */
/* reverse : 0x04 */
/* compressed : 0x08 */
/* functional : 0x10 */

The comment is wrong but the important thing is that the AUTO attribute is defined as an immutable property rather than a flag that can be mutable.

This gives me a possibility to drop an index that has been created by the Auto Index feature, but totally unsupported, undocumented and probably very dangerous. Here is the OBJECT_ID:

SQL> select owner,index_name,object_id,auto,tablespace_name from dba_indexes natural left outer join (select owner index_owner,object_name index_name,object_id from dba_objects where object_type='INDEX') where auto='YES';

The property 0x8 is set:

SQL> select property from sys.ind$ where obj#=73191;

I un-flag it:

SQL> show user
show user
USER is "SYS"

Not anymore flagged as AUTO:

SQL> select owner,index_name,object_id,auto,tablespace_name from dba_indexes natural left outer join (select owner index_owner,object_name index_name,object_id from dba_objects where object_type='INDEX') where index_name like 'SYS_AI%';

And I can now drop it:

SQL> drop index ADMIN."SYS_AI_8u25mzzr6xw1v";

Again, this is totally unsupported: don’t do that!

SQL> select owner,index_name,object_id,auto,tablespace_name from dba_indexes natural left outer join (select owner index_owner,object_name index_name,object_id from dba_objects where object_type='INDEX') where index_name like 'SYS_AI%';

DROP TABLESPACE

In a more supported way, I can drop all AUTO indexes by dropping the tablespace where they reside. If I plan to do that, I’ve probably defined a specific tablespace for them (rather than the default tablespace for the user):

SQL> select parameter_name,parameter_value from dba_auto_index_config order by 1;

This just works to remove all indexes created there:

SQL> drop tablespace AITBS including contents;

MOVE and DROP

I may not want to drop all of them. What if I move one index into a new tablespace? I don’t want to actually rebuild it, unusable is ok for me:

SQL> alter index ADMIN."SYS_AI_26rdw45ph3hag" rebuild tablespace EPHEMERAL unusable;

Well, I don’t know how to do this without rebuilding it. So let’s do this:

SQL> create tablespace EPHEMERAL nologging;

This works, so not all ALTER INEX commands fail with an ORA-65532.

SQL> select owner,index_name,object_id,auto,tablespace_name from dba_indexes natural left outer join (select owner index_owner,object_name index_name,object_id from dba_objects where object_type='INDEX') where index_name like 'SYS_AI%';

And I can now drop this tablespace that contains only this index:

SQL> drop tablespace EPHEMERAL including contents;

Goal achieved, in a supported way:

SQL> select owner,index_name,object_id,auto,tablespace_name from dba_indexes natural left outer join (select owner index_owner,object_name index_name,object_id from dba_objects where object_type='INDEX') where index_name like 'SYS_AI%';

“_optimizer_use_auto_indexes”=OFF

Finally, if I don’t want to use the AUTO indexes, I don’t have to drop them. There’s a parameter to disable the use of them.

Here is a query using my AUTO index:

SQL> select count(*) from admin.words where sound='H400';
COUNT(*)
___________
152

Now, disabling all Auto Index at my session level:

SQL> alter session set "_optimizer_use_auto_indexes"=OFF;
Session altered.

Do you really want to drop them?

Note that if you drop them, then you probably also want to disable Auto Indexing at all or they will probably re-appear:

exec dbms_auto_index.configure('AUTO_INDEX_MODE','REPORT');

And there it is your decision to create the indexes or not.

But remember that in theory, the presence of an index should not have bad effects as the optimizer. With correct statistics, the CBO can decide to use it or not. And the Auto Indexing feature has also a way to blacklist some auto-created indexes from some queries if a regression has been encountered.

Franck Pachot

Written by

https://twitter.com/FranckPachot Passionate about all databases. Oak Table member, Oracle ACE Director & OCM 12c. Other blog posts: http://blog.dbi.pachot.net

Franck Pachot

Written by

https://twitter.com/FranckPachot Passionate about all databases. Oak Table member, Oracle ACE Director & OCM 12c. Other blog posts: http://blog.dbi.pachot.net

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch

Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore

Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store