1. SecureFiles and Large Objects Developer's Guide
  2. Persistent LOBs: Advanced DDL
  3. LOBs in Partitioned Tables

12.4 LOBs in Partitioned Tables

Partitioning can simplify the manageability of large database objects. This section discusses various aspects of LOBs in partitioned tables.

Very large tables and indexes can be decomposed into smaller and more manageable pieces called partitions, which are entirely transparent to an application. You can partition tables that contain LOB columns. All partitioning schemes supported by Oracle are fully supported on LOBs.

See Also:

Partitions_ Views_ and Other Schema Objects

Partitioning for All Databases

LOBs can take advantage of all of the benefits of partitioning including the following:

  • LOB segments can be spread between several tablespaces to balance I/O load and to make backup and recovery more manageable.
  • LOBs in a partitioned table become easier to maintain.
  • LOBs can be partitioned into logical groups to speed up operations on LOBs that are accessed as a group.

The following section describes some of the ways you can manipulate LOBs in partitioned tables.

Parent topic: Persistent LOBs: Advanced DDL

12.4.1 Partitioning a Table Containing LOB Columns

All partitioning schemes supported by Oracle are fully supported on LOBs. This section discusses the partitioning of tables with LOB columns.

You can partition a table containing LOB columns using any of the following techniques:

  • When the table is created using the PARTITION BY ... clause of the CREATE TABLE statement.
  • Adding a partition to an existing table using the ALTER TABLE ... ADD PARTITION clause.

The data dictionary views USER_LOB_PARTITIONS, ALL_LOB_PARTITIONS and DBA_LOB_PARTITIONS provide partition specific information for a LOB column.

Different partitions can have different inline sizes. This is useful if you want to EXCHANGE a new table into a partition of an existing table. If a partition level inline size is not specified, the column’s table level default is used. In the case of composite partitioning, the sub-partition-level inline size takes precedence over the partition-level inline size, which takes precedence over the table-level inline size. During new partition creation, if inline size values are not specified, the table level defaults are used. Inline size values are never NULL.

Example 12-15 A partitioned table with LOB columns:

CREATE TABLE print_media
    ( product_id        NUMBER(6),
      ad_id             NUMBER(6),
      ad_sourcetext     CLOB)
    LOB (ad_sourcetext)  STORE AS SECUREFILE (TABLESPACE tbs_2)
    PARTITION BY RANGE(product_id)
    (PARTITION P1 VALUES LESS THAN (1000)
         LOB (ad_sourcetext) STORE AS BASICFILE (TABLESPACE tbs_1),
     PARTITION P2 VALUES LESS THAN (2000)
         LOB (ad_sourcetext) STORE AS (TABLESPACE tbs_2 COMPRESS HIGH),
     PARTITION P3 VALUES LESS THAN (3000));

See Also:

Summary of CREATE TABLE LOB Storage Parameters for Securefile LOBs

Example 12-16 A partitioned table with different inline sizes for LOB columns

CREATE TABLE print_media
    ( product_id        NUMBER(6),
      ad_content        BLOB,
      ad_sourcetext     CLOB)
      LOB (ad_content) STORE AS SECUREFILE (ENABLE STORAGE IN ROW)
      LOB (ad_sourcetext) STORE AS SECUREFILE (ENABLE STORAGE IN ROW 8000) TABLESPACE tbs_1
    PARTITION BY RANGE(product_id)
    (PARTITION P1 VALUES LESS THAN (1000)
        LOB (ad_sourcetext) STORE AS SECUREFILE (ENABLE STORAGE IN ROW 4000),
    PARTITION P2 VALUES LESS THAN (2000)
        LOB (ad_sourcetext) STORE AS (ENABLE STORAGE IN ROW 8000 COMPRESS HIGH),
    PARTITION P3 VALUES LESS THAN (3000)
        LOB (ad_content) STORE AS (DISABLE STORAGE IN ROW));

Parent topic: LOBs in Partitioned Tables

12.4.2 Default LOB Storage Attributes

This section discusses the default LOB storage attributes.

