Elements of index-organized Table[Diagram]

Creating an index-organized table in Oracle

CREATE TABLE table_name column_list 
  CONSTRAINT pk_name PRIMARY KEY (pk_column_list))
  ORGANIZATION INDEX;

CREATE TABLE	Required keywords.
table_name	Unique table name.
column_list	Required keyword.
CONSTRAINT	An index-organized table must include a primary key, so the definition must include a primary key constraint. This keyword is required to begin the definition of the primary key constraint.
pk_name	The name of the primary key constraint
PRIMARY KEY	Required keywords
pk_column_list	A list of columns in the primary key
ORGANIZATION INDEX	Required keywords

Example: Creating an Index-Organized Table

The following statement creates an index-organized table:

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;

This example creates an index-organized table named admin_docindex, with a primary key composed of the columns token and doc_id. The OVERFLOW and PCTTHRESHOLD clauses specify that if the length of a row exceeds 20% of the index block size, then the column that exceeded that threshold and all columns after it are moved to the overflow segment.
The overflow segment is stored in the admin_tbs2 tablespace.