Oracle Indexes

Richard Foote's picture

Oracle 19c Automatic Indexing: Adding Columns To Existing Automatic Indexes (2+2=5)

  In my previous post, I discussed how when the following query is run: select * from major_tom3 where code3=4 and code2=42; the Automatic Indexing process will create an index on (CODE2, CODE3) but ultimately not use the index as the CBO considers the corresponding index based execution plan too expensive. I’m going to expand […]

Richard Foote's picture

Oracle 19c Automatic Indexing: Index Created But Not Actually Used (Because Your Young)

    The following is an interesting example of how Oracle Automatic Indexing is currently implemented that can result in an Automatic Index being created but ultimately ignored by the CBO. To illustrate, we begin by creating a simple little table that has two columns of particular interest, CODE2 which has 100 distinct values and […]

Richard Foote's picture

Oracle Database 19c Automatic Indexing: Minimum Number Of Required Indexes (Low)

  As I discussed in my previous posts, Oracle Automatic Indexing will try and create as few indexes as possible to satisfy existing workloads, even if that means reordering the columns in an existing index. To illustrate how Automatic Indexing creates as few indexes as possible, I’ll create the following table which has a number […]

Richard Foote's picture

Oracle Database 19c Automatic Indexing – Need Another Index (Another Brick in The Wall Part 2)

I previously discussed how Automatic Indexing can effectively cleverly reorder an existing index if it means it can now use the new index to satisfy new SQL predicates. In this post, we’ll explore this example further with some new workloads. So, we previously ran SQL queries with SQL predicates in the following combinations: CODE1=42 and […]

Richard Foote's picture

Merry Christmas and Happy New Year !! (“Heroes”)

I would like to take this opportunity to wish all my readers a very Merry Christmas and a most happy, peaceful and prosperous New Year. My gift this year is not David Bowie and Bing Crosby doing their famous Christmas duet but a performance by Bowie of his classic “Heroes” as featured on the same […]

Richard Foote's picture

London March 2020: “Oracle Indexing Internals and Best Practices” and “Oracle Performance Diagnostics and Tuning” Seminars !!

It’s with great excitement that I announce I’ll finally be returning to London, UK in March 2020 to run both of my highly acclaimed seminars. The dates and registration links are as follows: 23-24 March 2020: “Oracle Indexing Internals and Best Practices” seminar – Tickets and Registration Link 25-26 March 2020: “Oracle Performance Diagnostics and […]

Richard Foote's picture

Oracle Database 19c Automatic Indexing: Default Index Column Order Part I (Anyway Anyhow Anywhere)

The next thing I was curious about regarding Automatic Indexing was in which order would Oracle by default order the columns within an index. This can be a crucial decision with respect to the effectiveness of the index (but then again, may not be so crucial as well). Certainly one would expect the index column […]

Richard Foote's picture

Oracle 19c Automatic Indexing: How Many Executions Does It Take? (One Shot)

One of the first questions I asked when playing with the new Oracle Database 19c Automatic Indexing feature was how many executions of an SQL does it take for a new index to be considered? To find out, I create the following table: I then ran the following query just once and checked to see […]

Richard Foote's picture

Speaking at Trivadis Performance Days 2019

I’ll again be speaking at the wonderful Trivadis Performance Days 2019 conference in Zurich, Switzerland on 26-27 September. There’s again another fantastic lineup of speakers, including: CHRISTIAN ANTOGNINI IVICA ARSOV MARK ASHDOWN SHASANK CHAVAN EMILIANO FUSAGLIA STEPHAN KÖHLER JONATHAN LEWIS FRANCK PACHOT TANEL PODER DANI SCHNIDER   I’ll be presenting two papers: “Oracle 18c and […]

Richard Foote's picture

Speaking at Oracle OpenWorld 2019

It’s been remarkably 9 years since I’ve been to Oracle OpenWorld, but will finally get the opportunity to present there again this year (with many thanks to the Oracle ACE Director program for making this possible). Details of my presentation are as follows: Conference: Oracle OpenWorld Session Type: Conference Session Session ID: CON1432 Session Title: […]

To prevent automated spam submissions leave this field empty.
Syndicate content