Search

Top 60 Oracle Blogs

Recent comments

Oracle Indexes

Oracle 19c Automatic Indexing: Poor Data Clustering With Autonomous Databases Part III (Star)

In Part I we looked at a scenario where an index was deemed to be too inefficient for Automatic Indexing to create a VALID index, because of the poor clustering of data within the table. In Part II we improved the data clustering but the previous SQLs could still not generate a new Automatic Index […]

Oracle 19c Automatic Indexing: Poor Data Clustering With Autonomous Databases Part II (Wild Is The Wind)

  In my previous post, I discussed a scenario in which Oracle Automatic Indexing refused to create a VALID index, because the resultant index was too inefficient to access the necessary rows due to the poor clustering of data within the table. If the performance of such an SQL were critical for business requirements, there […]

Oracle 19c Automatic Indexing: Poor Data Clustering With Autonomous Databases Part I (Don’t Look Down)

I’ve discussed many times the importance of data clustering in relation to the efficiency of indexes. With respect to the efficiency of Automatic Indexes including their usage within Oracle’s Autonomous Database environments, data clustering is just as important. The following demo was run on an Oracle 19c database within the Oracle Autonomous Database Transaction Processing […]

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: 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: 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 […]