In the above example, the default storage attribute for LOB column ad_sourcetext is mentioned as "STORE AS SECUREFILE (TABLESPACE tbs_2)". This means that if no LOB storage clause is provided for any partition, this default will be used. In this example, partition P3 uses tablespace tbs_2 since no LOB storage is specified. Similarly, SECUREFILE is the default storage and is used by partitions P2 and P3, but partition P1 overrides it to specify BasicFile storage.

The dictionary views USER_PART_LOBS, ALL_PART_LOBS and DBA_PART_LOBS provide information on default LOB storage options for a LOB column in a table.

The table level default LOB storage attribute can be changed, as shown in the example below:

ALTER TABLE print_media MODIFY DEFAULT ATTRIBUTES  LOB (ad_sourcetext)  
    (TABLESPACE  tbs_1);

The change in the default attribute will not affect the existing partitions. Any new partitions created without LOB storage clause will inherit the default values for that column.

Parent topic: LOBs in Partitioned Tables

12.4.3 Partition Maintenance Operation

This section discusses maintenance operations on partitioned tables with LOB columns.

All partitioning maintenance operations are supported with LOB columns. Here are some examples:

Example 12-17 Adding Partition containing LOBs

ALTER TABLE print_media ADD PARTITION P4 VALUES LESS THAN (4000)
         LOB (ad_sourcetext) STORE AS SECUREFILE(TABLESPACE tbs_2);

Example 12-18 Modifying Partition Containing LOBs

ALTER TABLE print_media MODIFY PARTITION P3 LOB(ad_sourcetext)
     (RETENTION AUTO);

Example 12-19 Moving Partition Containing LOBs

ALTER TABLE print_media MOVE PARTITION P1 LOB(ad_sourcetext)  
    STORE AS (TABLESPACE tbs_3 COMPRESS LOW);

The example above moves a LOB partition into a different tablespace, which can be useful if the tablespace is no longer large enough to hold the partition. Move partition can also be used to perform other operations that require moving the LOB data, such as performing a COMPRESS operation on the LOB, or changing the ENABLE / DISABLE STORAGE IN ROW option.

Example 12-20 Splitting Partitions Containing LOBs

You can split a partition containing LOBs into two using the ALTER TABLE ... SPLIT PARTITION clause. Doing so permits you to place one or both new partitions in a new tablespace. For example: 

ALTER TABLE print_media SPLIT PARTITION  P1 AT(500) into
(PARTITION P1A LOB(ad_sourcetext) STORE AS (TABLESPACE tbs_1),
PARTITION P1B LOB(ad_sourcetext) STORE AS (TABLESPACE tbs_2)) UPDATE INDEXES;

Example 12-21 Merging Partitions Containing LOBs

Merging partitions is useful for reclaiming unused partition space. For example:

ALTER TABLE print_media MERGE PARTITIONS P1A, P1B INTO PARTITION P1;

Example 12-22 Exchange Partition containing LOB column with non-partitioned table

Exchanging partitions with a table that has partitioned LOB columns using the ALTER TABLE ... EXCHANGE PARTITION clause. Exchange partition is a powerful tool to change new data / partitions to a newer storage format without the costly operation of migrating old data. You can exchange partition with LOB data having different storage option, e.g. partition p1 of BasicFile data in Example 11-15 can be exchanged with non-partitioned table with LOB column stored in SecureFile Compressed form: 

CREATE TABLE print_media_nonpart
    ( product_id NUMBER(6),
      ad_id NUMBER(6),
      ad_sourcetext CLOB)
      LOB (ad_sourcetext) STORE AS SECUREFILE (COMPRESS HIGH);

ALTER TABLE print_media  EXCHANGE PARTITION p1 WITH TABLE print_media_nonpart;

Parent topic: LOBs in Partitioned Tables

12.4.4 Creating an Index on a Table Containing Partitioned LOB Columns

To improve the performance of queries, you can create local or global indexes on partitioned LOB columns.

Only function-based and domain indexes are supported on LOB columns. Other types of indexes, such as unique indexes are not supported with LOBs.

For example: 

CREATE INDEX ad_sourcetext_idx_sql on print_media (to_char(substr(ad_sourcetext,1,10)))
      GLOBAL;
CREATE INDEX ad_sourcetext_idx_sql on print_media (to_char(substr(ad_sourcetext,1,10)))
      LOCAL;

Parent topic: LOBs in Partitioned Tables