Monday, November 30, 2020
dbametrix
More
    Home Oracle DBA Tips oracle 19c new features - Automatic Indexing

    oracle 19c new features – Automatic Indexing

    A major Oracle 19c autonomous database new Feature automatic indexing using DBMS_AUTO_INDEX some details and how to article.

    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).

    - Advertisement -
    dbametrix

    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');

    - Advertisement -
    dbametrix

    EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');

    EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

    Consider Reading to these articles:

    - Advertisement -
    dbametrix
    - Advertisment -
    dbametrix

    Most Popular

    Oracle Scheduler Jobs Example

    Blog post explains how to configure job scheduler in Oracle by example with how to manage, delete, drop, disable and enable jobs with how to check history of job if broken and enable.

    Identifying important information in Big Data

    The way in which Big Data technologies have evolved in the real word enterprise goes on to show that even technologists and scientists who might have disparaged the word (Big Data) previously

    Five Reasons Why Database Outsourcing

    The blog post explains why database outsourcing and remote dba support becomes most popular during present time of Covid-19 Pandemic.

    Hardware of Database Server

    The blog post explains how to choose best hardware of your database server for getting high performance and security.

    Recent Comments