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
ROW. If you forcibly try to specify an
ROWclause 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
An overflow data segment in tablespace
Columns starting from column
C3being explicitly stored in the overflow data segment
C2) data segments in the tablespace
C2) index segments in the tablespace
C3) data segments in the tablespace
C3) index segments in the tablespace
C3) stored in line by virtue of the IOT having an overflow segment
C2) explicitly forced to be stored out of line
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
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;