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.trackback
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.
Richard Foote's Oracle Blog 
[…] of questions in relation to DDL support for Automatic Indexes since my last post on how one can now drop Automatic Indexes, so decided to quickly discuss what DDL statements are supported with Automatic […]
LikeLike
Richard,
Does this auto indexing only support B*Tree index, how about Bitmap’s and bitmap join index, and search index for JSON’s ? does those auto created as well based on the workload ?
LikeLiked by 1 person
Yes, it only currently supports basic B*Tree indexes and none of those listed. It does though also support Local Partitioned indexes on partitioned tables. I blog about all this in the not too distant future.
LikeLike