| Lesson 6 | Limitations in index-organized tables |
| Objective | Understand the differences between index-organized and regular tables. |
Index-organized Tables versus Regular Tables
Index-organized tables use a completely different storage structure than regular tables. There are also a number of differences in capability and functionality between the two types of tables.
Differences
The following table summarizes the differences between an index-organized table and a standard table:
| Standard table | Index-organized table |
| Bitmap indexes allowed | Bitmapped indexes not allowed |
| Reverse indexes allowed | Reverse indexes not allowed |
| Unique constraint allowed | Unique constraint not allowed |
| Triggers allowed | Triggers not allowed |
| Can be stored in a cluster | Cannot be stored in a cluster |
| Can contain LONG columns | Cannot contain LONG columns |
| Distribution supported | Distribution not supported |
| Replication supported | Replication not supported |
| ROWID uniquely identifies a row–primary key optional | Primary key uniquely identifies a row–primary key required |
| Secondary indexes use ROWIDs to locate table rows | Secondary indexes use logical ROWIDs to locate table rows |
| Secondary indexes store physical data | Secondary indexes store primary-key based logical ROWIDs |
Once upon a time, there was only one type of table known as a "normal" table. It was managed in the same
way a "heap" is managed (the definition of which is below). Over time, Oracle added more sophisticated
types of tables. There are clustered tables (two types of those), index organized tables, nested tables,
temporary tables, and object tables in addition to the heap organized table. Each type of table has
different characteristics that make it suitable for use in different application areas.
Index Organized Tables: In an (iot) table, the 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.
When to use index organized table
Index organized table :Efficient when most of the column values are included in the primary key. You access the index as if it were a table. The data is stored in a B-tree like structure.
Index-Organized Table
An index-organized table (IOT) 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;