Partitioning and IOT

In Oracle 8 and 8.1 Index Organized Tables (IOT) are partitioned on RANGE only. Oracle 9 enables IOT to have hash partitions also.

Before any further discussion on IOT let us recapitulate IOT concepts.

IOT are generally preferred to hold static data and not dynamic data. There are listed restrictions as mentioned in Oracle Docs and Metalink Doc Id 176041.1

1. A primary key must be specified otherwise ORA-25175 is returned.
2. If pctthreshold is defined and an overflow segment is not defined, rows exceeding the threshold are rejected with an ORA-1429 error.
3. Cannot create an IOT of object types
4. An IOT can contain columns of LOB and nested table types, but only in the table is not partitioned
5. IOT-s must be reorganized using the move clause of the alter table command.

The basic difference between an index and IOT is that IOT-s store the data as if the entire table was stored in an index where as a normal index only stores the indexed columns in the index. This means that the index shall have all the columns even though the primary key is defined on a single column or set of columns.

This means that the table cannot have ROWID and they are not accessible. This was true until Oracle 8.

In 8.1 Oracle had introduced logical ROWID in place of physical ROWID. Logical ROWID-s allows the construction of secondary indexes and improves index performance allowing the IOT performance to match the performance of regular indexes. This was a new feature

Do not forget that Oracle 8 had not allowed any additional indexes on IOT-s
With the new feature Logical ROWID-s on IOT-s additional indexes are allowed and are constructed in Oracle 8.i. The space requirement for this logical ROWID is less when compared to the physical ROWID-s.

The Index Organized Table (IOT) concept has matured in Oracle9i to the point where the differences between IOT-s and Heap Organized Tables (HOT) are less than the similarities.

We shall have more discussions on this in Part VI