Oracle Magazine, July/August 2019
Emerging Technologies AUTONOMOUS The system ignores autocreated indexes for any SQL statements run for the first time This prevents evaluation of one off SQL that is never issued again and wouldnt have been able to benefit from the indexes anyway DBAs can disable the autoindex job for specific periods of time so as not to affect normal processing Using the Resource Manager feature of Oracle Database DBAs can limit the job to a limited number of CPUs to reduce any negative effect of the autoindex jobs If the autoindex job is not completed by a certain time the next run will be skipped This prevents proliferation of runaway jobs The indexes created automatically are deleted after a specific number of days which defaults to 373 But Betty can set the retention period of the unused autoindexes ORACLE MAGAZINE JULY AUGUST 2019 133 to say 100 days DBMS_ AUTO_ INDEX CONFIGURE AUTO_ INDEX_ RETENTION_ FOR_ AUTO 100 However the unused manually created indexes are never deleted by the automatic indexing process They can be deleted automatically if needed but never by default Betty can set another property AUTO_ INDEX_ RETENTION_ FOR_ MANUAL to specify after how many days the unused manual indexes can be dropped This seems to allay everyones concern that something could go terribly out of control In conclusion John points out the big difference between the advisors available earlier and the Automatic Indexing feature in Oracle Database 19c The advisors identify the need for and suggest possible indexes but the onus of deciding whether those indexes will help or not lies with the DBAs Automatic Indexing takes that responsibility away it implements needed indexes automatically and constantly
You must have JavaScript enabled to view digital editions.