Creating an Index-Organized Table in Oracle

An index-organized table (IOT) is a specialized table structure in Oracle where data is stored in a B-tree index structure, sorted by the primary key. Unlike a standard heap-organized table, which stores data in an unordered heap, an IOT combines the table and its primary key index into a single structure. This provides faster access to data when queried by the primary key but may impact performance for other operations.

Why Use an Index-Organized Table?

• Performance Benefits: IOTs offer faster primary key-based lookups because the data is stored directly in the B-tree index, reducing the need for separate index lookups. This is ideal for queries that frequently filter or join on the primary key.
• Storage Efficiency: Since the primary key index and table data are unified, IOTs can save storage space compared to a heap table with a separate index.
• Use Cases: IOTs are best suited for:
  • Lookup tables or mapping tables (e.g., key-value stores).
  • Applications where most queries use the primary key (e.g., dictionary tables, document indexing).
  • Scenarios requiring sorted access to data by the primary key.
• Drawbacks:
  • Slower DML operations (INSERT, UPDATE, DELETE) due to the need to maintain the B-tree structure.
  • Limited support for secondary indexes, which may perform less efficiently.
  • Increased complexity for managing overflow segments (explained below).

The syntax for creating an IOT is similar to that of a standard table, with the addition of the ORGANIZATION INDEX keywords. An IOT requires a primary key, as the data is physically stored in the order of the primary key. Below is the syntax:

CREATE TABLE table_name (
  column_list 
  CONSTRAINT pk_name PRIMARY KEY (pk_column_list)
)
ORGANIZATION INDEX
[TABLESPACE tablespace_name]
[PCTTHRESHOLD percentage]
[OVERFLOW TABLESPACE overflow_tablespace_name];

This example creates a basic IOT for tracking sales figures, using a composite primary key:

CREATE TABLE sales_figures (
  store_id NUMBER,
  quarter INTEGER,
  month INTEGER,
  amount NUMBER,
  CONSTRAINT pk_sales_figures PRIMARY KEY (store_id, quarter, month)
)
ORGANIZATION INDEX;

This table is ideal for queries that retrieve sales data by store_id, quarter, and month, as the data is stored in sorted order by these columns.

This example demonstrates an IOT with overflow storage for a document indexing system:

CREATE TABLE admin_docindex (
  token CHAR(20),
  doc_id NUMBER,
  token_frequency NUMBER,
  token_offsets VARCHAR2(2000),
  CONSTRAINT pk_admin_docindex PRIMARY KEY (token, doc_id)
)
ORGANIZATION INDEX
TABLESPACE admin_tbs
PCTTHRESHOLD 20
OVERFLOW TABLESPACE admin_tbs2;

In this example:

• The table admin_docindex stores document indexing data with a composite primary key (token, doc_id).
• PCTTHRESHOLD 20 means that if a row exceeds 20% of the index block size, the excess columns (starting with token_offsets) are moved to an overflow segment.
• The overflow segment is stored in the admin_tbs2 tablespace, improving performance for large rows.

Compared to a standard heap-organized table:

• An IOT requires a primary key constraint and the ORGANIZATION INDEX clause.
• Data is stored in a B-tree index, sorted by the primary key, unlike the unordered storage of a heap-organized table (which uses ORGANIZATION HEAP, the default in Oracle).
• The ORGANIZATION HEAP keyword is rarely used explicitly, as it is the default table structure, but it can be included for clarity in documentation.

Review Primary Key Concepts

Use an IOT when:

• Queries primarily access data via the primary key (e.g., lookup tables).
• You need sorted data access or compact storage.

Avoid IOTs when:

• Frequent inserts, updates, or deletes are expected, as these are slower due to B-tree maintenance.
• Secondary indexes are heavily accessed or non-unique key access is common.

The next lesson covers how to rebuild an index-organized table.