Manipulating LOBs in Partitioned Tables

You can partition tables with LOBs. As a result, LOBs can take advantage of all of the benefits of partitioning. For example, 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 also become easier to maintain.

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

As an extension to the example multimedia application described in Chapter 8, "Sample Application", let us suppose that makers of a documentary are producing multiple clips relating to different Presidents of the United States. The clips consist of photographs of the presidents accompanied by spoken text and background music. The photographs come from the PhotoLib_Tab archive. To make the most efficient use of the presidents' photographs, they are loaded into a database according to the structure illustrated in Figure 7-1.

The columns in Multimedia_tab are described in Table 7-5, "Multimedia_tab Columns".

Figure 7-1 Table Multimedia_tab structure Showing Inclusion of PHOTO_REF Reference

Table 7-5 Multimedia_tab Columns

Column Name	Description
PRESNAME	President's name. This lets the documentary producers select data for clips organized around specific presidents. PRESNAME is also chosen as a primary key because it holds unique values.
PRESPHOTO	Contains photographs in which a president appears. This category also contains photographs of paintings and engravings of presidents who lived before the advent of photography.
PHOTODATE	Contains the date on which the photograph was taken. In the case of presidents who lived before the advent of photography, PHOTODATE pertains to the date when the painting or engraving was created. This column is chosen as the partition key to make it easier to add partitions and to perform MERGEs and SPLITs of the data based on some given date such as the end of a president's first term. This will be illustrated later in this section.
PHOTONAME	Contains the name of the photograph. An example name might be something as precise as "Bush Addresses UN - June 1990" or as general as "Franklin Rooseveld - Inauguration".
SCRIPT	Contains written text associated with the photograph. This could be text describing the event portrayed by the photograph or perhaps segments of a speech by the president.
ACTOR	Contains the name of the actor reading the script.
MUSIC	Contains background music to be played during the viewing of the photographs.

Creating and Partitioning a Table Containing LOB Data

To isolate the photographs associated with a given president, a partition is created for each president by the ending dates of their terms of office. For example, a president who served two terms would have two partitions: the first partition bounded by the end date of the first term and a second partition bounded by the end date of the second term.

Note: In the following examples, the extension 1 refers to a president's first term and 2 refers to a president's second term. For example, GeorgeWashington1_part refers to the partition created for George Washington's first term and RichardNixon2_part refers to the partition created for Richard Nixon's second term.

Note: You may need to set up data structures for certain examples to work; such as: CONNECT system/manager GRANT CREATE TABLESPACE, DROP TABLESPACE TO scott; CONNECT scott/tiger CREATE TABLESPACE EarlyPresidents_tbs DATAFILE 'disk1:moredata01' SIZE 1M; CREATE TABLESPACE EarlyPresidentsPhotos_tbs DATAFILE 'disk1:moredata99' SIZE 1M; CREATE TABLESPACE EarlyPresidentsScripts_tbs DATAFILE 'disk1:moredata03' SIZE 1M; CREATE TABLESPACE RichardNixon1_tbs DATAFILE 'disk1:moredata04' SIZE 1M; CREATE TABLESPACE Post1960PresidentsPhotos_tbs DATAFILE 'disk1:moredata05' SIZE 1M; CREATE TABLESPACE Post1960PresidentsScripts_tbs DATAFILE 'disk1:moredata06' SIZE 1M; CREATE TABLESPACE RichardNixon2_tbs DATAFILE 'disk1:moredata07' SIZE 1M; CREATE TABLESPACE GeraldFord1_tbs DATAFILE 'disk1:moredata97' SIZE 1M; CREATE TABLESPACE RichardNixonPhotos_tbs DATAFILE 'disk1:moredata08' SIZE 2M; CREATE TABLESPACE RichardNixonBigger2_tbs DATAFILE 'disk1:moredata48' SIZE 2M; CREATE TABLE Mirrorlob_tab( PresName VARCHAR2(30), PhotoDate DATE, PhotoName VARCHAR2(30), PresPhoto BLOB, Script CLOB, Actor VARCHAR2(30), Music BFILE);

CREATE TABLE Presidentphoto_tab(PresName VARCHAR2(30), PhotoDate DATE,
                                PhotoName VARCHAR2(30), PresPhoto BLOB, 
                                Script CLOB, Actor VARCHAR2(30), Music BFILE)
   STORAGE (INITIAL 100K  NEXT 100K PCTINCREASE 0)
   LOB (PresPhoto) STORE AS (CHUNK 4096)
   LOB (Script) STORE AS (CHUNK 2048)
   PARTITION BY RANGE(PhotoDate)
