Richard Foote

I would like to take this opportunity to wish all my readers who celebrate the festive season a very Merry Christmas and a most happy, peaceful and prosperous New Year. 2023 promises to be a big year for me, in that I’ll be officially retiring from work early in the year, after close to 40 […]

In Part II in this series, I discussed how the update of the partitioned key column of a row that results in the row being moved to a different partition, will result in the ROWID of such rows changing. However, there a quite a number of other user initiated actions in which ROWIDs can easily […]

In Part II of this series, I discussed how updating the Partitioned Key of a row from a Partitioned table will result in the row physically moving and the associated ROWID changing. One of the reasons why changing the ROWID has historically has not been the default behaviour and requires the explicit setting of the […]

In my previous post, I discussed how a row is generally “migrated”, but the ROWID remains unchanged, when a row is updated such that it can no longer fit into its current block. Hence the general rule has always been that the ROWID of a row does not change. However, even before the changes now […]

Recently, my mate Connor McDonald caused a tad of a storm when he disclosed that the once sacred, (almost) unchangeable ROWID can now indeed potentially easily change, without the DBA doing a thing. You can watch his excellent video on the subject here. As the humble ROWID is a critical component of any index, I […]

In my previous post, I highlighted how a long transaction can potentially cause the creation of an Automatic Index to hang due to the inability of the Automatic Indexing process to obtain the necessary locks. However, these locks can have a much wider consequence, as it’s the entire Automatic Indexing process that is forced to […]

  I’ve discussed previously locking issues associated with the creation of indexes. Although things have changed and improved over the years, even with the ONLINE option currently, an index creation process still requires (albeit brief and non-escalating) locks on the underlining table. Basically, there needs to be a brief period where there isn’t an active […]

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

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

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

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

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

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

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

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

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

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

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

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

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