Alter to Index Organized Table

[uparna]

Hi ,
Is it possible to convert an existing normal table to become an IOT ? ALTER TABLE X ORGANIZATION INDEX; doesnt seem to work...

Regards,
S. Jayaram Uparna .

 

[alexhu]

According to the Oracle9i Database Administrator's Guide
you use the create statement

I guess in your case that'll me export/ create/ import


Alex


Creating Index-Organized Tables
You use the CREATE TABLE statement to create index-organized tables, but you must provide the following additional information:

An ORGANIZATION INDEX qualifier, which indicates that this is an index-organized table
A primary key, specified through a column constraint clause (for a single column primary key) or a table constraint clause (for a multiple-column primary key). A primary key must be specified for index-organized tables.
An optional row overflow specification clause (OVERFLOW), which preserves dense clustering of the B-tree index by storing the row column values exceeding a specified threshold in a separate overflow data segment. An INCLUDING clause can also be specified to specify what (nonkey) columns are to be stored in the overflow data segment.
A PCTTHRESHOLD value which defines the percentage of space reserved in the index block for an index-organized table. Any portion of the row that exceeds the specified threshold is stored in the overflow segment. In other words, the row is broken at a column boundary into two pieces, a head piece and tail piece. The head piece fits in the specified threshold and is stored along with the key in the index leaf block. The tail piece is stored in the overflow area as one or more row pieces. Thus, the index entry contains the key value, the nonkey column values that fit the specified threshold, and a pointer to the rest of the row.
The following example creates an index-organized table:

CREATE TABLE admin_docindex(
token char(20),
doc_id NUMBER,
token_frequency NUMBER,
token_offsets VARCHAR2(512),
CONSTRAINT pk_admin_docindex PRIMARY KEY (token, doc_id))
ORGANIZATION INDEX
TABLESPACE admin_tbs
PCTTHRESHOLD 20
OVERFLOW TABLESPACE admin_tbs2;