Standard Tables versus index-organized Tables

With an Index Organized Tables, a table is stored in an index structure. This imposes physical order on the rows themselves. Whereas in a heap, the data is stuffed wherever it might fit, in an index organized table the data is stored in sorted order, according to the primary key. Efficient when most of the column values are included in the primary key. You access the index as if it were a table and the data is stored in a B-tree like structure.

Index-Organized Table

An (IOT) index-organized table stores the entire contents of the table's row in a B-tree index structure. An IOT provides fast access for queries that have exact matches and/or range searches on the primary key. Even though an IOT is implemented as a B-tree index structure, it is created via the

CREATE TABLE...ORGANIZATION INDEX

statement.
For example,

create table prod_sku
(prod_sku_id number
,sku varchar2(256),
constraint prod_sku_pk primary key(prod_sku_id, sku)
) organization index;

1) In a standard table and index, the index includes index nodes and a 'bottom' level of leaf nodes, which contain ROWIDs that point to rows in the data table.

2) To access data, you must go through the index to the leaf node and then to the row in the data table by using the ROWID.

3) In an index organized-table, the data is stored in the leaf nodes of the index itself, so there is no need for the ROWID of the data row.

4) To access data in an index-organized table, you go through the index directly to the data in the leaf nodes. — 4) To access data in an index-organized table, you go through the index directly to the data in the leaf nodes .