jump to navigation

Oracle 19c Automatic Indexing: Dropping Automatic Indexes Part II (New Angels of Promise) August 25, 2020

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Drop Automatic Indexing, Drop Index, DROP_AUTO_INDEXES.

Just a quick update on a previous post on dropping Automatic Indexes.

As I discussed previously, one could drop Automatic Indexes by moving them to a new tablespace and then dropping the tablespace. This cumbersome technique was necessary because there was no direct facility to drop Automatic Indexes. Additionally, it’s worth noting this process isn’t viable in Autonomous Databases Cloud environments as it’s not possible to create/drop tablespaces.

As I’ve also discussed previously, there may be scenarios when you may want to drop an Automatic Index, such as when newly created Automatic Indexes don’t get used by the CBO in a manner as predicted by the Automatic Indexing process.

Thankfully, there is now (since Oracle Database 20c and back-ported to 19.5) an API option to easily drop Automatic Indexes when desired:

SQL> exec DBMS_AUTO_INDEX.DROP_AUTO_INDEXES(owner=>’BOWIE’, index_name=>'”SYS_AI_600vgjmtqsgv3″‘, allow_recreate=>true);

PL/SQL procedure successfully completed.


Note: the index name must be further enclosed in a ” “ due to the mixed-case naming convention of Automatic Indexes.

The last parameter controls whether or not you want to allow the Automatic Indexing process to subsequently potentially recreate the index again.  The default is false.

As I’ve mentioned a number of times in this series, Automatic Indexing is still a relatively new feature that will only improve and expand in future versions…


A big thanks to Nigel Bayliss for the heads-up on this new Auto Indexing feature.