Clustered index in SQL Server vs index organized tables in Oracle

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.

StackExchange Database Administrators Q#34236, answer score: 9