Oracle 11g Notes-index organization table

I. Index organization table (index-organized table, IOT)
An indexed organization table is a special kind of table that stores indexes and table data together. The data in the normal table is stored in the database in an unordered (Heap) manner.
The Index organization table is sorted by primary key, and the data of the table is stored in the form of a two-fork tree.
An indexed organization table does not store ROWID, which accesses data through a primary key.
The Index organization table is suitable for applications that access data through a primary key.

1. Advantages
(1) Fast random access. The index and table data are stored together, and if updates are made to the table, Oracle updates only the index structure.
(2) Fast range scan. The Index organization table has sorted the data by primary key, so the range scan is fast.
(3) less storage requirements. Index data and table data are stored together to reduce storage requirements. Normal index entries contain only index values and rowid that point to data rows

2. Composition
(1) the index section. Store primary key values, frequently accessed partial non-primary key values, pointing to the overflow area of the ROWID
(2) Overflow part. Used to hold non-primary key values. The overflow area is stored in an overflow table space. The user can specify an overflow table space.

3. Example
(1) Create INDEX Organization table
Sql>crate Table Students (
Name varchar (primary key),
ID number,
Location VARCHAR2 (200)
)
Organization Index
Tablespace LMTBSB
Pctthreshold 30
Including location
Overflow tablespace bigtbs_01;
Note: The index organization table must have a primary key,
Pctthreshold: Specifies the overflow ratio, and if the limit is exceeded, the overflow portion is stored in the overflow area.
Including: Indicates that all columns from this column will be stored in the overflow area.
Overflow tablespace: Specify overflow table space
If you want to create a bitmap index on an indexed organization table, the Index organization table must have a mapping (Mapping table), such as: in Organization index downstream: Mapping table
If the table is created, you can modify the table.
Sql>alter table students add mapping table;

(2) View IoT
Sql>select Owner,table_name,iot_name,iot_type
From Dba_tables
where owner= ' test ' and iot_type like '%iot% ';

Oracle 11g Notes-index organization table