Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
My research seems to indicate that they are not as widely used, though - is it as @Gaius says here: dba.stackexchange.com/questions/1847/…? Are Oracle folks just missing out? –  JHFB Feb 6 '13 at 14:52
    
IOTs are very rarely used in Oracle. Think I've only ever used 2 in my 12 years as an Oracle DBA –  FreshPhilOfSO Feb 6 '13 at 15:21
1  
add comment

3 Answers

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. ( http://www.red-gate.com/products/oracle-development/deployment-suite-for-oracle/education/webinars/webinar-statistics-oracle-sql-server-jonathan-lewis ) 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.

share|improve this answer
    
Thanks, @Jim. So it sounds like the clustered indexes in SS overcome this lack of physical information in the stats; thus Oracle should theoretically run faster without such an index? Also to clarify, I would want to use IOTs to guarantee the close physical location of data rows for particular columns? –  JHFB Feb 6 '13 at 15:10
1  
@JHFB - yes, the IOT guarantees that the data making up the primary key index for the table will be physically ordered according to the columns in the index. So this could be used to ensure that the rows in a child table for a given parent are physically located near each other. –  Chris Saxon Feb 7 '13 at 12:45
add comment

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.
share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.