11.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.
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 theCREATE 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.
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 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.
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
changing the 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
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;
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.
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;