The ODC appreciation Day is a yearly tradition created by Tim Hall of Oracle-Base (link) to say "Thank You" to the Oracle Developer Community. This year he designated 10-Oct-2017, hence this post.
link to Tim: https://oracle-base.com/blog/2017/09/25/odc-appreciation-day-2017-thanksodc/
To join in on the ODC chorus, I'll present my favorite, very Simple, feature.
The INDEX ORGANIZED TABLE.
This feature is a favorite of mine, because 1) it is simple, you can explain it to almost anyone in under five minutes 2) there are a few additional benefits that kick in with the way Oracle has implemented this feature.
Short Description:
In an Index-Organized Table (IOT) is a table of which the of records are (physically) stored in the order of their defined primary-key index, even if they are inserted at very different points in time. In a conventional (heap) table, the dependants of 1 parent may get scattered all over the heap, depending on the time of insert, whereas in an IOT the children of a parent will always end up in the same block. (or adjacent blocks if more are needed).
Use-Cases:
In my experience, IOTs can be beneficial in 3 specific cases :
1) parent-child tables where retrieval often includes the "fetch all children" case.
2) link-tables that connect two sets in an n:m relation.
3) lookup tables.
I will not say you need to take these cases as "must-do" or "best practices", because there are cases where IOTs will fit nicely, but there are also cases where they will backfire on you. Go Read.
History and "other databases":
IOT has been around since Oracle 8 or 8.1 (the nineties!), but somehow has only been used by nerdy-geeky DBA's in specific cases. Competing Databases have "discovered" and used this feature more than Oracle. Hence explaining this feature to converted colleagues is sometimes very easy.
Further reading:
I would encourage you to explore the Manual and other sources of information, especially if you have large "hierarchical" sets of data with parent-child relations such as history-keeping or sets of object-attribute tables.
Richard -Mr Index- Foote has discussed IOTs in 2012:
And Martin Widlake in 2011:
And here is the link to my original blogpost about IOTs, mainly based on my work on a few project from around that time:
Voodoo Anecdote:
Kamil and his team on the Voodoo product have recently found to their surprise(?), that direct-inserts do not work on IOTs. (link) Not surprising, if you realise that each record will have to be inserted in exactly the right place in the table: in between it's "siblings", to maintain the correct ordering of the records. Hence a Direct-Insert (inserting into a new block) would only be possible if the inserted data adhered to at least two conditions: 1) new records should have higher PK values than the highest existing record and 2) the newly inserted data would need to be presented in a (pre)ordered way to maintain the IOT property.
Careful study of the Really Terribly Fantastic Manual may reveal that a direct-insert on IOT is (was?) possible, but only when inserting pre-ordered data into an empy-table (link to docu, if I can still find it).
IoT versus IOT
Since the abbreviation IoT is now in use for Internet of Things, any mention of IOT (index organized table) seems to get lost in the noise. Hence this blog-post is also little reminder to the search engines to keep the legacy-item of IOT alive.
1 comment:
Your blog is so nice, and the article is very good it helps to so many people.
Oracle Training in Hyderabad
Post a Comment