Index Organized Tables – A Start (Star) January 3, 2012
Posted by Richard Foote in Index Organized Tables, Oracle Indexes.trackback
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 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 …
Richard Foote's Oracle Blog 
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?
🙂
LikeLike
Hi Narendra
Actually, that’s not a bad guess. It’s not quite correct but certainly in the right ball park 🙂
LikeLike
Hi Richard (and best wishes on the 3rd day of the new year).
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.
Just two of my IOT hobby-horses.
Happy Writing.
LikeLike
Hi Piet
See what I can do for you 🙂
I might even convince you on why the “row-guess” might be quite useful yet 🙂
LikeLike
I hope a focus on IOTs in whether to use them all the time unless it seems like a bad choice, or never to use them unless they seem like a good choice.
SQL Server organizes each TABLE according to the CLUSTERED INDEX (usually the PK), and i’m trying to figure out Oracle’s style.
LikeLike
Hi Brian
That’s a good point. I think in the end, I hope to convince you to just use them when they’re useful and not to use them when other structures are better 🙂
LikeLike
“hope to convince you to just use them when they’re useful and not to use them when other structures are better :)”
Ever think of becoming a politician? 🙂
LikeLike
[…] I mentioned in a previous post, Martin Widlake has recently written an excellent series on IOTs, which I highly recommend. […]
LikeLike