Richard Foote's Blog

Subscribe to Richard Foote's Blog feed Richard Foote's Blog
Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music
Updated: 2 days 17 hours ago

Announcement: New Oracle Webinars Scheduled For February 2021 !!

Mon, 11/23/2020 - 04:46
  After much badgering from a number of you (you know who you all are), I’m pleased to finally announce the scheduling of 2 new webinars for February 2021 !! As usual, places are very strictly limited as I only run small classes to give every attendee the opportunity to get the most from the […]

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

Wed, 10/14/2020 - 04: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 […]

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

Thu, 10/08/2020 - 04: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 […]

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

Wed, 10/07/2020 - 06: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 […]

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

Tue, 10/06/2020 - 04: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 […]

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

Mon, 09/28/2020 - 06: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 […]

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

Fri, 09/25/2020 - 01: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 […]

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

Mon, 09/21/2020 - 07: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 […]

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

Fri, 09/18/2020 - 08: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 […]

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

Wed, 09/16/2020 - 09: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 […]

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

Mon, 09/14/2020 - 05: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 […]

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

Thu, 09/10/2020 - 07: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 […]

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

Tue, 09/01/2020 - 03: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 […]

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

Tue, 08/25/2020 - 03: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 […]

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

Tue, 08/11/2020 - 05: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 […]

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

Mon, 08/10/2020 - 07: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 […]