Index Organized Tables (IOTs) are tables saved within an index structure. Whereas a table saved inside a heap is unorganized, data within an IOT is saved and sorted by primary key (the information may be the index). IOTs behave much like “regular” tables, and you apply the same SQL to gain access to them.
Every table inside a proper relational database should really possess a primary key... If every table during my database includes a primary key, must i always employ a catalog organized table?
I am speculating the reply is no, then when is definitely an index organized table not the best option?
Essentially a catalog-organized table is definitely an index with no table. If you possess a table whose posts contain the main key and for the most part another column then you've a potential candidate for INDEX ORGANIZED.
But when you are considering the requirement for additional indexes around the non-primary key posts then you are most likely best having a regular heap table. So, since many tables most likely need additional indexes most tables aren't appropriate for IOTs.
Used, index organized tables are that appears to be reference data, code look-up matters. Application tables are nearly always just heap organized.
In the Oracle Concepts guide:
Index-organized tables are helpful when related bits of data should be saved together or data should be physically saved inside a specific order. This kind of table is frequently employed for information retrieval, spatial (see "Summary of Oracle Spatial"), and OLAP programs (see "OLAP").
This question from AskTom can also be of some interest especially where someone provides a scenario after which asks would an IOT perform much better than an heap organised table, Tom's fact is:
we are able to hypothesize all day long lengthy, but before you measure it, you may never know without a doubt.
I'd consider them for very narrow tables (like the join tables accustomed to resolve many-to-many tables). If (virtually) all of the posts within the table will be within an index anyway, then why should not you used an IOT.
Small tables could be good candidates for IOTs as talked about by Richard Foote here
I think about the following types of tables excellent candidates for IOTs:
- "small" "research" type tables (e.g. queried frequently, up-to-date rarely, matches a comparatively few blocks)
- any table that you simply already will have a catalog that covers all of the posts anyway (i.e. might as well save the area utilized by the table when the index replicates 100% from the data)
A catalog-organized table generally is a sensible choice should you only access data from that table through the key, the entire key, and absolutely nothing the answer.
Further, you will find many restrictions by what other database features can and can't be utilized with index-organized tables -- From the that in a minumum of one version you could not use logical standby databases with index-organized tables. A catalog-organized table isn't a sensible choice whether it prevents you against using other functionality.
I can not by itself discuss IOTs, if however I am reading through this right then they are just like a 'clustered index' in SQL Server. Typically you need to consider not using this kind of index in case your primary key (or even the value(s) you are indexing when not a principal key) could be distributed fairly at random - because these card inserts can lead to many page splits (costly).
Indexes for example identity posts (sequences in Oracle?) and dates 'around the present date' makes permanently candidates for such indexes.