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 LOB
s. As a result, LOB
s 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 LOB
s 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".
Table 7-5 Multimedia_tab Columns
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.
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:
CREATE UNIQUE INDEX PresPhoto_idx ON PresidentPhoto_tab (PresName, PhotoName, Photodate) LOCAL;
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 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);
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 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);
|
![]() Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|