Richard Foote

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

Oracle 19c Automatic Indexing: Non-Equality Predicates Part II (Let’s Spend The Night Together)

Wed, 2021-01-20 21:42
In my previous post in this series, I discussed out Automatic Indexing currently does not consider Non-Equality predicates. Automatic Indexing will index columns based only on Equality predicates. So how does Oracle handle the scenario when an SQL has a mixture of both Equality and Non-Equality predicates? I’ll begin by creating two very similar tables, […]
Categories: DBA Blogs

Announcement: Both Of My Oracle Webinars Scheduled For February 2021 !!

Mon, 2021-01-18 19:56
I’m please to announce that both of my highly acclaimed webinars scheduled for February are now confirmed. These webinars are a must for any Oracle DBA or Developer interested in designing, developing or maintaining high performance, highly scalable Oracle-based applications or databases. However only a few places are currently available on each webinar with numbers […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Non-Equality Predicates Part I (Lucy Can’t Dance)

Thu, 2021-01-14 00:43
  I’ve been waiting a while before posting a series on the various limitations associated with Automatic Indexing, in order to see how the feature matures over time. The following have all been re-tested post 1 January 2021 on the Autonomous ATP Database Cloud service, using Oracle Database version 19.5.0.0.0. In the Oracle Documentation (including […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Indexing Partitioned Tables Part II (Neighbourhood Threat)

Wed, 2021-01-13 00:38
In my first post on Automatic Indexing on Partitioned Tables, I discussed how Automatic Indexing (AI) can now create a Non-Partitioned index if deemed the most effective indexing structure (this wasn’t supported when AI was initially released). A Non-Partitioned index is indeed likely the most efficient indexing structure if the underlying table has many partitions […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Currently Broken In Autonomous ATP Databases Part II (Fix You)

Mon, 2021-01-11 18:43
  Firstly, thank you for all those who contacted me regarding the issue with Oracle Automatic Indexing (AI) having stopped working within the Autonomous ATP Database Cloud service. It appears this issue was indeed widely spread and impacted numerous (if not all) Autonomous ATP Database Cloud services. This was all possibly due to (unpublished) bug […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Currently Broken In Autonomous ATP Databases? (What In The World)

Thu, 2020-12-17 00:24
  I’ve been playing with the free tier Oracle Autonomous Databases for quite some time, but unfortunately in recent times, I’ve hit a bit of a snag. The Automatic Index capability appears to be currently broken… The Automatic Indexing task appears to have stopped running, yes even with the AUTO_INDEX_MODE set to “IMPLEMENT” and with […]
Categories: DBA Blogs

Announcement: New Oracle Webinars Scheduled For February 2021 !!

Sun, 2020-11-22 22: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 […]
Categories: DBA Blogs

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

Pages