More Partitioning Choices

Learn when and how to use new partitioning schemes in Oracle Database 11g.

September/October 2007

The September/October 2006 issue of Oracle Magazine included an article I wrote on the various types of database partitioning and how to choose a partitioning strategy to meet your specific requirements. In Oracle Database 11g, the partitioning schemes have been greatly expanded to offer more functionality, including the ability to define new composite partitioning, choose a partition interval, specify a foreign key to inherit the partitioning key of its parent table, and partition on virtual columns.

Consider a hypothetical company, Acme Hotels, for which you are building a hotel reservation system. One core table, named RES, stores the reservation information. Here are the columns of the RES table:

RES_ID     NUMBER
RES_DATE   DATE
HOTEL_ID   NUMBER(3)
GUEST_ID   NUMBER

The res_id, res_date, hotel_id, and guest_id columns refer to a unique ID number for the reservation, the date for which the reservation was made, the unique ID of the hotel for which the reservation was made, and the unique identifier of the guest who made the reservation, respectively. Because most users query on the res_date column and it is also used to identify records for partitioning, you decide to range-partition the table on that column with a partition per quarter, as shown in Listing 1.

There can be many transactions for a specific reservation, and each record is uniquely identified by a trans_id. Because TRANS is a child table of RES, there is a foreign key on the TRANS.res_id column, pointing to the RES table. Because the TRANS table has the same archival requirements as the RES table, you want to partition it in exactly the same way—range-partition on res_date, with one partition per quarter.

Code Listing 1: Creation of RES table

create table res (
   res_id      number primary key not null,
   res_date   date,
   hotel_id   number(3),
   guest_id   number
)
partition by range (res_date) (
   partition p1 values less than (to_date('01/01/2007','mm/dd/yyyy')),
   partition p2 values less than (to_date('04/01/2007','mm/dd/yyyy')),
   partition p3 values less than (to_date('07/01/2007','mm/dd/yyyy')),
   partition p4 values less than (to_date('10/01/2007','mm/dd/yyyy')),
   partition pm values less than (maxvalue)
);

Next, you want to create a table to hold the transactions resulting from the reservations. The table, called TRANS, looks like this:

TRANS_ID      NUMBER
RES_ID        NUMBER
TRANS_DATE    DATE
AMT           NUMBER

But there is a problem: the TRANS table does not have a res_date column, so how can you partition on a column that does not exist?

Enter Oracle Database 11g.

Oracle Database 11g provides a very useful new feature: referential partitioning. So instead of adding a res_date column to the TRANS table and specifying the partitioning clause, as you did for RES in Listing 1, you can specify a simple PARTITION BY REFERENCE clause in Oracle Database 11g, as shown in Listing 2. You must pass the foreign key constraint name as an argument that tells how the references are established. For instance, in this case, you are creating referential partitions on TRANS, referencing the foreign key as FK_TRANS_01, which points to the parent table RES. The TRANS table will inherit the partitioning strategy of the RES table, even though the partitioning column is not present in TRANS. Referential partitioning essentially instructs Oracle Database to equipartition the child table (TRANS, in this example) in exactly the same way as the parent table (RES).

Code Listing 2: Creating TRANS table, using reference partitioning

create table trans (
  trans_id     number not null,
  res_id        number not null,
  trans_date  date not null,
  amt           number,
  constraint fk_trans_01
    foreign key (res_id)
    references res
)
partition by reference
   (fk_trans_01);

You can see how the referential partitioning has been set up, by querying the USER_PART_TABLES dictionary view, as shown in Listing 3. The partitioning_type column shows the type of partitioning scheme; in Listing 3, the type is REFERENCE, and the ref_ptn_constraint_name column shows the foreign key constraint name, FK_TRANS_01, when the partition type is REFERENCE.

Code Listing 3: Checking for foreign key in reference partitioning

