When you create an index on a table, a separate
storage area is allocated to store the B-Tree Structure,
another B-Tree object is created related to the
table. In an index-organized table the data for
the table is held in its associated index.
Rather than having a row's rowid
as the second element of the index entry, the actual
data row is stored in the B*-tree index. Changes
to the table data, such as adding new rows, updating
rows, or deleting rows, result only in updating
the index.
How to create an Index Organized
Table
You can use the CREATE TABLE
statement, but you have to provide certain other
parameters.
CREATE
TABLE emp_dept
(
EMP_ID number,
DEPT_ID number,
NAME varchar2(250),
ADDRESS varchar2(250),
CITY varchar2(250),
STATE char(2),
constraint PK_EXP_DEPT primary key (EMP_ID, DEPT_ID)
)
ORGANIZATION INDEX TABLESPACE index_tblspace;
The ORGANIZATION INDEX clause identifies the table
as an IOT.
Differences Between IOT and Regular
Tables
Regular
Table
IOT
Rowid is the
Unique Identifier
Primary key is
the unique Identifier
Physical Rowid
Logical Rowid
Rowid Based Access
Primary Key based
access
Unique Constraints
allowed
Unique Constraints
Not Allowed
Can be stored
in a cluster
Cannot be stored
in a cluster
Can contain both
LONG and LOB
Can Contain LOBs
but not LONG
Replication Supported
Replication Not
Supported
Benefits of IOT
1. Since you are not storing the
index keys separately the space required is less.
2. Optimizer does not need to
read two locations. The data can be found in one
structure.
3. Since the data is organized
based on the primary key, applications that use
primary key to fetch the data performs well.
Notes :
1. You can use the MOVE option
of the create table to rebuild an Index Organized
Table
2. You can create IOT's in parallel
by issuing a Parallel hint in the select part of
"CREATE TABLE AS SELECT" statement
3. You can partition an IOT as
long as the partition key is a subset of the primary
key
