Partitioning Tables  «Prev  Next»

Lesson 3Partitioning Object Tables
ObjectiveDefine how to Partition an Object Table.

Partitioning Object Tables

Oracle has the ability to partition object tables. Just like partitioning relational tables, you must define a column or list of columns in the object table that defines the division points for each partition. The column has to be a standard Oracle data type, such as NUMBER or VARCHAR2. The following series of images illustrates how to partition an object table.

Partitioned Object Tables in Oracle
1) An object table is based on an object type.
Oracle SQL*Plus Code: 
SQL> CREATE TYPE PLANT_TYPE AS OBJECT
  2  (PLANT_ID NUMBER,
  3   SPECIES VARCHAR2(50),
  4   VARIETY VARCHAR2(50),
  5   COMMON_NAME VARCHAR2(50))
  6  /
  
Type created.

SQL>

Accompanying Explanation: An object table is based on an object type. Here is the CREATE command that creates the object type for our example object table. Our partitions are going to be based on the COMMON_NAME attribute."*
Analysis: This code is defining an Oracle object type named `PLANT_TYPE`. The `CREATE TYPE` statement is used in Oracle PL/SQL to define a structured object with four attributes:
  1. PLANT_ID
    • NUMBER
  2. SPECIES
    • VARCHAR2(50)
  3. VARIETY
    • VARCHAR2(50)
  4. COMMON_NAME
    • VARCHAR2(50)
  • The final note suggests that an object table will be created based on this object type.
  • The COMMON_NAME attribute will be used for partitioning, meaning that Oracle will distribute data across partitions based on values in COMMON_NAME.
1) An object table is based on an object type.

2) This shows the beginning of the CREATE TABLE command, where we specify which object type is the basis for the object table.
Oracle SQL*Plus Code: 
SQL> CREATE TYPE PLANT_TYPE AS OBJECT
  2  (PLANT_ID NUMBER,
  3   SPECIES VARCHAR2(50),
  4   VARIETY VARCHAR2(50),
  5   COMMON_NAME VARCHAR2(50))
  6  /

Type created.

SQL> CREATE TABLE PARTITIONED_PLANT
  2  OF PLANT_TYPE
  3

Accompanying Explanation:
This shows the beginning of the CREATE TABLE command, where we specify which object type is the basis for the object table.
Analysis:
  • The first SQL statement creates an Oracle Object Type named PLANT_TYPE with four attributes (PLANT_ID, SPECIES, VARIETY, COMMON_NAME).
  • The second SQL statement starts creating an object table named PARTITIONED_PLANT based on the PLANT_TYPE object type.
  • The object table will store instances of the PLANT_TYPE object.
  • The statement is incomplete (as seen in the image). Likely, partitioning information will be added in subsequent lines.
2) This shows the beginning of the CREATE TABLE command, where we specify which object type is the basis for the object table.

3) Like the relational table, you specify storage requirements before you specify partitioning details.
3) Like the relational table, you specify storage requirements before you specify partitioning details.

4) We define the partitions. The last line in the command you see in this example specifies that you use range partitioning and the partitioning is based on the COMMON_NAME attribute.
4) We define the partitions. The last line in the command you see in this example specifies that you use range partitioning and the partitioning is based on the COMMON_NAME attribute.

5) Here you see the final three lines of the command, which describe the cutoff values for each partition. 
SQL> CREATE TABLE PARTITIONED_PLANT
OF PLANT_TYPE
TABLESPACE USERS PCTFREE 20
PARTITION BY RANGE (COMMON_NAME)
(PARTITION NAME_A_F VALUES LESS THAN ('G'),
 PARTITION NAME_G_P VALUES LESS THAN ('Q'),
 PARTITION NAME_Q_Z VALUES LESS THAN (MAXVALUE)
);

Additional Text Explanation: > Here you see the final three lines of the command, which describe the cutoff values for each partition. The `MAXVALUE` keyword tells Oracle8i that the last partition holds values from the previous partition’s cutoff point to the maximum value found in the table.
This image demonstrates how partitioning by range works in Oracle databases, specifically defining partitions based on the `COMMON_NAME` column, with values segmented alphabetically. The last partition (`NAME_Q_Z`) captures all values beyond `'Q'` up to the highest possible value using `MAXVALUE`. 5) Here you see the final three lines of the command, which describe the cutoff values for each partition.

Subtype Attribute for Partitioning

Let us look at another example where you use a subtype attribute for partitioning. In this case, you must base your partitioning on a subtype column rather than a column in the table's object type. The syntax is only slightly different: you must identify the table column and the subtype column in the PARTITION BY RANGE clause. 
CREATE TABLE PARTITIONED_CUSTOMER
OF CUSTOMER_TYPE
TABLESPACE USERS PCTFREE 20
PARTITION BY RANGE (FULL_ADDRESS.CITY)
(PARTITION NAME_A_F VALUES LESS THAN ('G'),
PARTITION NAME_G_P VALUES LESS THAN ('Q'),
PARTITION NAME_Q_Z VALUES LESS THAN
   (MAXVALUE))

This example uses the object types found in the Course Project and shows how to specify the CITY subtype column in the PARTITION BY RANGE clause. The next lesson covers how to partition a table with LOBs.
The following paragraph below discusses how to partition an object-organized table.

Create Partitioned Object Table

The SQL to create a partitioned object table. 
CREATE TYPE GAME_TYPE  AS OBJECT
(GAME_ID NUMBER,
 GAME_NAME VARCHAR2(50),
 CATEGORY VARCHAR2(50),
 DIFFICULTY VARCHAR2(10))
/
CREATE TABLE GAMES OF GAME_TYPE
TABLESPACE USERS PCTFREE 20
PARTITION BY RANGE (GAME_NAME)
(PARTITION NAME_A_K VALUES LESS THAN ('J'),
 PARTITION NAME_J_R VALUES LESS THAN ('S'),
 PARTITION NAME_S_Z VALUES LESS THAN (MAXVALUE))
/

SEMrush Software 3 SEMrush Banner 3