Lesson 3	Creating an index-organized table
Objective	Syntax for Creating an index-organized table

Syntax for Creating an index-organized table

As the name implies, an index-organized table is just another variety of a standard table. To create an index-organized table, a simple variation of the standard commands is used to create a table.

Create index-organized table Syntax

The syntax for creating an index-organized table is exactly like the syntax used to create a standard table, with a single added pair of keywords, as shown in the following Diagram:

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

Required keywords

Unique table name

List of columns in the table. Each column is followed by a data type description.

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.

The name of the primary key constraint

A list of columns in the primary key

Elements Of Index Organized Table

As you can see from the ToolTip above , the only real difference between

defining a standard index and
defining an index-organized table

is the inclusion of the keywords ORGANIZATION INDEX at the end of the statement defining an index-organized table. Oracle Data Definition Language (DDL) does support the keywords ORGANIZATION HEAP as an alternative, but because the HEAP structure is the default, the only reason to include ORGANIZATION HEAP in a DDL statement is for documentation.
An index-organized table must have a primary key, so you must also define a primary key constraint for the table.
Click the learning bridge if you would like to review primary keys.
How To Bridge Primary Keys

Example

The following code will create a table called sales figures as an index-organized table.

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;

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