(PARTITION GeorgeWashington1_part
   /* Use photos to the end of Washington's first term */
   VALUES LESS THAN (TO_DATE('19-mar-1792', 'DD-MON-YYYY'))
   TABLESPACE EarlyPresidents_tbs
   LOB (PresPhoto) store as (TABLESPACE EarlyPresidentsPhotos_tbs)
   LOB (Script) store as (TABLESPACE EarlyPresidentsScripts_tbs),
PARTITION GeorgeWashington2_part
   /* Use photos to the end of Washington's second term */
   VALUES LESS THAN (TO_DATE('19-mar-1796', 'DD-MON-YYYY'))
   TABLESPACE EarlyPresidents_tbs
   LOB (PresPhoto) store as (TABLESPACE EarlyPresidentsPhotos_tbs)
   LOB (Script) store as (TABLESPACE EarlyPresidentsScripts_tbs),
PARTITION JohnAdams1_part
   /* Use photos to the end of Adams' only term */
   VALUES LESS THAN (TO_DATE('19-mar-1800', 'DD-MON-YYYY'))
   TABLESPACE EarlyPresidents_tbs
   LOB (PresPhoto) store as (TABLESPACE EarlyPresidentsPhotos_tbs)
   LOB (Script) store as (TABLESPACE EarlyPresidentsScripts_tbs),
/* ...intervening presidents... */
PARTITION RichardNixon1_part
   /* Use photos to the end of Nixon's first term */
   VALUES LESS THAN (TO_DATE('20-jan-1972', 'DD-MON-YYYY'))
   TABLESPACE RichardNixon1_tbs
   LOB (PresPhoto) store as (TABLESPACE Post1960PresidentsPhotos_tbs)
   LOB (Script) store as (TABLESPACE Post1960PresidentsScripts_tbs)
);

Creating an Index on a Table Containing LOB Columns

To improve the performance of queries which access records by a President's name and possibly the names of photographs, a UNIQUE local index is created:

CREATE UNIQUE INDEX PresPhoto_idx 
   ON PresidentPhoto_tab (PresName, PhotoName, Photodate) LOCAL;

Exchanging Partitions Containing LOB Data

As a part of upgrading from Oracle8.0 to 8.1, data was exchanged from an existing non-partitioned table containing photos of Bill Clinton's first term into the appropriate partition:

ALTER TABLE PresidentPhoto_tab EXCHANGE PARTITION RichardNixon1_part 
   WITH TABLE Mirrorlob_tab INCLUDING INDEXES;

Adding Partitions to Tables Containing LOB Data

To account for Richard Nixon's second term, a new partition was added to PresidentPhoto_tab:

ALTER TABLE PresidentPhoto_tab ADD PARTITION RichardNixon2_part 
   VALUES LESS THAN (TO_DATE('20-jan-1976', 'DD-MON-YYYY'))
   TABLESPACE RichardNixon2_tbs
   LOB (PresPhoto) store as (TABLESPACE Post1960PresidentsPhotos_tbs)
   LOB (Script) store as (TABLESPACE Post1960PresidentsScripts_tbs);

Moving Partitions Containing LOBs

During his second term, Richard Nixon had so many photo-opportunities, that the partition containing information on his second term is no longer adequate. It was decided to move the data partition and the corresponding LOB partition of PresidentPhoto_tab into a different tablespace, with the corresponding LOB partition of Script remaining in the original tablespace:

ALTER TABLE PresidentPhoto_tab MOVE PARTITION RichardNixon2_part 
   TABLESPACE RichardNixonBigger2_tbs
   LOB (PresPhoto) STORE AS (TABLESPACE RichardNixonPhotos_tbs);

Splitting Partitions Containing LOBs

When Richard Nixon was re-elected for his second term, a partition with bounds equal to the expected end of his term (20-jan-1976) was added to the table (see above example.) Since Nixon resigned from office on 9 August 1974, that partition had to be split to reflect the fact that the remainder of the term was served by Gerald Ford:

ALTER TABLE PresidentPhoto_tab SPLIT PARTITION RichardNixon2_part
   AT (TO_DATE('09-aug-1974', 'DD-MON-YYYY'))
   INTO (PARTITION RichardNixon2_part, 
      PARTITION GeraldFord1_part TABLESPACE GeraldFord1_tbs
         LOB (PresPhoto) STORE AS (TABLESPACE Post1960PresidentsPhotos_tbs)
         LOB (Script) STORE AS (TABLESPACE Post1960PresidentsScripts_tbs));

Merging Partitions Containing LOBs

Despite the best efforts of the documentary producers in searching for photographs of paintings or engravings of George Washington, the number of photographs that were found was inadequate to justify a separate partition for each of his two terms. Accordingly, it was decided to merge these two partition into one named GeorgeWashington8Years_part:

ALTER TABLE PresidentPhoto_tab 
   MERGE PARTITIONS GeorgeWashington1_part, GeorgeWashington2_part 
   INTO PARTITION GeorgeWashington8Years_part TABLESPACE EarlyPresidents_tbs
      LOB (PresPhoto) store as (TABLESPACE EarlyPresidentsPhotos_tbs)
      LOB (Script) store as (TABLESPACE EarlyPresidentsScripts_tbs);