Index Organized Tables – A Start (Star) January 3, 2012

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 IOTs are often misunderstood little things and I would say are generally way under-utilised in many environments, I would suggest in part because they are so often misunderstood. The only reason I can really give for not discussing them more is that I simply haven’t gotten around to it :)

Well the good news as hinted at late last year, it’s about time I addressed this and started writing a few blog pieces on IOTs.

However, the even better news is that the subject of IOTs have already been covered by Martin Widlake in a truly excellent series of articles on his blog. I would strongly recommend giving them a read as they cover many aspects of IOTs in an interesting and informative manner.

I’ll recap some of the basic principles of IOTs here and then discuss various different aspects (such as the joys of Secondary Indexes on IOTs) in my own unique way :)

So, all that’s to come in the coming weeks.

However, I recently received an index question via email during the Christmas holidays which I thought was worthy of a blog piece or two. Although I often respond to emails, I much prefer to discuss index related “stuff” on the blog so everyone can share in the knowledge exchange. This is an interesting question because it relates to why an index keeps growing, even though the number of rows in the table remains constant. It’s an even more interesting question because there are a number of contributing factors for this impact on the index which I thought were worth sharing.

No more clues, I’ll post the question and the specific scenario in the next few days …

Share this:

Like this:

Related

A Happy New Year Richard !!!
Let me guess. Is it something to do with a non-unique index on a column that has increasing value (unique ID, record creation date etc.) and where some kind of batch process adds (new records) and removes (oldest records) in single transaction? :)

Nice to see IOTs get a bit more attention: it seems an under-used feature but is finally getting some “credit”.

Would you mind adding two itmes to your treatment:
1. the row-guess in secondary indexes. It seems well-intended but not really useful. and
2. the partitioning of IOTs and notably the Split of a partition: can be done without moving all the data. as data in an IOT is already ordered, you can just split the data on the extent that contains the new boundary. no other data needs to move. Potentially very efficient split.
For the same reason, you could also, potentially, partition an existing IOT by moving verly little data.