| 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:
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
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
- defining a standard index and
- defining an index-organized table
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.