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.
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.
11.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
Adding a partition to an existing table using the
ALTER TABLE ... ADD PARTITIONclause.
The data dictionary views
provide partition specific information for a LOB column.
Example 11-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));
11.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
P3, but partition
P1 overrides it to specify BasicFile storage.
The dictionary views
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.
11.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 11-16 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 11-17 Modifying Partition Containing LOBs
ALTER TABLE print_media MODIFY PARTITION P3 LOB(ad_sourcetext) (RETENTION AUTO);
Example 11-18 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
ENABLE / DISABLE STORAGE IN ROW option.
Example 11-19 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 11-20 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 11-21 Exchange Partition containing LOB column with non-partitioned table
Exchanging partitions with a table that has partitioned LOB columns
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;
11.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.
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;