Vincent makes some great points of the caveats of IOTs, but you can get some significant benefits from them as well.
Personally I think that they are significantly underused in Oracle and should be considered much more widely - not just as possible solution to performance problems. As you have to recreate the table to convert between IOT and heap, this is a change which is unlikely to happen on an always up, heavily used database unless the performance problems are severe.
Martin Widlake has a great series of posts about IOTs. There are some significant benefits you can get by using them:
- Significantly reduce physical and logical IO reads
- More efficient use of the buffer cache, which can benefit system wide performance
- Saved space as you're just maintaining an index, not a table as well (unless you have overflow segments)
However, to get these benefits you need tables where you (nearly) always include the leading column(s) of the primary key in queries and you're likely to be fetching several rows at once. Some common examples of such tables are:
- Master-multiple details as is often found in orders - order items, invoices - invoice lines etc.
- Many-to-many resolution tables which are typically queried "one-way". e.g. in a
customer_addresses table, it's far more common to find all the addresses for a customer, rather than all the customers for an address.
A downside is that inserting data is slower, so you need to weigh up the costs and benefits. Ultimately, it comes down to knowing your data and understanding how it's to be used which should guide the decision.
