Richard Foote's Blog

In my previous post on Index Organized Tables (IOT), I introduced the concept of the IOT Overflow Segment, where we can store columns that we may not want to include within the actual IOT index structure. Before we move on, I just wanted to cover off a few additional points that could be a trap for the [...]

In my previous introductory post, I illustrated how an Index Organized Table (IOT) might be worth consideration if most or all columns in a table were to be included within an index. I’m going to use a slightly different demo this time, replacing one of the columns with a much larger DESCRIPTION column, one which is [...]

Thought it was high time that I covered in a little detail the subject of Index Organized Tables (IOTs). When used appropriately, they can be an extremely useful method of storing and accessing data. Hopefully by the end of this series, you’ll have a better understanding of IOTs, their respective strengths and weaknesses and so perhaps [...]

Based on the excellent comments in the Quiz post, we have some clever cookies out there I guess the first thing to point out is that based in the basic scenario provided, the index shouldn’t ordinarily be continually growing in this fashion. Although the index values are monotonically increasing, the deletions are leaving behind fully emptied leaf blocks which [...]

I received an email recently that had a nice example of what can potentially go wrong with an index. Let’s first create a simple table with a unique index and populate it with 200,000 rows (following demo run on 11.2.0.1): So far, everything is as expected. With have an index with 200,000 rows that currently has [...]

Happy New Year !! As has been mentioned to me on quite a number of occasions, I’ve haven’t really covered the area of Index Organized Tables (IOTs) on this blog. This despite covering the subject of IOTs in quite some detail in my Index Internals Seminar. Which I readily admit is a bit of a shame as [...]

I just want to wish you all a very Merry Christmas and a very happy, safe and successful New Year. As always, thank you all so much for supporting the blog, with all your comments and nice feedback. Hopefully, you’ve found content here that has been of some use. Now that I’m back at Oracle [...]

I’ve been very quiet on the blogging front lately, so apologies for that. One of the main reasons is that after a period of nearly 10 years, I’ve decided to take up a position back at Oracle Corporation as a “Principal Solutions Consultant” in sunny Canberra. So things have been rather hectic, finishing up in [...]

I’ve been so busy lately, I just haven’t had any spare time to post. For now, the quick answer to the last quiz is that the second table was indeed an Index Organized Table (IOT). One of the nice benefits of an IOT is that when re-organised, unlike a Heap Table, all indexes remain valid, [...]