Skip to main content

Search

Welcome to Tibero GTS!

IOT (Index Organized Table) User Guide

Document Type | Technical Information

Category | Installation

Applicable Product Version | Tibero 7.2.4

Document Number | TADTI171

 

Overview

When accessing records through an index in a regular table, the process involves two steps: searching the index with the key value to obtain the ROWID, and then reading the data using the ROWID.
 
IOT was created to resolve the speed degradation caused by this, by storing the table's data together in the index area, and the table's data is always stored sorted by the table's Primary key value.
 
IOT can be applied to master-type tables where Nested Loop is frequently used, tables with few columns where most columns are indexed, and tables where indexes other than the Primary key are unnecessary.
 

IOT differs from regular tables in the following ways:

  • Regular tables distinguish rows by ROWID, but IOT distinguishes rows by the Primary key.

  • Full scans of regular tables return rows in an unpredictable order, but IOT outputs rows in the order of the Primary key values.

  • IOT requires a Primary key to be created.

  • IOT uses less storage space than regular tables.

 

Structure of Regular Tables and IOT

TABLEIOT

 

Advantages and Disadvantages of IOT

CategoryDescription
Advantages

- Faster than regular tables when performing random access on the primary key as the data is sorted based on the primary key.

- Storage usage is reduced because the primary key is not redundantly stored in both the index and data areas.

Disadvantages

- Performance slows down due to sorting operations when data is changed. (IOT is not suitable for cases with frequent inserts, updates, or deletes)

- Direct Path Insert does not work on IOT.

- Performance decreases as the number of columns increases.

 

Tibero IOT Support

CategoryDescription
Supported

- Column size changes

- Setting column default values, NULL, NOT NULL

- Changing two or more columns simultaneously

- Deleting columns and column constraints

Not Supported

- ALTER TABLE statements on partitions in Partitioned IOT (Partition modify not supported)

- Only NULL and NOT NULL modifications are allowed in MODIFY COLUMN (checks and unique constraints are not allowed)

- Modification of Primary key (planned for future support)

 

Method

Creating an IOT Table

How to Create an IOT Table

CREATE TABLE <table_name> (
    <column1>,
    <column2>,
    <column3>,
    PRIMARY KEY (<column1, column2>)
)
ORGANIZATION INDEX TABLESPACE <tablespace_name>
PCTTHRESHOLD <row_percentage>
INCLUDING <column>
OVERFLOW TABLESPACE <tablespace_name>;

The descriptions for each item are as follows:

ItemDescription
ORGANIZATION INDEXDefines the creation of the IOT and specifies the tablespace to use.
PCTTHRESHOLD

If data larger than n% of one block is entered, data that is not part of the key columns is stored in the tablespace defined by the OVERFLOW TABLESPACE clause.
โ€“ If INCLUDING is not specified, columns exceeding the PCTTHRESHOLD range are stored in the OVERFLOW data area.

โ€“ If INCLUDING is specified, if the sum of the sizes of columns up to and including the INCLUDING column exceeds the PCTTHRESHOLD range, they are stored in the OVERFLOW data area.

INCLUDINGColumns declared with INCLUDING are always stored in the overflow data area. INCLUDING can specify the last column of the primary key or any non-primary key column.
OVERFLOWSpecifies the tablespace to use when storing data in the overflow area.

 

Example of Creating an IOT Table

SQL> CREATE TABLE JOB_IOT (
        JOBID VARCHAR(10 BYTE),
        JOBNAME VARCHAR(35 BYTE),
        MINSAL NUMBER(6),
        MAXSAL NUMBER(6),
        PRIMARY KEY (JOBID)
     )
     ORGANIZATION INDEX TABLESPACE USR
     PCTTHRESHOLD 20
     INCLUDING JOBID
     OVERFLOW TABLESPACE USR;

Table 'JOB_IOT' created.

Total elapsed time 00:00:00.163124

Caution

When creating an IOT, be aware of the following restrictions.

  • LOBs or LONG data types cannot be included.
  • The maximum number of columns is 1000.
  • Only up to 255 columns can be stored in the index area. If the number of columns exceeds this or cannot all be stored in the index area, OVERFLOW must be specified.
  • The value of PCTTHRESHOLD is between 1 and 50. However, due to structural issues, the maximum size of rows that can actually be stored in the index area is less than 50% of the block.
  • All columns must be smaller than the PCTTHRESHOLD.

 

 

 

Related to