LOBs in Index Organized Tables

11.5 LOBs in Index Organized Tables

Index Organized Tables (IOTs) support LOB and BFILE columns.

For the most part, SQL DDL, DML, and piecewise operations on LOBs in IOTs produce the same results as those for normal tables. The only exception is the default semantics of LOBs during creation. The main differences are:

Tablespace Mapping: By default, or unless specified otherwise, the LOB data and index segments are created in the tablespace in which the primary key index segments of the index organized table are created.
Inline as Compared to Out-of-Line Storage: By default, all LOBs in an index organized table created without an overflow segment are stored out of line. In other words, if an index organized table is created without an overflow segment, then the LOBs in this table have their default storage attributes as DISABLE STORAGE IN ROW. If you forcibly try to specify an ENABLE STORAGE IN ROW clause for such LOBs, then SQL raises an error.

On the other hand, if an overflow segment has been specified, then LOBs in index organized tables exactly mimic their semantics in conventional tables.

Example of Index Organized Table (IOT) with LOB Columns

Consider the following example:

CREATE TABLE iotlob_tab (c1 INTEGER PRIMARY KEY, c2 BLOB, c3 CLOB, c4 
VARCHAR2(20)) 
  ORGANIZATION INDEX 
    TABLESPACE iot_ts 
    PCTFREE 10 PCTUSED 10 INITRANS 1 MAXTRANS 1 STORAGE (INITIAL 4K) 
    PCTTHRESHOLD 50 INCLUDING c2 
  OVERFLOW 
    TABLESPACE ioto_ts 
    PCTFREE 10 PCTUSED 10 INITRANS 1 MAXTRANS 1 STORAGE (INITIAL 8K) LOB (c2) 
    STORE AS lobseg (TABLESPACE lob_ts DISABLE STORAGE IN ROW 
                     CHUNK 16384 PCTVERSION 10 CACHE STORAGE (INITIAL 2M) 
                     INDEX lobidx_c1 (TABLESPACE lobidx_ts STORAGE (INITIAL 4K)));

Executing these statements results in the creation of an index organized table iotlob_tab with the following elements:

A primary key index segment in the tablespace iot_ts,
An overflow data segment in tablespace ioto_ts
Columns starting from column C3 being explicitly stored in the overflow data segment
BLOB (column C2) data segments in the tablespace lob_ts
BLOB (column C2) index segments in the tablespace lobidx_ts
CLOB (column C3) data segments in the tablespace iot_ts
CLOB (column C3) index segments in the tablespace iot_ts
CLOB (column C3) stored in line by virtue of the IOT having an overflow segment
BLOB (column C2) explicitly forced to be stored out of line

Note:

If no overflow had been specified, then both C2 and C3 would have been stored out of line by default.

LOBs in Partitioned Index-Organized Tables

LOB columns and attributes can be stored in partitioned index-organized tables.

Index-organized tables can have LOBs stored as follows; however, partition maintenance operations, such as MOVE, SPLIT, and MERGE are not supported with:

VARRAY data types stored as LOB data types.
Abstract data types with LOB attributes.
Nested tables with LOB types.

Restrictions on Index Organized Tables with LOB Columns

The ALTER TABLE MOVE operation cannot be performed on an index organized table with a LOB column in parallel. Instead, use the NOPARALLEL clause to move the LOB column for such tables. For example:

ALTER TABLE t1 MOVE LOB(a) STORE AS (<tablespace users>) NOPARALLEL;