I am making the transition as a database developer from SQL Server to Oracle and found some fantastic resources here already (How to make a transition from SQL Server DBA to Oracle? and As a DBA, how would I go about transitioning from Oracle to SQL Server?) but I am having a hard time finding good information on the use of index organized tables in Oracle.
In my previous life, we made extensive use of clustered indexes in SQL Server in our OLTP-ish datamart with great success. Are index organized tables as handy a tool in Oracle?
If you’re transitioning from SQL Server to Oracle, I would advise to try heap tables at first because they are the standard form of storing data in Oracle. For most workloads, heap tables with regular indexes in Oracle are the most balanced forms of storage regarding DML and query performance.
If later you find that you have performance problems or bottleneck, you should look into specialized advanced storage methods such as IOT, partitioning, clusters, reversed-key indexes, etc.
Regarding IOT in particular, I would advise against their generalized use because there are lots of “gotchas” that you may not want to get into as a beginner:
- IOT don’t have real rowid (because there’s no table per se).
- consequently, secondary indexes on IOT don’t have true pointers to the rows but only mere guesses which can lead to inefficient index scans.
- Some features are disabled on IOTs such as virtual columns, table compression, composite partitioning.
- You have to decide at creation where to store the non-index columns (inline or in an overflow segment), potentially leading to disastrous performance for some queries.
IOTs in Oracle are not quite the same as clustered indexes in SS because Oracle stats includes the physical scatter of rows, whereas SS does not include physical location in its stats. See this debate between Lewis and Fritchey on Statistics in Oracle and Sql Server for more info.
That is why a clustered index in SS is better than a heap. The clustered index adds physical location data to the stats.
IOTs are good when you know that the index provides colocation of data rows that will be searched, e.g. index on order_date and customer for a order table would make a good IOT.
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_addressestable, 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.