Today I was part of a NYOUG seminar about “Indexing Strategies” held by Mr. Jonathan Lewis himself. If somebody doesn’t know Jon Lewis, he is one of the top Oracle gurus on the entire planet with more than 25 years of Oracle experience. He probably used Oracle before I was even born (1985)! The very interesting fact however is that he actually never ever worked for Oracle but still has all the deep inside knowledge. Usually those gurus came out from Oracle or are still working for them. I guess the great amount of years dealing with Oracle helped him there.
However, Jonathan Lewis is also one of my top favorite because he shares his knowledge on his personal blog: http://jonathanlewis.wordpress.com In fact, Jons blog is the only one which I never can catch up with because he posts literally quicker than I got time to read his fabulous posts… reminds me that I should update my Blogroll.
The seminar itself was simply great. It was not free but those 230 bucks where totally worth it. Not only is Jon Lewis a great speaker – was the first time that I actually saw him – he also gave great detailed insights into indexes how they work, what is all possible with them and why Oracle is actually taking an index and much more important when it is not! . Furthermore he combined his sessions with some real world examples that he himself dealt with in the past which makes the whole thing much less theoretical.
Of course I won’t cover the whole seminar here, but I’ll end with some interesting things that I took out of it – some of which I got reminded again, some which were new for me:
- Indexing means: Getting to data quickly
- Indexes focus/compact data
- There is always a Trade-Off between loading and querying performance
- Index maintenance is expensive – it introduces costs
- It’s all about precision
- Non-unique indexes include the rowid automatically – for Oracle there are no non-unique indexes
- Index compression can save a lots of space on repetitive columns
- Index compression means elimination of duplicates
- Primary key constraints don’t necessarily need unique key indexes but only indexes with the columns in the right order
- Reverse indexes scatter entries accros the line, clustering factor can end up terrible
- Don’t duplicate indexes – e.g. FK constraints can share the first columns of another index
- Difference between tables and indexes: Index blocks have to be at the right place
- Don’t trust Oracle – test everything!
Gerald on IT 