|Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 1 (9.0.1)
Part Number A88879-01
Modeling and Design, 9 of 21
Index Organized Tables (IOT) now support internal and external LOB columns. The SQL DDL, DML and piece wise operations on LOBs in IOT exhibit the same behavior as for conventional tables. The only exception is the default behavior of LOBs during creation. The main differences are:
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.
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
ROW. If you forcibly try to specify an
ROWclause 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").
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:
C3being explicitly stored in the overflow data segment
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
LOB features, such as
BFILEs and varying character width
LOBs, are also supported in index organized tables, and their usage is the same as for conventional tables.