Heap tables are pretty self-explanatory, but IOTs seem to have some strange restrictions on them (have to have an index on them in the table definition, can't use certain partitioning on them...); whats the difference between the two?
Answer by Ian Ringrose ·
An index organized table in oracle is like a clustered index in SqlServer.
A index organized table is a btree that is index on all the columns in the primary key of the table and contains all the columns of the table
Secondary indexes on an IOT are more expensive than indexes on a heap table, they can be a lot more expensive if the primary key is complex or large.
Due to the way Oracle does locking etc, a index organized table has high overheads on insert so should only be used when you know the benefit of using it.
Consider an IOT when you have lots of lookups bases on the **same key and not many inserts or updates.**
Also consider using a covering index (that contains all the columns in the select) as another option, so your lookups dont have to go to the main table at all. However consider the cost of keeping the covering index up-to-date.
Answer by Andrew Mobbs ·
An IOT, as the name suggests, organized the table as a B-Tree index. It requires the PK index because that's how the data is structured on disk.
Secondary indexes on an IOT are more expensive than indexes on a heap table.
One advantage of IOTs is that rows are sorted by the primary key, so related records can be efficiently retrieved. For example, if you have an IOT with a PK of accountid,orderid then all orders for that account will be stored together, and thus can be retrieved quickly, whereas on a heap table the records would be all over the place. However, a secondary index on orderid will be less efficient than the same thing on a heap table, so pulling out an individual order would be slower.
Answer by Tariq Rahiman ·
HEAP - Rows stored in no particular order
IOT - Data is stored in a B*Tree index structure
B*Tree is a structure used for storing indexes. B*Tree index consists of levels of branch blocks, each level containing pointers to the next lower level, with a set of leaf blocks at the lowest level.
CREATE TABLE foh (f1 NUMBER PRIMARY KEY, f2 VARCHAR2(30)) ORGANIZATION HEAP;
CREATE TABLE fiot (f1 NUMBER PRIMARY KEY, f2 VARCHAR2(30)) ORGANIZATION INDEX;
The IOT combines a table and index in one, saving you the effort of creating an index Queries using the fields of this index result in fewer block accesses
Results in significant performance gains, since additional access to table blocks is no longer necessary
Because rows are stored in primary key order, a significant amount of additional storage space savings can be obtained through the use of key compression.
CREATE TABLE fiot_compressed (f1 NUMBER, f2 NUMBER, f3 NUMBER, CONSTRAINT pk_comp_iot PRIMARY KEY(f1, f2, f3)) ORGANIZATION INDEX COMPRESS 2
ALTER TABLE fiot MOVE ONLINE;
SELECT TABLE_NAME, INDEX_NAME FROM DBA_INDEXES WHERE INDEX_TYPE = 'IOT - TOP';