Richard Foote

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

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

Automatic Indexing: 3 Possible States Of Newly Created Automatic Indexes Part II (“Because You’re Young”)

Mon, 2021-12-20 01:12
In a previous post, I discussed how there can be three different states of newly created Automatic Indexes, based on the predicate selectivities of the SQLs that generated the indexes. In this post, I’m going to highlight new behaviour I’ve noticed with Oracle 21c that results in the three different states of newly created Automatic […]
Categories: DBA Blogs

Oracle Groundbreakers APAC Virtual Tour 2021: “Automatic Indexing: An Update On Improvements and New Capabilities”

Thu, 2021-11-18 17:43
I’m very excited to have another opportunity to present at the upcoming APACOUC Oracle Groundbreakers APAC Virtual Tour 2021 a somewhat expanded version of my new presentation “Automatic Indexing: An Update On Improvements and New Capabilities”. This massive virtual conference runs from 22 November through to 11 December and features some of the very best […]
Categories: DBA Blogs

AUSOUG Connect 2021- “Automatic Indexing: An Update On Improvements and New Capabilities”

Tue, 2021-10-26 02:39
  I’ve very pleased that my paper “Automatic Indexing: An Update On Improvements and New Capabilities” has been accepted for the upcoming AUSOUG Connect 2021 Virtual Conference. The conference runs between 9th – 12th November 2021 and features a host of great topics and speakers including Connor McDonald, Chris Saxon, Jim Czuprynski, Sandesh Rao, Karen […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Invisible/Valid Automatic Indexes (Bowie Rare)

Tue, 2021-08-31 03:46
In my previous post, I discussed how newly created Automatic Indexes can have one of three statuses, depending the selectivity and effectiveness of the associated Automatic Index. Indexes that improve performance sufficiently are created as Visible/Valid indexes and can be subsequently considered by the CBO. Indexes that are woeful and have no chance of improving […]
Categories: DBA Blogs

Pages