Top 60 Oracle Blogs

Recent comments

Automatic Indexing

Oracle 19c Automatic Indexing: Common Index Creation Trap (Rat Trap)

When I go to a customer site to resolve performance issues, one of the most common issues I encounter is in relation to inefficient SQL. And one of the most common causes for inefficient SQL I encounter is because of deficiencies the default manner by which the index Clustering Factor is calculated. When it comes […]

Oracle 19c Automatic Indexing: A More Complex Example (How Does The Grass Grow)

In this post I’m going to put together in a slightly more complex SQL example a number of the concepts I’ve covered thus far in relation to the current implementation of Oracle Automatic Indexing. I’ll begin by creating three tables, a larger TABLE1 and two smaller TABLE2 and TABLE3 lookup tables. Each table is created […]

Oracle 19c Automatic Indexing: Dropping Automatic Indexes (Fall Dog Bombs The Moon)

  Julian Dontcheff recently wrote a nice article on the new Automatic Index Optimization feature available in the upcoming Oracle Database 20c release (I’ll of course blog about this new 20c feature in the near future). Within the article, Julian mentioned a clever method of how to effectively drop Automatic Indexes that I thought would […]

Oracle 19c Automatic Indexing: Mixing Manual and Automatic Indexes Part II (Stay)

  In my previous post, I discussed how Automatic Indexing did not recognise there was already an existing logically equivalent manually created index and so created effectively a redundant Automatic Index. I also discussed previously how Automatic Indexing was clever enough to logically add new columns to existing Automatic Indexes if it determined such a […]

Oracle 19c: Automatic Indexing. Part 2. Testing Automatic Indexing with Swingbench

This is the second of a two-part post that looks at the Automatic Indexing feature introduced in Oracle 19c.
I have used Dominic Giles' Swingbench utility to create a realistic and repeatable OLTP load test using the Sales Order Entry (SOE) benchmark.  This post explains how I set up and ran the test, and what results I obtained.

Oracle 19c: Automatic Indexing. Part 1. Introduction

This is the first of a two-part post that looks at the Automatic Indexing feature introduced in Oracle 19c, available on engineered systems only. Initially, I simply wanted to see what it does and to understand how it worked.
Next, I wanted to see how good it is. I created a test based on Dominic Giles' Swingbench Sales Order Entry benchmark. Having dropped the secondary indexes (ones not involved in key constraints), I wanted to see which Automatic Indexing would recreate and whether that would reinstate the original performance.

References and Acknowledgements 

This blog is not intended to provide a comprehensive description of Automatic Indexing.  I explain some things as I go along, but I have referenced the sources that I found helpful.

Oracle 19c Automatic Indexing: Mixing Manual and Automatic Indexes Part I (I Can’t Read)

In previous articles, I discussed how Automatic Indexing has the capability to add columns or reorder the column list of previously created Automatic Indexes. However, how does Automatic Indexing handle these types of scenarios with regard to existing manually created indexes? To investigate, let’s create a table identical to the table I created in my […]

Oracle 19c Automatic Indexing: Adding Columns To Existing Automatic Indexes (2+2=5)

  In my previous post, I discussed how when the following query is run: select * from major_tom3 where code3=4 and code2=42; the Automatic Indexing process will create an index on (CODE2, CODE3) but ultimately not use the index as the CBO considers the corresponding index based execution plan too expensive. I’m going to expand […]

Oracle 19c Automatic Indexing: Index Created But Not Actually Used (Because Your Young)

    The following is an interesting example of how Oracle Automatic Indexing is currently implemented that can result in an Automatic Index being created but ultimately ignored by the CBO. To illustrate, we begin by creating a simple little table that has two columns of particular interest, CODE2 which has 100 distinct values and […]

Oracle Database 19c Automatic Indexing: Minimum Number Of Required Indexes (Low)

  As I discussed in my previous posts, Oracle Automatic Indexing will try and create as few indexes as possible to satisfy existing workloads, even if that means reordering the columns in an existing index. To illustrate how Automatic Indexing creates as few indexes as possible, I’ll create the following table which has a number […]