Lesson 4	Partitioning a table with LOBs
Objective	Define how to Partition a table with LOBs.

Partitioning Oracle Table with LOBs

Because of the way that both partitioning and LOBs can divide a table into several physical sections, it took a little longer for Oracle to support LOB data types in a partitioned table. Oracle has jumped that hurdle, and now you can create a partitioned table that contains LOB data types.
When specifying the partitions in the CREATE TABLE command, you also specify where the LOB is stored for each partition. This gives you maximum flexibility in storing the partitions and the LOBs. Move the large LOB data to a separate tablespace, so that queries not involving the LOB data are more efficient. In the following list of steps, you will create a partitioned table that contains LOB data.
Partitioning Table with Lob Data

Partitioning Table with LOB data

We have started the CREATE TABLE command for you. This is a table for storing a historical library of music. Type in the last column definition, the BLOB column, in the open line:
MUSIC BLOB)
We have added the storage line for the table, and now you must enter the storage line for the LOB. Type this information and press Enter:
```
LOB (MUSIC) STORE AS (CHUNK 4096) 
```
Now, just like the other types of partitioned tables, you specify that you will be creating range partitioning based on a certain column. In this example, the partitions are divided by date. Type in the following information and press Enter:
PARTITION BY RANGE(PUBLISH_DATE)
This line begins the definition of the first partition. For our example, the oldest music will be stored in the ELVIS_ERA partition. Type in the following information and press Enter:
(PARTITION ELVIS_ERA
The ELVIS_ERA partition will store music that was published earlier than 1960. To specify the values of the publish date for the partition, type this line and press Enter:
VALUES LESS THAN (TO_DATE('01-JAN-1960', 'DD-MON-YYYY'))

Let's assume that we have set up some special tablespaces just for this table. One of them is called ELVIS_TS, and it is going to store all the table data (except the LOB) for the ELVIS_ERA partition. Tell Oracle to use this tablespace by typing the following line and pressing Enter:
```
TABLESPACE ELVIS_TS
```
Another special tablespace, called ELVIS_MUSIC_TS was created earlier to store the LOB data related to the ELVIS_ERA partition. Tell Oracle to use this tablespace by typing the following line and pressing Enter:
LOB (MUSIC) store as (TABLESPACE ELVIS_MUSIC_TS),
The comma at the end of the previous line tells us that the definition of the first partition is now complete. Begin the second partition definition now. This partition is called BEATLES_ERA and stores music of the 1960s. Type the following line and press Enter:
PARTITION BEATLES_ERA
We have filled in the remaining specifications for the BEATLES_ERA partition. Notice the tablespace names in this partition. Now begin the third and final partition definition. This partition is called BEEGEES_ERA and stores music of the 1970s and beyond. Type the following line and press Enter:
PARTITION BEEGEES_ERA
We have filled in the remaining specifications for the BEEGEES_ERA partition. The closing parenthesis at the end of the partition definition marks the final partition. Execute the command by typing a forward slash (/) and pressing Enter.
You have successfully created a partitioned table containing LOB data.

It is easy to see how you might expand this kind of partitioning into more partitions. For example, in the simulation above, you might add ten-year sections for the 80s, 90s and the new millennium. The next lesson covers how to enable row movement in a partitioned table.

Partitioning and LOB Data

Unstructured data (such as images and documents) which is stored in a LOB column in the database can also be partitioned. When a table is partitioned, all the columns will reside in the tablespace for that partition, with the exception of LOB columns, which can be stored in their own tablespace. This technique is very useful when a table is comprised of large LOBs because they can be stored separately from the main data. This can be beneficial if the main data is being frequently updated but the LOB data isn't. For example, an employee record may contain a photo which is unlikely to change frequently. However, the employee personnel details (such as address, department, manager, and so on) could change. This approach also means that cheaper storage can be used for storing the LOB data and more expensive, faster storage used for the employee record.

Partitioning a Table Containing LOB Columns

LOBs are supported in RANGE partitioned, LIST partitioned, and HASH partitioned tables. Composite heap-organized tables can also have LOBs. You can partition a table containing LOB columns using the following techniques:

When the table is created using the PARTITION BY ... clause of the CREATE TABLE statement.
Adding a partition to an existing table using the ALTER TABLE ... ADD PARTITION clause.
Exchanging partitions with a table that already has partitioned LOB columns using the ALTER TABLE ... EXCHANGE PARTITION clause. Note that EXCHANGE PARTITION can only be used when both tables have the same storage attributes, for example, both tables store LOBs out-of-line.

Creating LOB partitions at the same time you create the table (in the CREATE TABLE statement) is recommended. If you create partitions on a LOB column when the table is created, then the column can hold LOBs stored either inline or out-of-line LOBs.
After a table is created, new LOB partitions can only be created on LOB columns that are stored out-of-line. Also, partition maintenance operations, SPLIT PARTITION and MERGE PARTITIONS, will only work on LOB columns that store LOBs out-of-line.

Partition Table with LOB Data

The following paragraph describes how to partition tables using LobData in Oracle. The code you created should look something like this after creating a partitioned table containing LOB data in Oracle.

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))
/

Here are suggested answers to your questions.

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.

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.