Richard Foote

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

Costing Concatenated Indexes With Range Scan Predicates Part II (Coming Back To Life)

Tue, 2022-07-26 23:22
In my previous Part I post, I discussed how the CBO basically stops the index leaf block access calculations after a non-equality predicate. This means that for an index with the leading indexed column being accessed via an unselective non-equality predicate, a large percentage of the index’s leaf blocks might need to be scanned, making […]
Categories: DBA Blogs

Costing Concatenated Indexes With Range Scan Predicates Part I (Nothing To Be Desired)

Thu, 2022-07-21 18:43
In my previous post, I discussed how Automatic Indexing ordered columns when derived from SQLs containing both equality and non-equality predicates. I’ve since had offline questions asking why indexes are more effective with leading columns addressing the equality predicates rather than the leading columns addressing non-equality predicates. Based on the theory that for everyone who […]
Categories: DBA Blogs

Automatic Indexing 21c: Non-Equality Predicate Anomaly (“Strangers When We Meet”)

Thu, 2022-07-14 06:24
I’m currently putting together some Exadata related training for a couple of customers and came across a rather strange anomaly with regard the status of Automatic Indexes, when created in part on unselective, non-equality predicates. As discussed previously, Oracle Database 21c now allows the creation of Automatic Indexes based on non-equality predicates (previously, Automatic Indexes […]
Categories: DBA Blogs

Upcoming Webinar Series Now Sold Out!! (“White Light White Heat”)

Tue, 2022-07-12 23:27
My upcoming webinars now have the maximum number of attendees I’m comfortable in having in one session and are now officially sold out!! Taking my inspiration from David Bowie’s famous 1973 farewell speech, not only is this the last webinar series of the year, but it’s likely the last webinar series I’ll ever do. A […]
Categories: DBA Blogs

Automatic Indexes: Automatically Rebuild Unusable Indexes Part IV (“Nothing Has Changed”)

Mon, 2022-05-30 23:11
In a previous post, I discussed how Automatic Indexing (AI) does not automatically rebuild a manually built index that is in an Unusable state (but will rebuild an Unusable automatically created index). The demo I used was a simple one, based on manually created indexes referencing a non-partitioned table. In this post, I’m going to […]
Categories: DBA Blogs

Announcement: Registration Links For Upcoming Webinars Now Open (“Join The Gang”)

Wed, 2022-05-25 01:58
The registration links for my upcoming webinars running in August are now open!!! The price of each webinar is $1,600 AUD. There is a special price of $2,750 AUD if you wish to attend both webinars (just use the Special Combo Price button). (Note: Do NOT use the links if you’re an Australian resident. Please […]
Categories: DBA Blogs

Announcement: Dates Confirmed For Upcoming Webinars (“Here Today, Gone Tomorrow”)

Thu, 2022-05-19 01:53
As promised last week, I have now finalised the dates for my upcoming webinars. They will be run as follows: “Oracle Indexing Internals“ Webinar: 18-22 July 2022 (between 09:00 GMT and 13:00 GMT daily) “Oracle Performance Diagnostics and Tuning“ Webinar: 8-11 August 2022 (between 09:00 GMT and 13:00 GMT daily) I’ll detail costings and how […]
Categories: DBA Blogs

Automatic Indexes: Automatically Rebuild Unusable Indexes Part III (“Waiting For The Man”)

Tue, 2022-05-17 01:43
I’ve previously discussed how Automatic Indexing (AI) will not only create missing indexes, but will also rebuild unusable indexes, be it a Global or Local index. However, all my previous examples have been with Automatic Indexes. How does AI handle unusable indexes in which the indexes were manually created? In my first demo, I’ll start […]
Categories: DBA Blogs

Announcement: New (And Likely Final) Dates For My Webinars Finalised Next Week !!

Thu, 2022-05-12 02:15
  It’s been one hell of a hectic year!! For all those of you who have been patiently hanging on for the next series of my webinars, I finally, at long last, have some good news. I’m currently just finalising my calendar for the upcoming months, but I shall announce the next running of my […]
Categories: DBA Blogs

Automatic Indexes: Automatically Rebuild Unusable Indexes Part II (“I Wish You Would”)

Wed, 2022-05-11 01:54
Within a few hours of publishing my last blog piece on how Automatic Indexing (AI) can automatically rebuild indexes that have been placed in an UNUSABLE state, I was asked by a couple of readers a similar question: “Does this also work if just a single partition of an partitioned index becomes unusable”? My answer […]
Categories: DBA Blogs

Automatic Indexes: Automatically Rebuild Unusable Indexes Part I (“Andy Warhol”)

Mon, 2022-05-09 22:55
Obviously, the main feature of Automatic Indexing (AI) is for Oracle to automatically create indexes, that have been proven to improve performance, in a relatively safe and timely manner. However, another nice and useful capability is for AI to automatically rebuild indexes that are placed in an “Unusable” state. The documentation states that: “Automatic indexing […]
Categories: DBA Blogs

Automatic Indexes: AUTO_INDEX_TABLE Configuration (“Without You”)

Tue, 2022-05-03 01:51
One of the more common questions I get regarding Automatic Indexing (AI) are areas of concern around having large and expensive automatic index build operations suddenly occurring in one’s database and the impact this may have on overall performance. Additionally, I’ve had questions around scenarios where very large automatic indexes are suddenly being built, but […]
Categories: DBA Blogs

Automatic Indexes: Scenarios Where Automatic Indexes NOT Created Part III (“Loaded”)

Thu, 2022-04-28 01:58
In my previous two posts, I’ve discussed scenarios where Automatic Indexing (AI) does not currently created automatic indexes and you may need to manually create the necessary indexes. In this post, I’ll discuss a third scenario where AI will create an index, but you may want to manually create an even better one… I’ll start […]
Categories: DBA Blogs

Automatic Indexes: Scenarios Where Automatic Indexes NOT Created Part II (“Ragazzo Solo, Ragazza Sola”

Wed, 2022-04-27 02:20
In my last post, I discussed how Automatic Indexing doesn’t create an automatic index in the scenario where the minimum or maximum of a column is required. Another scenario when an automatic index is not created is when we hit issues associated with a missing index on a Foreign Key (FK) constraint. As I’ve discussed […]
Categories: DBA Blogs

Automatic Indexes: Scenarios Where Automatic Indexes NOT Created Part I (“Always Crashing In The Same Car”)

Tue, 2022-04-26 01:39
As I’ve discussed previously, Oracle has increased the number of scenarios in which it will now create automatic indexes, such as with non-equality predicates and JSON expressions. However, as of Oracle Database 21c, there are still a number of scenarios where an automatic index will NOT be created, even though an index might prove beneficial. […]
Categories: DBA Blogs

Automatic Indexing: Deferred Invalidations (“The Post War Dream”)

Tue, 2022-04-19 01:16
In my previous post on how JSON expressions can now be automatically indexed, I mentioned there was an outstanding issue with the associated CBO execution plan, immediately post the creation of the automatic index: If we look at the number of recursive calls, we notice that it remains at 0. If we look at both […]
Categories: DBA Blogs

Automatic Indexing: JSON Expressions Part II (“Without You I’m Nothing)”

Wed, 2022-04-13 21:18
In my previous post on Automatic Indexing (AI) and JSON expressions, I mentioned two additional issues with the execution plan generated after the automatic indexes were created: The first issue is in relation to the Cost and the estimated cardinality (Rows) of the generated plan. The execution plan has a Cost of 1524 but most […]
Categories: DBA Blogs

Automatic Indexing: JSON Expressions Part I (Making Plans For Nigel)

Tue, 2022-04-12 22:59
When Automatic Indexing was first released, one of the restrictions was that automatic indexes on JSON expressions were NOT supported. However, the Oracle Database 21c doco mentions: “Automatic indexes can be single or multi-column. They are considered for the following: Selected expressions (for example, JSON expressions)“. So on my (admittedly dodgy) “Exadata” VM, I thought […]
Categories: DBA Blogs

Merry Christmas and a Happy, Covid-Free New Year!! (“The Little Drummer Boy/Peace On Earth”)

Thu, 2021-12-23 18:16
Well, 2021 was a bit of a struggle wasn’t it !! I’m hopeful that 2022 might just be a tad better (but I’m not overly confident to be honest). I would like to take this opportunity to wish all my readers and Oracle friends a very Merry Christmas and a most happy, peaceful, prosperous and […]
Categories: DBA Blogs

Automatic Indexing: Non-Equality Predicates Part III (“Who Can I Be Now”)

Mon, 2021-12-20 19:04
In previous posts, I discussed how Automatic Indexing supported the creation of indexes based on equality predicates only. One of the most significant improvements with Automatic Indexing introduced with Oracle Database 21c is that non-equality predicates are now supported. Previously, if I created the following 10 million row table and ran an SQL query based […]
Categories: DBA Blogs