|Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 1 (9.0.1)
Part Number A88879-01
Modeling and Design, 10 of 21
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 Appendix B, "The Multimedia Schema Used for Examples in This Manual", 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".
Column Name Description
President's name. This lets the documentary producers select data for clips organized around specific presidents.
PRESNAMEis also chosen as a primary key because it holds unique values.
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.
This column is chosen as the partition key to make it easier to add partitions and to perform
SPLITsof 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.
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".
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.
Contains the name of the actor reading the script.
Contains background music to be played during the viewing of the photographs.
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.
In the following examples, 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.
You may need to set up the following data structures for certain examples to work:
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) );
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:
As a part of upgrading from Oracle8.0 to 8.1 or higher, 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;
To account for Richard Nixon's second term, a new partition was added to
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);
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);
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));
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