Select the Type of Partitioning - Oracle

After you have selected the partition key, the next step in the partitioning process is to decide which type of partitioning you want to implement on the candidate table. Oracle Database 10g provides four ways to partition data:

• Range partitioning
  
• List partitioning
  
• Hash partitioning
  
• Composite partitioning

The type of partitioning you choose will depend on the results of your analysis of the candidate table. The commonest type of partitioning is range partitioning and will be covered here in the most detail. Let’s look at the characteristics of each.

Range Partitioning  Range partitioning has been around the longest of all partitioning types and is the one implemented most often. In most cases, the ranges are based on some date component, such as quarters, months or, in the case of very large data volumes, days. (Theoretically, you can go down to any level of time—hours, minutes, and so on—assuming you have a time component. But the maintenance implications of defining this many partitions make it unrealistic.) The ranges selected will again be based on the results of your analysis of the table, using dates, numeric values, or character values. Following is an example based on the SALES table we saw earlier in the chapter.

---

NOTE

The partitioning examples presented in this chapter do not address all of the command options available. They are meant to give you a taste of what is available.

---

To create our SALES table as nonpartitioned, we would use the standard create table statement as shown in this listing:

create table sales (
prod_id        number        not null,
cust_id        number        not null,
time_id        date          not null, channel_id     number        not null,
promo_id       number        not null, quantity_sold  number (10,2) not null, amount_sold    number (10,2) not null)
tablespace example pctfree 5 initrans 1 maxtrans 255
storage ( initial 65536 minextents 1 maxextents 2147483645);

Based on our analysis of the usage patterns and row distribution, we have decided that the optimal partition strategy for this table is based on sales month. We will now redefine the SALES table using time_id as our partition key to create monthly partitions for January 2005 to December 2007, inclusive. Creation of data partitions is accomplished using extensions of the create table statement. The following listing shows the creation of the table with range partitions. Explanations of the important lines are given in Table 9-4.

1 create table sales (
2  prod_id           number        not null,
3  cust_id           number        not null,
4  time_id           date          not null,
5  channel_id        number        not null,
6  promo_id          number        not null,
7  quantity_sold     number (10,2) not null, 8  amount_sold       number (10,2) not null) 9  storage (initial 65536 minextents 1 
   maxextents 2147483645)
10 partition by range (time_id)
11 (partition sales_200501 values less than
12                      (to_date('2005-02-
   01','YYYY-MM-DD'))
13                      tablespace
   sales_ts_200501,
14 partition sales_200502 values less than 15                      (to_date('2005-03-
   01','YYYY-MM-DD'))
16                      tablespace
   sales_ts_200502,
17 partition sales_200503 values less than 18                      (to_date('2005-04-
   01','YYYY-MM-DD'))
19                      tablespace
   sales_ts_200503,
...
113 partition sales_200711 values less than 114                     (to_date('2007-12-
    01','YYYY-MM-DD'))
115                     tablespace
    sales_ts_200711,
116 partition sales_200712 values less than 117                     (to_date('2008-01-
    01','YYYY-MM-DD'))
118                     tablespace
    sales_ts_200712,
119 partition sales_max values less than
    (maxvalue)
120                     tablespace
    sales_ts_max);

TABLE 9-4.  Explanation of Range Partitioning  
                   Syntax (continued) 

---

NOTE

List Partitioning  There may be cases when, after your analysis of a candidate table, you decide that range partitioning is not the best fit for your table. Another way to subset your data is to use list partitioning, where you group a set of partition key values and assign them to their own tablespace. By using this type of partitioning, you can control the placement of the records in specified partitions, thereby allowing you to group related records together that may not otherwise have a relationship.

As an example, assume we have an INS_COVERAGE table that contains insurance coverages. Our analysis of this table and its usage leads us to decide that we should partition, based on the attribute cov_type_cd, into the buckets shown in Table 9-5.

TABLE 9-5.  Insurance Coverage Groupings

The syntax of the create table statement is similar to that for range partitioning. An explanation is provided in Table 9-6.

 1 create table ins_coverage (
 2  plan_id         number         not null,
 3  cust_id         number         not null,
 4  time_id         date           not null,
 5  dist_channel_id number         not null,
 6  cov_type_cd     varchar2(50)   not null,
 7  cov_amt         number (10,2)  not null,
 8  prem_amt        number (10,2)  not null)
 9  storage (initial 65536 minextents 1
    maxextents 2147483645)
10 partition by list (cov_type_cd)
11 (partition cov_life values ('TERM 65', 
   'UL', 'ADB')
12              tablespace cov_life_ts,
13 partition cov_gib values ('COLA', 'GIB') 14              tablespace cov_gib_ts,
15 partition cov_dis values ('WP', 'DIS')
16              tablespace cov_dis_ts,
17 partition cov_inv values ('MF')
18              tablespace cov_inv_ts
19 partition cov_other values(default));

---

TIP

TABLE 9-6.  Explanation of List Partitioning Syntax

offered by partitioning, Oracle Database 10g provides a third partitioning option called hash partitioning. With hash partitioning, you define up to 16 partition key attributes as well as the number of partitions you want to spread the data across. As long as each partition is on its own physical device and most of the queries use the partition key as a predicate, you should see performance gains. Hash partitioning is useful if the distribution of data is unknown or unpredictable.

The following listing is an example of hash partitioning. Table 9-7 explains the important lines.

TABLE 9-7.  Explanation of Hash Partitioning Syntax

It is beyond our scope to discuss the hashing algorithm used by Oracle Database 10g.We can say, however, that it is based on the number of attributes in the partition key and the number of partitions selected.

Composite Partitioning  The final type of partitioning is a combination of two of the previous types. Combining two types of partitioning is called composite partitioning. There are two valid combinations: range with hash and range with list. Using composite partitioning allows you to take advantage of the features of either hash or list partitioning within the higher groupings of ranges.

A good example of where this type of partitioning is used would be the PHONE_USAGE table we saw in our candidate table analysis. In this case, we have a table that is being loaded with 300 million records per month. We could choose to implement range partitioning by month and then subdivide the monthly partitions into four hash partitions. The following listing shows the SQL syntax that accomplishes this, and Table 9-8 provides the explanation of the important lines.

TABLE 9-8.  Explanation of Composite Partitioning
                        Syntax

We’ve covered all the partitioning types and some of their nuances. Now it’s time to look at how we can get an even bigger boost from our partitioned tables using indexing.