OracleSource

An indexed-organized table (IOT) is like a regular table with an index on one or more of its columns, but instead of maintaining two separate segments for the table and the B*-tree index, the Oracle server maintains one single B*-tree structure which contains:
• The primary key value
• Other (non-key) column values for the row

The B*-tree structure, which is based on the primary key of the table, is organized like an index.
The leaf blocks in this structure contain the rows instead of the ROWIDs. This means that the rows in the index-organized table are always maintained in the order of the primary key.

Because index-organized tables do not have real ROWIDs, the concept of logical ROWIDs is introduced in Oracle9i. This allows you to create additional indexes on index-organized tables; this was not possible in Oracle8 release 8.0.
Because large rows of an index-organized table might destroy the dense and efficient storage of the B*-tree structure, in Oracle9i you can store part of the row in another segment, called an overflow area. This is discussed on the following pages.

IOT Performance Characteristics

 B*-tree stores full rows
 Sorted rows
 Logical ROWIDs
 Fast, key-based access to table data

You can access an index-organized table by using either the primary key or a combination of columns that constitute the leading part of the primary key.
IOTs provide fast, key-based access for queries involving exact match (equality operator) or range searches on the primary key.

Before Oracle9i, any other access to rows in an index-organized table required a full scan of the B*-tree structure. That is why Oracle9i introduced the concept of logical ROWIDs, which you can use to create additional indexes on an index-organized table. Logical ROWIDs internally have the UROWID datatype.

Because the rows are ordered in the IOT, full scans on an index-organized table return rows in a primary key sequence.
Because there is no duplication of primary key values (compared with regular tables: index segment and data segment), IOTs use less storage. Index organization is useful for a table that is frequently accessed using the primary key and has only a few, relatively short nonkey columns.

Note : The statement in the fourth paragraph about returning the rows in primary key sequence is only true for serial execution; it is no longer true with parallel execution. Also, the first bullet on the slide is not always true: the concept of row overflow is introduced on the following pages.

IOT Limitations

 Must have a primary key
 Cannot be part of an index cluster or hash cluster
 Cannot contain LONG columns (although LOB columns are allowed)

• Index-organized tables must have a primary key. This is the unique identifier and is used as the basis for ordering; there is no ROWID to act as a unique identifier in the B*-tree structure.
• Index-organized tables cannot be part of an index cluster or a hash cluster.
• Index-organized tables cannot include LONG columns, but they can contain LOB columns.
• Since index-organized tables are B*-tree structures, they are subject to fragmentation as a result of incremental updating.
• You can use the ALTER TABLE … MOVE command to rebuild the index-organized table:
ALTER TABLE iot_tablename MOVE [OVERFLOW...];
• Specifying the optional OVERFLOW clause causes the overflow segment to be rebuilt, as well. Overflow segments are explained on the following pages.

Note : Oracle9i removed the IOT restrictions regarding secondary indexes and additional UNIQUE constraints. You may mention the absence of real ROWIDs as an IOT limitation, although the implementation of logical ROWIDs (with the UROWID data type) resolved all related shortcomings. Note that the MOVE option for the ALTER TABLE statement is new in Oracle9i.

When to Use Index-Organized Tables

 Note that:
• IOTs are transparent to users and applications
• IOTs are supported by Oracle9i utilities

 Content-based information applications: text, images, and sound storage or retrieval

• Applications that use complex data objects have many different indexing requirements. For example, information-retrieval applications may need a keyword search or a search for similar words.
• Index-organized tables are particularly useful for applications accessing and manipulating complex data. Examples are information-retrieval, spatial, and OLAP applications.
• No special considerations exist for using most SQL statements against an index-organized table. The structure of the table should be completely transparent to the user.
• The Oracle9i utilities also support index-organized tables. SQL*Loader, using direct path mode, can load data directly into an index-organized table. This kind of loading can be quicker than loading into a standard table and then building the indexes afterwards.


Creating Index-Organized Tables

CREATE TABLE table-name
( column_definitions
[,constraint_definitions] )
ORGANIZATION INDEX
[ block_util_parameters ]
[ storage_clause ]
[ TABLESPACE tablespace ]
[ PCTTHRESHOLD integer
[ INCLUDING column_name ] ]
[ OVERFLOW segment_attr_clause ]


• The ORGANIZATION INDEX clause of the CREATE TABLE statement specifies that you create an index-organized table.
• You must specify a primary key constraint when creating index-organized tables. If you try to create an index-organized table without a primary key, the following error is generated:
ORA-25175: no PRIMARY KEY constraint found
• PCTTHRESHOLD specifies the percentage of space reserved for an index-organized table row.
• PCTTHRESHOLD defaults to 50 and must be a value from 0 to 50.
• If a row exceeds the size calculated based on this value, all columns after the column named in the INCLUDING clause are moved to the overflow segment.
• If OVERFLOW is not specified, then rows exceeding the threshold are rejected.
• INCLUDING column_name specifies a column at which to divide an index-organized table row into index and overflow portions. All columns that follow column_name are stored in the overflow data segment. If this is not specified and a row size exceeds PCTTHRESHOLD, all columns except the primary key columns are moved to the overflow area. The column is either the name of the last primary key column or any nonprimary key column.
• OVERFLOW specifies that index-organized table rows exceeding the specified threshold be placed in the data segment defined by segment_attr_clause, which specifies the tablespace, storage, and block utilization parameters.

IOT Row Overflow

• Large rows in an index-organized table might destroy the dense B*-tree storage. You can overcome this problem by using an overflow area. Note that you need additional I/Os to retrieve these large rows, because the rows are stored in two pieces. This results in a decrease in performance compared to an IOT with shorter records that are mostly stored entirely in the IOT segment alone.
• When an index-organized table is created by specifying an OVERFLOW clause, the following three segments are created in the database:
• A logical table with the name defined in the CREATE TABLE clause
• An index with the same name as the primary key constraint
• A table to accommodate the overflow row pieces (The name of this table is SYS_IOT_OVER_n, where n is the OBJECT_ID of the index-organized table as seen from USER_OBJECTS.)

Note: If you create an index-organized table without specifying an OVERFLOW clause, only the first two segments are created. Give the primary key constraint a name, so that the index segment receives a meaningful name (as opposed to a system generated one.)

Retrieving IOT Information from the Data Dictionary

Use the following query to list the index-organized tables and information related to their structure:
SQL> select t.table_name as "IOT"
2 , o.table_name as "Overflow"
3 , i.index_name as "Index"
4 , o.tablespace_name as "Overflow TS"
5 , i.tablespace_name as "Index TS"
6 , i.pct_threshold as "Threshold"
7 from user_tables t
8 , user_tables o
9 , user_indexes i
10 where t.table_name = o.iot_name
11 and t.table_name = i.table_name;