RelationalDBDesign RelationalDBDesign



Clustering Tables   «Prev 

What Are Index-Organized Tables?

An index-organized table has a storage organization that is a variant of a primary B-tree. Unlike an ordinary (heap-organized) table whose data is stored as an unordered collection (heap), data for an index-organized table is stored in a B-tree index structure in a primary key sorted manner. Each leaf block in the index structure stores both the key and nonkey columns.
The structure of an index-organized table provides the following benefits:
  1. Fast random access on the primary key because an index-only scan is sufficient. And, because there is no separate table storage area, changes to the table data (such as adding new rows, updating rows, or deleting rows) result only in updating the index structure.
  2. Fast range access on the primary key because the rows are clustered in primary key order.
  3. Lower storage requirements because duplication of primary keys is avoided. They are not stored both in the index and underlying table, as is true with heap-organized tables.
Index-organized tables have full table functionality. They support features such as constraints, triggers, LOB and object columns, partitioning, parallel operations, online reorganization, and replication. And, they offer these additional features:
  1. Key compression
  2. Overflow storage area and specific column placement
  3. Secondary indexes, including bitmap indexes.

Index-organized tables are ideal for OLTP applications, which require fast primary key access and high availability. Queries and DML on an orders table used in electronic order processing are predominantly primary-key based and heavy volume causes fragmentation resulting in a frequent need to reorganize. Because an index-organized table can be reorganized online and without invalidating its secondary indexes, the window of unavailability is greatly reduced or eliminated. Index-organized tables are suitable for modeling application-specific index structures.
For example, content-based information retrieval applications containing text, image and audio data require inverted indexes that can be effectively modeled using index-organized tables. A fundamental component of an internet search engine is an inverted index that can be modeled using index-organized tables.

Table Index versus Cluster Key (Oracle Access)

In a normal table and index, the index tree has an entry for each row in the underlying data table, regardless of value.

If you wanted to access all the data for a particular index value, you would access each index value and its associated row in the table.

In a cluster, Oracle keeps a single entry for each value in the cluster key.

To access all the rows in a table that have the same value in the cluster key, Oracle reads a single entry from the cluster key and then directly accesses a data block that is filled with rows from the table that have the same value.