About Me Contact Me Oracle12cSIG(IOUG)
Home  :: Oracle12c

Automatic Indexing


Automatic Indexing


As an Oracle DBA we have been continually working on making things fast in Oracle database. One of the key tasks in performance tuning is introducing new indexes.

As a DBA/Developer you propose indexes to your production system when you are assured that this this the best option as introducing index is two-way sward. On positive side it enhances data access but on negative side it can slow your inserts, add up time to stats gathering job, and adds up to database size.

Oracle database 19c introduced Automatic Indexing using database package DBMS_AUTO_INDEX. Currently this feature is only available in Exadata’s and Oracle cloud. In this article , I will explain you how it works. I will use a little hack   in my test database running on OEL 7 to act as Exadata. Please note this is for testing purpose only. Please Do not do it in your live environment



Automatic Indexing – How It Works (Concepts)


  • Periodically capture the application SQL history into a SQL repository

  • Includes SQL, plans, bind values, execution statistics, etc.


Identify candidate:

  • Identify candidate indexes that may benefit the newly captured SQL statements

  • Creates index candidates as unusable, invisible indexes (metadata only)

  • Drop indexes obsoleted by newly created indexes (logical merge)



  • Ask the optimizer if index candidates will be used for captured SQL statements

  • Materialize indexes and run SQL to validate that the indexes improve their performance

  • All verification is done outside application workflow



  • If performance is better for all statements, the indexes are marked visible

  • If performance is worse for all statements, the indexes remain invisible

  • If performance is worse for some, the indexes are marked visible except for the SQL statements that regressed


    Online Validation:

  • The validation of the new indexes continues for other statements, online

  • Only one of the sessions executing a SQL statement is allowed to use the new indexes



  • Index usage is continuously monitored

  • Automatically created indexes that have not been used in a long time will be dropped




Now let’s See it in action

Coming soon…



Ref: https://static.rainfocus.com/oracle/oow18/sess/1523250557343001OBEw/PF/TRN3980_Test_Drive_Auto_Index_Creation_in_ADB_1541192406753001UYd0.pdf



Posted on : 5/31/2020 by Anuj
No comment found.

Add Your Comments
Name :
Email :
Comments :


  Recent Posts

How to find correct Golden Gate version?
Posted on :   2/28/2021

Automatic Indexing
Posted on :   5/31/2020

Database 19c New Features
Posted on :   5/28/2020

  Recent Comments

Home  |  About Me  |  Contact  |  Oracle12cSIG(IOUG)

All Rights Reserved © 2019 Oracle-12c