6
LOBs and Partitioned Tables

Using LOBs in Partitions

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 1, "Introduction to Working With LOBs", 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 will 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 schema illustrated in Figure 6-1.

Figure 6-1 Schema Design for Inclusion of PHOTO_REF Reference

PRESNAME: A column on the president's name 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: This column 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: This column 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: This column 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 Roosevelt - Inauguration".

SCRIPT: This column 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: This column contains the name of the actor reading the script.

MUSIC: This column 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 that 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.

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-ops, 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);

Populating the Script CLOB and Photo BLOB

The documentary producers have found a photograph Bill Clinton during his trip to Florida on 22 March 1993. They will add it to the PresidentPhoto_tab table, and then fill the PresPhoto column with the photograph BLOB data and the Script column with the text CLOB data. This section illustrates populating the Script CLOB and the Photo BLOB.

Assume that the following directory objects for the music audio files and the presidential photographs were already created,

CREATE DIRECTORY Music_dir as '/audio/presidents';
CREATE DIRECTORY Image_dir as '/image/presidents';

and that READ permission has been granted to the user who will use it:

GRANT READ ON DIRECTORY Music_dir TO a_user;
GRANT READ ON DIRECTORY Image_dir TO a_user;

INSERT INTO PresidentPhoto_tab VALUES (
   'RichardNixon', TO_DATE('22-mar-1973', 'DD-MON-YYYY'), 'NixonFlorida1993',
   EMPTY_BLOB(), EMPTY_CLOB(), 'Warren Beatty', BFILENAME('MUSIC_DIR', 
   'TropicalMusic'));

Populating the BLOB:

The following code segment uses the LOADFROMFILE command to populate the PresPhoto BLOB with data:

CREATE OR REPLACE PROCEDURE loadPartLOBFromBFILE_proc IS
   Dest_loc   BLOB; 
   Src_loc    BFILE := BFILENAME('IMAGE_DIR', 'FloridaTrip');
   Amount     INTEGER := 4000;
BEGIN 
   /* Select the LOB from the partitioned table: */ 
   SELECT PresPhoto INTO Dest_loc FROM PresidentPhoto_tab WHERE 
      PresName  = 'RichardNixon' AND 
      PhotoName = 'NixonFlorida1993' 
      FOR UPDATE;

   /* Opening the LOB is optional: */ 
   DBMS_LOB.OPEN (Dest_loc, DBMS_LOB.LOB_READWRITE); 
   /* Opening the BFILE is mandatory */
   DBMS_LOB.OPEN (Src_loc, DBMS_LOB.LOB_READONLY);

   DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount);

   /* Closing the LOB is mandatory if you have opened it */
   DBMS_LOB.CLOSE(Dest_loc);
   DBMS_LOB.CLOSE(Src_loc);

     
COMMIT;

     END;

Populate the CLOB:

The following code segment uses the CHECKIN method to load data into the Script CLOB:

CREATE OR REPLACE PROCEDURE checkinPartLOB_proc IS
   Lob_loc       CLOB; 
   Buffer        VARCHAR2(32767);
   Amount        BINARY_INTEGER := 32767;
   Position      INTEGER := 1;
   i             INTEGER;
BEGIN 
   /* Select the LOB from the partitioned table: */ 
   SELECT script INTO Lob_loc FROM PresidentPhoto_tab where 
      PresName = 'RichardNixon' AND 
      PhotoName = 'NixonFlorida1993';
     


        /* Opening the LOB is optional: */ 
   DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE); 

   /* Fill the buffer with data */

   FOR i IN 1..3 LOOP 
      /* Write data: */ 
      DBMS_LOB.WRITE (Lob_loc, Amount, Position, Buffer); 
      /* Fill in more data: */ 
      Position := Position + Amount;
   END LOOP;

   /* Closing the LOB is mandatory if you have opened it */
   DBMS_LOB.CLOSE(Lob_loc);
   COMMIT;

EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Operation failed');
END;

Reading the LOB Value:

The following code segment uses the CHECKOUT command to READ the LOB value:

CREATE OR REPLACE PROCEDURE checkoutPartLOB_proc is
   Lob_loc       CLOB; 
   Buffer        VARCHAR2(32767);
   Amount        BINARY_INTEGER := 32767;
   Position      INTEGER := 1;
BEGIN 
   /* Select the LOB from the partitioned table: */ 
   SELECT Script INTO Lob_loc FROM PresidentPhoto_tab WHERE 
      PresName = 'RichardNixon' AND 
      PhotoName = 'NixonFlorida1993';

   /* Opening the LOB is optional: */ 
   DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY); 

   LOOP 
      /* Read data: */ 
      DBMS_LOB.READ (Lob_loc, Amount, Position, Buffer); 
      /* Process the data in the buffer. */ 
      Position := Position + Amount;
   END LOOP;

   /* Closing the LOB is mandatory if you have opened it */
   DBMS_LOB.CLOSE(Lob_loc);

EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('End of data');
END;

6 LOBs and Partitioned Tables