Saturday, July 31, 2021
dbametrix
More
    HomeOracle DBA Tipsoracle 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

    Recent Comments