Free sample data sets for everyone!

It’s actually been quite a while since I have put together these free sample data sets for everyone but it just struck me today that I’ve never blogged about them. It was in December 2020 when I finally published and announced these data sets on Twitter. But what am I actually talking about here, you may ask? Well, here comes a story of “when life gives you lemons, make lemonade!”

Continue reading “Free sample data sets for everyone!”

Blog update!

Hi everyone!

This blog hasn’t seen any updates since over a year now and I do apologize for that. However, the reason for that is simple. Last year I’ve decided to take on a new project. But before I was going to tell everybody about it I wanted to make sure that I also really got the time to do so. In the meantime I got the confidence that the project will succeed and that I have enough time to take it forward. So, how to say it… it’s almost like breaking up with a girlfriend. Well, here it is: I got a new one! Blog I mean of course. Last year in August I started a new blog:
The intention of that blog is to show how write the right code for using Oracle technologies. Over the years I’ve seen a lot of badly written code that would never ever perform on top of technologies such as the Oracle Database. Unfortunately, I’ve also not seen too many folks that did understand Database performance and were good programmers. There seems to be a gap between the database world and the programming world, well I’m sure that that doesn’t come as a surprise to you. So, with this new blog I want to bridge the gap as good as I can.

Let’s stay friends!

However, this blog is not dead yet! The other blog is for a very specific goal. But there is more in my IT life than just that. And everything that doesn’t fit into the above one will sooner or later end up here. That being said, the above one will definitely get more attention from me going forward so you may want to add that one to your RSS Reader as well!

Last but not least: I’ve also finally managed to go live on Twitter. If you prefer that way of communication you can follow me @GeraldVenzl

Indexing strategies with Jonathan Lewis

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: 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!