LOBs in Index Organized Tables (IOT)

Index Organized Tables (IOT) now support internal and external LOB columns. The SQL DDL, DML and piece wise operations on LOBs in index organized tables exhibit the same behavior as that observed in conventional tables. The only exception is the default behavior of LOBs during creation. The main differences are:

• Tablespace Mapping: By default, or unless specified otherwise, the LOB's data and index segments will be 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 will be stored out of line. In other words, if an index organized table is created without an overflow segment, 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, SQL will raise an error.

  On the other hand, if an overflow segment has been specified, LOBs in index organized tables will exactly mimic their behavior in conventional tables (see "Defining Tablespace and Storage Characteristics for Internal LOBs").

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 1 PCTVERSION 1 CACHE STORAGE (INITIAL 2m) 
                     INDEX LOBIDX_C1 (TABLESPACE lobidx_ts STORAGE (INITIAL 
                                                                    4K))); 

Executing these statements will result 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, both C2 and C3 would have been stored out of line by default.

Other LOB features, such as BFILEs and varying character width LOBs, are also supported in index organized tables, and their usage is the same as conventional tables.

Note: Support for LOBs in partitioned index organized tables will be provided in a future release.