SQL> select table_name, partitioning_type, ref_ptn_constraint_name
  2    from user_part_tables
  3    where table_name in ('RES', 'TRANS');
 TABLE_NAME         PARTITIONING_TYP      REF_PTN_CONSTRAINT_NAME
------------        ------------------    -----------------------
 RES                RANGE
TRANS               REFERENCE                    FK_TRANS_01

To check the boundaries of the partitions, query the USER_TAB_PARTITIONS dictionary view, as shown in Listing 4. Note that the boundaries of the partitions of the TRANS child table, shown in the HIGH_VALUE column, are all null . This indicates that the boundaries are the same as those of the partitions of the RES parent table and are not independently defined.

Code Listing 4: Checking partitions of tables

SQL> select table_name, partition_name, high_value
  2    from user_tab_partitions
  3    where table_name in ('RES', 'TRANS');
TABLE_NAME   PARTITION_NAME       HIGH_VALUE
----------   --------------       ----------------------------------------
RES          P1                   TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-M
                                  M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
RES          P2                   TO_DATE(' 2007-04-01 00:00:00', 'SYYYY-M
                                  M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
RES          P3                   TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-M
                                  M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
RES          P4                   TO_DATE(' 2007-10-01 00:00:00', 'SYYYY-M
                                  M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
RES          PM                         MAXVALUE
TRANS        P1
TRANS        P2
TRANS        P3
TRANS        P4
TRANS        PM

With referential partitioning, you can partition any child table in the same way as its parent to improve performance and meet archival requirements, even though the partition key column is not present in any of the child tables.

Suppose you purge the RES table by dropping partitions. All the child tables are consequently purged as well, automatically. When you add a partition to the parent, a corresponding partition is added to the child table, automatically . Similarly, when you split the PM partition of the RES table to carve out a new partition, the PM partition of the TRANS table is also split into two partitions, at exactly the same point. Here is how you can split the PM partition of the RES table at the value 401:

alter table res
split partition pm
at (401)
into (partition p4, partition pm);

Now if you query the partitions of the TRANS table, the child, you see

select partition_name
from user_tab_partitions
where table_name = 'TRANS';
PARTITION_NAME
---------------
P1
P2
P3
P4
SYS_P45

Note the new partition, SYS_P45, which was created when the PM partition was split. (The SYS_P45 name comes from a sequence number prefixed by SYS_P.) You may want to change its name to be consistent with the naming of the PM partition in RES. To rename it, use the following SQL:

alter table trans rename partition
SYS_P45 to PM;

Note that if the child table had any locally partitioned indexes, their corresponding partitions would have been split as well. When you drop or split partitions on the parent, the operations are performed on the corresponding partitions on the child. If the parent has multiple referentially partitioned child tables, the operations are performed automatically on all of them. Referential partitioning enables you to define an appropriate partitioning strategy for the parent table while leaving the details out for the child tables. This not only simplifies the administration of partitioned objects significantly but also allows equipartitioning even when it is not desirable to include the partition key columns in the child table—or even possible to include them, such as with prepackaged applications where adding an extra column in a table is not allowed.

What happens when an incoming record in an INSERT statement has a value in the partitioning key column, for which no partitions have been defined? The INSERT will fail. However, if you have defined a default partition by, for example, using the MAXVALUE clause in a range-partition scheme (as shown in Listing 1) or the DEFAULT partition for a list-partitioned table, the INSERT will not fail but the new record will go into the default partition, which defeats the purpose of partitioning. So you have to ensure that you have a partition available for all possible records coming into the table; you have to carefully identify all possible values and then create partitions for all of them before the actual data arrives. For instance, suppose that the RES table is partitioned on the res_id column with partitions defined on value ranges 1-100, 101-200, and 201-300, so that you have defined partitions up to res_id = 300. You can monitor the maximum res_id column value, and just before it approaches 300, you can create a new p4 partition for values 301-400.

This task may be easier said than done. If you forget to add the partition, the new record with res_id = 301 will either fail to insert or go into the default partition, if you have defined one. Wouldn't it be great if the partitions were somehow created automatically?

Oracle Database 11g can do exactly that: create partitions automatically as needed without your intervention. To accomplish this, implement an interval partitioning method, as follows:

create table res (
   res_id      number not null,
   res_date   date,
   hotel_id   number(3),
   guest_id   number
)
partition by range (res_id)
interval (100) store in (users)
(
   partition p1 values less than (101)
);

This script creates a partition named p1 for records in which the value of the res_id column is 1-100. When records with a res_id value of less than 101 are inserted, they go into the p1 partition, but when a new record shows up with a res_id value equal to or greater than 101, Oracle Database 11g creates a new partition for it with a system-generated name. For instance, suppose you insert a record that has a res_id value of 901, as follows:

insert into res values (901,sysdate,1,1);

Now check the partitions defined on the table by executing the query shown in Listing 5. Note how the partition SYS_P82 was created automatically to hold the new 901 value, which was not within the boundary value of the P1 partition. At this time, if you insert another record with a res_id value of 301, the partition SYS_P82 is split in two, as shown in the output at the bottom half of Listing 5.

Code Listing 5: Checking partitions generated in interval partitioning

SQL> select partition_position, partition_name, high_value
  2    from user_tab_partitions
  3    where table_name = 'RES'
  4    order by 1;
PARTITION_POSITION    PARTITION_NAME     HIGH_VALUE
--------------------  --------------     ---------------
1                     P1                  101
2                     SYS_P82            1001

The result of the same query after inserting res_id = 301:

PARTITION_POSITION    PARTITION_NAME     HIGH_VALUE
--------------------  --------------     ---------------
1                     P1                  101
2                     SYS_P83             401
3                     SYS_P82            1001

You may want to address partitions by name, such as when you are truncating a specific partition. Given that the partition names are generated at runtime and you don't know them in advance, how can you address the specific partition of the table? For instance, suppose you want to truncate the partition that contains the value 901 as the res_id but you don't know the name of the partition. One way to find the partition name is to query the USER_TAB_PARTITIONS data dictionary view, as shown in Listings 4 and 5, but an easier way is to use the expanded partition access syntax in Oracle Database 11g. You can truncate that partition by issuing the following SQL:

alter table res truncate partition
for (901);

In interval partitioning, the first partition you specify in the table creation script is created in the default tablespace of the user but the subsequent partitions are created in the default tablespace of the database, not that of the user. Even if you change the attributes of the table to change the default tablespace, the new partitions still go into the default tablespace of the database. To force them to go to different tablespaces, you have to specify an additional clause at table creation time. To specify the RESDATA1 and RESDATA2 tablespaces as the locations for all new partitions, include the STORE IN clause after the INTERVAL clause, as follows:

interval (100) store in (resdata1,resdata2)

Now the new partitions will be spread over these two tablespaces in a round-robin manner.

You can also use a time stamp as the partitioning interval. This comes in handy for creating a table containing date ranges and a partition for each month of records. To accomplish that, write the INTERVAL clause as follows:

interval (numtoyminterval(1, 'MONTH'))

In my earlier partitioning article, I showed another important feature: composite partitioning. A composite partition is a partition further broken up into subpartitions. Up through Oracle Database 10g Release 2, you could divide only range partitions into hash or list subpartitions. Although this was adequate for most partitioning, some situations could really benefit from a range subpartition. For instance, consider the example of the hotel reservations table (RES) you saw earlier. Suppose the hotel ID shows the type of hotel: values 1-100 are for 5-star hotels, values 101-200 indicate 4-star hotels, and so on. Because a comparative revenue analysis is usually done within the same star rating, users tend to select the data for a specific range of hotel IDs, such as 1-100 only. Therefore, it makes a lot of sense to range-partition the RES table on the hotel_id column. However, users also tend to select the most recent data and you may want to store the older data on cheaper disks to save storage costs. So a perfectly valid competing argument may be to range-partition the table on the res_date column. Both are attractive alternatives—which one should you choose?

Code Listing 6: Range-range composite subpartitioning

create table res (
   res_id     number not null,
   res_date  date,
   hotel_id  number(3),
   guest_id  number
)
partition by range (res_date)
interval (numtoyminterval(1,'MONTH')) store in (example)
subpartition by range (hotel_id)
subpartition template
(
  subpartition s1 values less than (101),
  subpartition s2 values less than (201),
  subpartition s3 values less than (301),
  subpartition s4 values less than (401),
  subpartition sm values less than (maxvalue)
)
(
   partition p1 values less than (to_date('01-FEB-2007','DD-MON-YYYY'))
);

Why not both? In Oracle Database 11g, you can create—in addition to already available range-hash and range-list composite partitioning—the following: range-range, list-range, list-hash, and list-list composite partitioning. In this example, you can create range partitions on res_date and then create a range subpartition on the hotel_id column, as shown in Listing 6. Note that I have deliberately chosen an interval scheme for the partitioning. I could have chosen explicit partition names and high values as well, but I wanted to show how you can use interval partitioning to ease maintenance. I could instead have chosen to reverse the order of partitioning—I could have partitioned on hotel_id and then subpartitioned on res_date. To find the subpartitions created, you can select from the USER_TAB_SUBPARTITIONS data dictionary view, as follows:

SQL> select partition_name,
  2    subpartition_name
  3    from user_tab_subpartitions
  4    where table_name = 'RES';
PARTITION_NAME     SUBPARTITION_NAME
------------------ -----------------
P1                 P1_SM
P1                 P1_S4
P1                 P1_S3
P1                 P1_S2
P1                 P1_S1
SYS_P106           SYS_SUBP105
SYS_P106           SYS_SUBP104
SYS_P106           SYS_SUBP103
SYS_P106           SYS_SUBP102
SYS_P106           SYS_SUBP101

Oracle Database 11g also offers a new feature called a virtual column. A virtual column is not actually stored in the table, but it is computed every time it is accessed at runtime and presented to the user. You can partition data on this virtual column as well. Take the example of the RES table. As explained earlier, the hotel_id column signifies the star rating of the hotel: 1-100 means 5-star, 101-200 indicates 4-star, and so on. Suppose you want to make the start-rating value of the hotel_id column value a part of the RES table. The simplest way to do that is to create a column—STAR_RATING—and list-partition the table on that column. But how do you populate the column? One option is to rewrite the application to populate it, but this is not a very good option. Another option is to use a trigger to automatically update the column. In Oracle Database 11g, however, instead of populating the column with real values, you can just use a virtual column. Listing 7 shows the table creation script.

Code Listing 7: Virtual column partitioning

create table res (
   res_id           number not null,
   res_date        date,
   hotel_id        number(3),
   guest_id        number,
   star_rating     number(1)
      generated always as (
         substr(hotel_id,1,1)
      ) virtual
)
partition by list (star_rating)
(
   partition star5 values (5),
   partition star4 values (4),
   partition star3 values (3),
   partition others values (default)
)

So instead of making users figure out how to decipher the star rating, you have created a column that conveys the meaning in an unambiguous manner and have created a meaningful partitioning scheme based on this column. And you've done all this without writing a single line of code in a trigger to populate the column.

Partitioning is a powerful feature in modern database design that enables easier database administration with no change in logical design. In most cases, it helps accomplish the seemingly contradictory objectives of fine-grained storage, backup, archival, and retrieval—all with no application changes. In Oracle Database 11g, the partitioning option is further enhanced with the introduction of referential and interval partitioning, extended composite partitioning, and partitioning on virtual columns to offer a compelling reason to include partitioning in your physical database design.