Richard Foote

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

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

Oracle 19c Automatic Indexing: The 3 Possible States Of Newly Created Automatic Indexes (“Don’t Sit Down”)

Tue, 2021-08-24 03:06
As I discussed way back in February 2021 (doesn’t time fly!!), I discussed some oddity cases in which Automatic Indexes were being created in an Invisible/Valid state. At the time, I described it as unexpected behaviour as this wasn’t documented and seemed an odd outcome, one which I had only expected to find when Automatic […]
Categories: DBA Blogs

Announcement: AUSOUG 1 Day Oracle Database AWR/Statspack Workshops with Richard Foote

Wed, 2021-05-19 23:42
It’s with much excitement that I announce a couple of in-person workshops I’ll be running in the coming months in conjunction with the Australian Oracle User Group (AUSOUG). The dates and locations are: Melbourne: Thursday 15th July 2021 Perth: Thursday, 12th August 2021 These will be 1 day workshops that discuss in detail when and […]
Categories: DBA Blogs

Presenting At The “Oracle Horizon Breakfast Series” Throughout May in Canberra

Mon, 2021-04-12 20:38
  If you’re lucky enough to be based close to sunny Canberra, Australia, please join me in cooperation with Oracle Corporation for the “Oracle Horizons Breakfast Series” I’m running throughout May 2021 at the Canberra Hyatt Hotel. Enjoy breakfast while I discuss some exciting Oracle Database related topics: 5 May  : Innovate with Oracle Database […]
Categories: DBA Blogs

METHOD_OPT Default In Oracle Autonomous Databases (She’ll Drive The Big Car)

Tue, 2021-03-02 00:18
  In a recent post on Invisible Automatic Indexes, I was puzzled by a couple of “oddities” in relation to some behaviour in the Oracle Autonomous Database Cloud environments. The first one was how Oracle appeared to be creating Histograms on a much more regular basis than it had previously. As one can see in […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Function-Based Indexes? Part II (If You Can See Me)

Thu, 2021-02-04 17:52
In my previous post, I discussed how Automatic Indexing does not currently support creating an index based on a function or expression predicate, even if it’s an equality predicate. You must manually create the associated function-based index. However, if you have access to the application, there’s a better strategy when frequently searching on a function-based […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Function-Based Indexes? (No Plan)

Thu, 2021-02-04 00:25
I previously discussed how Automatic Indexing only currently supports Equality based predicates. The question I have today is does Automatic Indexing support function-based indexes? Let’s take a look. The below DAVID table has the key column NAME which is an effectively unique VARCHAR2 column: If we look at the current details of the table columns: […]
Categories: DBA Blogs

Oracle Database 19c Automatic Indexing: Invisible Indexes Oddity (Wild Eyed Boy From Freecloud)

Tue, 2021-02-02 21:11
There have been a couple of “oddities” in relation to both Oracle Autonomous Databases and Automatic Indexing behaviour that I’ve seen frequently enough now (on Oracle 19.5.0.0.0) to make it worth a quick blog article. The following is a simple test case that highlights both these issues. I’ll begin with a basic table, that has […]
Categories: DBA Blogs

Oracle Database 19c Automatic Indexing: Index Compression Update (New Morning)

Tue, 2021-01-26 23:41
  I was reminded in a recent comment by Rajeshwaran Jeyabal that I hadn’t updated my post on Automatic Indexing with Advanced Compression that’s in need of a couple of amendments. Initially when Automatic Indexing was released, the ability to set Advanced Compression was NOT included in the official documentation, although the EXEC DBMS_AUTO_INDEX.CONFIGURE( ‘AUTO_INDEX_COMPRESSION‘ […]
Categories: DBA Blogs

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

Pages