Partitioning Tables  «Prev  Next»

Partition a table with LOB Data

The code you created should look something like this:
tcreated a partitioned table containing LOB data.

CREATE TABLE ANIMAL_BEHAVIOR
(ANIMAL VARCHAR2(30), 
 CLIP_DATE DATE,
 BEHAVIOR_DESCRIPTION VARCHAR2(500),
 SCIENTIST_NAME VARCHAR2(30),
 AV_CLIP BLOB)
   STORAGE (INITIAL 100K  NEXT 100K)
   LOB (AV_CLIP) STORE AS (CHUNK 4096)
PARTITION BY RANGE(CLIP_DATE)
(PARTITION REALLY_OLD_CLIPS
   VALUES LESS THAN (TO_DATE('01-JUL-1951', 'DD-MON-YYYY'))
   TABLESPACE OLD_OLDIES_TS   
   LOB (AV_CLIP) STORE AS (TABLESPACE OLDIES_CLIPS_TS), 
PARTITION SOMEWHAT_OLD_CLIPS

   VALUES LESS THAN (TO_DATE('01-JAN-1967', 'DD-MON-YYYY')) 

   TABLESPACE OLD_TS

   LOB (AV_CLIP) store as (TABLESPACE PRE_66_CLIPS_TS), 

PARTITION POST_66_ERA

   VALUES LESS THAN (MAXVALUE)

   TABLESPACE NEW_TS

   LOB (AV_CLIP) store as (TABLESPACE POST_66_CLIPS_TS))
/ 

Now answer this….

Here are suggested answers to your questions.
  1. What is the advantage of being able to partition an index-organized table?

Answer 1: A partitioned index-organized table allows a query that uses the primary key in its WHERE clause to run faster because the uery can skip directly to the partition containing the range of data it is searching for.
  1. What is the purpose of separating the LOB portion of a partition to a separate tablespace?

Answer 2: Because LOB data tends to be large, it is advantageous to move it to a separate tablespace. Queries that do not involve the LOB data are more efficient.