3 ms·

I thought Oracle already has this feature - https://www.oracle.com/news/connect/oracle-database-automatic-indexing.html https://www.oracle.com/news/connect/orac

• KronisLV 4y ago
  It does, though are some restrictions in regards on which versions it can run on, unless you want to enable features that are not officially supported: https://oracle-base.com/articles/19c/automatic-indexing-19c https://oracle-base.com/articles/19c/automatic-indexing-19c My issue is that you cannot (easily) delete the automatically generated indices, should you want to do that for whatever reason. Of course, Oracle will try to only enable the indices that will lead to actual performance gains, but when it comes to removing the automatically created ones altogether, the best that you can do is: DBMS_AUTO_INDEX.CONFIGURE ('AUTO_INDEX_RETENTION_FOR_AUTO', '1'); Consider that you have a few different environments, all of the same database schema with a few hundred tables in it. You might want to take one of those environments (a development environment) and enable the feature there, have a bunch of indices be automatically created, take the DDL for those and turn them into migrations that can be run against other environments (any number of testing environments, eventually production) either manually or through some automated SQL migration solution. The problem is that you can't easily get rid of all the automatically generated ones so that you could test this migration against the same environment (basically create those same indices through your own SQL).