LOBs in Partitioned Index-Organized Tables

Oracle9i introduces support for LOB, VARRAY columns stored as LOBs, and BFILEs in partitioned index-organized tables. The behavior of LOB columns in these tables is similar to that of LOB columns in conventional (heap-organized) partitioned tables, except for the following few minor differences:

• Tablespace Mapping. In the absence of a tablespace specification for a LOB column at the individual partition level or at the table level, the LOB's data and index segments for that partition are created in the tablespace in which the primary key index segment of the corresponding partition of the index-organized table is created. In other words, they are equi-partitioned and collocated with their corresponding primary key index segments.

• Inline vs Out-of-line. If the partitioned index-organized table is defined without an overflow segment, then a LOB column cannot be defined or altered to be created inline. This requirement is the same as that for a non-partitioned index-organized table.

LOB columns are supported only in range partitioned index-organized tables.

Example of LOB Columns in Partitioned Index-Organized Tables

In this section, we'll highlight the differences listed above for LOBs in partitioned index-organized tables with the Multimedia_Tab example described in Appendix B.

Assume that Multimedia-tab has been created as a range-partitioned index-organized table, as follows:

CREATE TABLE Multimedia_tab (
  CLIP_ID    INTEGER PRIMARY KEY,
  CLIP_DATE  DATE,
  STORY      CLOB,
  FLSUB      NCLOB,
  PHOTO      BFILE,
  FRAME      BLOB,
  SOUND      BLOB,
  ...
  )
  ORGANIZATION INDEX
    TABLESPACE TBS_IDX
  OVERFLOW
    TABLESPACE TBS_OVF
  LOB (FRAME, S0UND) STORE AS (TABLESPACE TBS_LOB)
  PARTITION BY RANGE (CLIP_DATE)
  (PARTITION Jan_Multimedia_tab VALUES LESS THAN (01-FEB-2000)
     LOB (STORY) STORE AS (TABLESPACE TBS_LOB),
   PARTITION Feb_Multimedia_tab VALUES LESS THAN (01-MAR-2000)
     LOB (FLSUB) STORE AS (TABLESPACE TBS_LOB
                           ENABLE STORAGE IN ROW)
  );

In the above example, the LOB columns FRAME and SOUND will be stored in the tablespace TBS_LOB across all the partitions.

• In the partition, Jan_Multimedia_tab, the column STORY is stored in TBS_LOB because of the partition-specific override, but the column FLSUB will be stored in TBS_IDX, that is, the tablespace of its primary key index segment.

• In the partition, Feb_Multimedia_tab, the column STORY is stored in TBS_IDX, and the column FLSUB will be stored in TBS_LOB by virtue of the partition-specific override.

  Note: Since Multimedia_tab was created with an overflow segment, the LOB columns are allowed to be stored inline. If that was not the case, the "ENABLE STORAGE IN ROW" clause for FLSUB in Feb_Multimedia_tab, would have generated an error.

The inheritance semantics for the rest of the LOB physical attributes are in line with LOBs in conventional tables.

See Also: Oracle9i SQL Reference, for a description of the lob_storage_clause in CREATE TABLE.