Richard Foote

Subscribe to Richard Foote feed Richard Foote
Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music
Updated: 5 hours 35 min ago

Oracle 19c Automatic Indexing: Indexing Partitioned Tables Part I (Conversation Piece)

Tue, 2020-10-13 23:47
In this little series, I’m going to discuss how Automatic Indexing works in relation to Partitioning. I’ve discussed Indexing and Partitioning many times previously and how Oracle has various options when indexing a partitioned table: Non-Partitioned Index Globally Partitioned Index Locally Partitioned Index So the question(s) are how does Automatic Indexing handle scenarios with partitioned […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Indexing With Stale Statistics Part III (Do Anything You Say)

Wed, 2020-10-07 23:22
In Part I of this series, we saw how Automatic Indexing will not create a viable Automatic Index if there are stale or missing statistics on the underlining segments. In Part II we saw how these SQL statements effectively become blacklisted and when segment statistics are subsequently collected, Automatic Indexing will still not create viable […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Indexing With Stale Statistics Part II (Survive)

Wed, 2020-10-07 01:07
    In my previous post, I discussed how having stale statistics, usually a bad idea, is especially problematic with regard Automatic Indexes as it usually results in viable automatic indexes only being created in an UNUSABLE/INVISIBLE state. If we were to now to collect the missing statistics:   If we now repeatedly re-run the […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Indexing With Stale Statistics Part I (Dead Against It)

Mon, 2020-10-05 23:56
A “golden rule” when working with Automatic Indexing is that things don’t work properly if there are stale statistics on the dependant objects. Stale statistics can of course be problematic but they can be particularly troublesome when dealing with Automatic Indexing. In the Oracle Autonomous Database environments, this issue is addressed somewhat by the new […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Data Skew Fixed By Baselines Part II (Sound And Vision)

Mon, 2020-09-28 01:00
  In my previous post, I discussed how the Automatic Indexing task by using Dynamic Sampling Level=11 can correctly determine the correct query cardinality estimates and assume the CBO will likewise determine the correct cardinality estimate and NOT use an index if it would cause performance to regress. However, if other database sessions DON’T use […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Data Skew Fixed By Baselines Part I (The Prettiest Star))

Thu, 2020-09-24 20:53
In my previous few blog posts, I’ve been discussing some issues in relation to how Automatic Indexes handle SQL statements that accesses skewed data. In this post, I’m going to setup the scenario in which Automatic Indexing can potentially use Baselines to help address some of these issues. BUT, as we’ll see, I’m having to […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: CBO Incorrectly Using Auto Indexes Part II ( Sleepwalk)

Mon, 2020-09-21 02:39
As I discussed in Part I of this series, problems and inconsistencies can appear between what the Automatic Indexing processing thinks will happen with newly created Automatic Indexing and what actually happens in other database sessions. This is because the Automatic Indexing process session uses a much higher degree of Dynamic Sampling (Level=11) than other […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: CBO Incorrectly Using Auto Indexes Part I (Neighborhood Threat)

Fri, 2020-09-18 03:19
Following on from my previous few posts on “data skew”, I’m now going to look at it from a slightly different perspective, where there is an inherent relationship between columns. The CBO has difficulties in recognising (by default) that some combinations of column values are far more common than other combinations, resulting in incorrect cardinality […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Data Skew Part III (The Good Son)

Wed, 2020-09-16 04:05
  I’m going to expand just a tad on my previous posts on data skew and run a simple query that returns a few rows based on a column predicate AND another query on the same column that returns many rows. The following table has a CODE column as with previous posts with the data […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Data Skew Part II (Everything’s Alright)

Mon, 2020-09-14 00:05
In my previous post, I discussed an example with data skew, in which the Automatic Indexing process created a new index, but somehow the CBO when using the index estimated the correct cardinality estimate even though no histograms were explicitly calculated. In this post I’ll answer HOW this achieved by the CBO. Get some idea […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Data Skew Part I (A Saucerful of Secrets)

Thu, 2020-09-10 02:44
When it comes to Automatic Indexes, things can become particularly interesting when dealing with data skew (meaning that some columns values are much less common than other column values). The next series of blog posts will look at a number of different scenarios in relation to how Automatic Indexing works with data that is skewed […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: DDL Statements With Auto Indexes (No Control)

Mon, 2020-08-31 22:46
  I’ve had a number of questions in relation to DDL support for Automatic Indexes since my last post on how one can now drop Automatic Indexes, so decided to quickly discuss what DDL statements are supported with Automatic Indexes. Many DDL commands are NOT supported with Automatic Indexes, such as making indexes (IN)VISIBLE and […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Dropping Automatic Indexes Part II (New Angels of Promise)

Mon, 2020-08-24 22:09
Just a quick update on a previous post on dropping Automatic Indexes. As I discussed previously, one could drop Automatic Indexes by moving them to a new tablespace and then dropping the tablespace. This cumbersome technique was necessary because there was no direct facility to drop Automatic Indexes. Additionally, it’s worth noting this process isn’t […]
Categories: DBA Blogs

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

Tue, 2020-08-11 00:44
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 […]
Categories: DBA Blogs

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

Mon, 2020-08-10 02:24
  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 […]
Categories: DBA Blogs

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

Wed, 2020-08-05 22:39
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 […]
Categories: DBA Blogs

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

Tue, 2020-06-30 03:34
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 […]
Categories: DBA Blogs

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

Mon, 2020-06-15 23:53
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 […]
Categories: DBA Blogs

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

Mon, 2020-05-11 23:19
  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 […]
Categories: DBA Blogs

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

Wed, 2020-05-06 02:49
  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 […]
Categories: DBA Blogs

Pages