Oracle 19c autonomous database new Feature – Automatic Indexing
This is definitely the best and one of the next generation elements I’ve even seen in Oracle database. Once this type of automation is put into operational and discharged, it will open doors to many other invention computerizations (like table reorganization, table encryption or whatever thing you can visualize).
The automatic indexing tactic will be relayed on a universal move toward to physical SQL Tuning. Oracle will detain the SQL statements, recognize the aspirant indexes and apprises the ones that will advantage those testimonials. The whole process is not something effortless.
Essentially, Oracle will first generate those indexes as unusable and invisible (metadata only). Then, outside the appliance workflow, oracle will inquire the optimizer to test if those candidate indexes get better the SQL performance. In case the performance is improved for all statements when indexed are utilized, it will turn into visible. If performance is not as good as, it continues invisible. And if it only carry outs better for some statements, the index is only spotted visible for those SQLs (via SQL Patch, maybe).
The automation will also plunge the indexes that become outdated by the newly created indexes (logical merge) and also eliminate the indexes that were created automatically but have not been used in a long time. The whole thing is customizable using following commands.
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');
Consider Reading to these articles:
