Search

Top 60 Oracle Blogs

Recent comments

Automatic Indexing

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

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

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

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

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

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

Oracle 19c Automatic Indexing: Non-Equality Predicates Part II (Let’s Spend The Night Together)

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

Oracle 19c Automatic Indexing: Non-Equality Predicates Part I (Lucy Can’t Dance)

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

Oracle 19c Automatic Indexing: Indexing Partitioned Tables Part II (Neighbourhood Threat)

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

Oracle 19c Automatic Indexing: Currently Broken In Autonomous ATP Databases Part II (Fix You)

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

Oracle 19c Automatic Indexing: Currently Broken In Autonomous ATP Databases? (What In The World)

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

Oracle 19c: Adventures with Automatic Indexing

The video of this recent presentation, given as a part of the Oracle Groundbreakers EMEA Tour 2020, is now available.

Automatic Indexing is one of the much-heralded features of Oracle 19c, but it is only available on Engineered Systems, therefore in Autonomous Database that is built on Exadata and on other Exadata platforms. This presentation shares some initial experiences with the feature based on testing it in conjunction with Swingbench and discusses how well it performed.

Oracle 19c Automatic Indexing: Indexing Partitioned Tables Part I (Conversation Piece)

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