Richard Foote

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

Merry Christmas and Happy New Year!! (“The Jean Genie”)

Wed, 2022-12-21 21:34
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 […]
Categories: DBA Blogs

When Does A ROWID Change? Part IV (“Mass Production”)

Tue, 2022-12-20 19:59
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 […]
Categories: DBA Blogs

When Does A ROWID Change? Part III (“Arriving Somewhere But Not Here”)

Mon, 2022-12-12 23:14
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 […]
Categories: DBA Blogs

When Does A ROWID Change? Part II (“You’ve Got A Habit Of Leaving”)

Thu, 2022-12-08 18:34
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 […]
Categories: DBA Blogs

When Does A ROWID Change? Part I (“Fearless”)

Tue, 2022-12-06 19:54
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 […]
Categories: DBA Blogs

Automatic Indexing: Potential Locking Issues Part II (“Don’t Stop”)

Sun, 2022-12-04 20:25
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 […]
Categories: DBA Blogs

Automatic Indexing: Potential Locking Issues Part I (“Rattle That Lock”)

Wed, 2022-11-30 20:30
  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 […]
Categories: DBA Blogs

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

Pages