RelationalDBDesign RelationalDBDesign 


Performance Tuning  «Prev 

Creating an index-organized table in Oracle

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.