jump to navigation

E4 2012 Enkitec Extreme Exadata Expo (Blue Jean) June 14, 2012

Posted by Richard Foote in Advert, Exadata, Oracle Indexes.
6 comments

I’m very pleased to have been invited to speak at the E4 2012 Enkitec Exadata Expo to be held in Dallas, USA on 13-14 August.

It’s basically the first ever conference that’s dedicated exclusively to Oracle’s exciting Exadata platform. It should be a fabulous event, featuring some of the best Oracle talent going around, including Jonathan Lewis, Tanel Poder, Kerry Osbourne, Cary Millsap, Arup Nanda, Fritz Hoogland, Doug Burns, Karen Morton, Maria Colgan and Peter Bach to name but a few. The full list of speakers is listed here.

My talk will naturally be on “Indexing In Exadata“, the abstract being:

“There’s often confusion regarding how indexing requirements may change when moving to Exadata, with some even suggesting that indexes are perhaps no longer required at all. Considering indexes can consume a considerable proportion of total storage within a database and can be crucial to general database performance, care needs to be taken to fully consider indexing requirements when moving to Exadata. This presentation will discuss the indexing structures unique to Exadata, how indexing considerations change (and don’t change), how database usage is critical to indexing requirements and how to implement safely an appropriate indexing strategy when migrating to Exadata that will ensure indexes get used when appropriate without compromising Exadata specific features such as Smart Scans and Storage Indexes.”

I’ve had the opportunity to research and have a good play with Exadata since re-joining the Oracle mothership so I’m looking forward to sharing some of what I’ve learnt.

Hopefully, you can make this historic event. If not in person, you might still be able to attend the event in a virtual capacity.

I’m hoping to confirm my attendance at another conference in the very near future. More details soon.

And yes, I’m planning to blog at some point about the challenges of having an appropriate indexing strategy on Exadata, busting a few myths in the making while I’m at it 🙂

Indexes vs. Full Table Scan: Picture vs. 1000 Words (Pictures Of Lily) June 8, 2012

Posted by Richard Foote in CBO, Clustering Factor, Oracle Indexes.
19 comments

I’m in the process of writing a number of new presentations and in one I’ve included a favorite little graph of mine that I’ve used over the years to help illustrate the relationship between the cost of using an index vs. the cost of using a Full Table Scan (FTS). It’s occurred to me that I’ve never actually shared this graph on this blog, so I thought it about time I did.

The Cost Based Optimizer (CBO) when choosing between an index scan and a FTS will simply go for the cheapest option. The more rows that are retrieved (or the greater the percentage of rows retrieved), the more expensive the index option as it needs to perform more logical I/Os. There will generally be a point when the selectivity of  a query is such, that so many rows are retrieved, that the index costs will increase beyond those of the FTS and the FTS becomes the cheaper option.

The cost of a FTS meanwhile is pretty well constant regardless of  the number of rows retrieved. It needs to read all the blocks in the table, whatever the selectivity of the query.

Although I’ve not quite reached 1000 words, the below graph illustrates this point:

The red line represents the constant cost of the FTS. The green lines represents the cost of using various indexes, which increases as more rows are retrieved. The “steepness” of the green line and the subsequent increase in cost of the index as more rows are retrieved is due entirely to the Clustering Factor of the index. The steeper the line, the worse (higher) the Clustering Factor, the less efficient the index and the quicker we get to the point when the FTS becomes cheaper. The less steep the line, the better (lower) the Clustering Factor, the more efficient the index and the longer it takes for the FTS to become the cheaper option.

In some rarer cases, the index might be so efficient (or the FTS so inefficient) that the index never reaches the point of the FTS and the CBO decides it’s overall cheaper for the index to potentially access 100% of all rows in a table rather than via a FTS.

Ok, so now you have almost 1000 words and the picture 🙂