HomeOracle Page 3 - Large Database Features In Oracle
Select the Type of Partitioning - Oracle
When you are working with large databases with Oracle Database 10g, there are certain topics with which you need to be familiar. These topics are covered in this article, the first of two parts. It is excerpted from chapter nine of the book Oracle Database 10g A Beginner's Guide, written by Ian Abramson et al (McGraw-Hill/Osborne, 2004; ISBN: 0072230789).
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:
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.
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 tablestatement 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 tablestatement. 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);
Defines the default table-level storage parameters that will apply to all
partitions. It is possible to override these defaults at the partition level
in favor of specific parameters required for a particular partition.
Defines the type of partitioning (for example, range) and the partition
key (for instance, time_id).
Define each partition based on the values of time_id (repetitive
lines for Apr 2005 to Oct 2007 omitted for brevity’s sake). For each
partition, the upper boundary of the partition key value is specified
(as defined by the values less than clause), as well as the name of the
tablespace where the subset is to be stored. Values must be specified
in ascending order and cannot overlap. It is good practice to give
meaningful names to both the partitions and tablespaces.
TABLE 9-4. Explanationof Range Partitioning Syntax
Define the default partition for any values of time_id that are
greater than the last defined partition (for instance, dates greater
than June 30, 2006). maxvalue is an Oracle keyword that results
in a value that is higher than any other possible value of the partition
key. It will also include null values if the situation exists.
TABLE 9-4. Explanation of Range Partitioning Syntax (continued)
Lines 11 to 13 define the first partition to hold data where time_id is less than February 1, 2005. Our intention in this example is that this first partition will only hold data for January 2005 (our data analysis tells us that there is no data before this date). However, if there happens to be data prior to January 2005, it will also be placed in this partition and may skew the row distribution by placing many more rows than intended in this partition.
That completes our discussion on range partitioning. Let’s now have a look at list and hash partitioning.
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.
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));
If you discover missing partition keys values that need to be added to existing partition definitions after the table has been created, you can issue and alter table abc modify partition xyz add values (‘value1’, …).
Hash Partitioning If you determine from your table analysis that neither range nor list partitioning is appropriate for your table, but you still want to reap the benefits
Defines the type of partitioning (for example, list) and the partition key (cov_type_cd, for instance). Note that with list partitioning, only one attribute from the table can be chosen as the partition key—in other words, multicolumn partition keys are not permitted.
Define each partition based on the groups of values of cov_type_cd.
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.
1 create table sub_activations ( 2 sub_id number not null, 3 dist_channel_id number not null, 4 act_date date not null, 5 deact_date date not null, 6 sales_rep_id number not null) 7 storage (initial 65536 minextents 1 maxextents 2147483645) 8 partition by hash (sub_id) 9 partitions 4 10 store in (subact_ts1, subact_ts2, subact_ts3, subact_ts4);
Defines the type of partitioning (for instance, hash) and the partition key (for example, sub_id).
Specifies the number of partitions over which to spread the data.
Specifies the tablespaces into which the partitions will be placed.
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 compositepartitioning. 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.
1 create table phone_usage 2 (sub_id number, 3 call_date date, 4 call_type_id number, 5 called_location varchar2(50), 6 service_carrier_id number) 7 storage (initial 65536 minextents 1 maxextents 2147483645) 8 partition by range (call_date) 9 subpartition by hash(sub_id) 10 subpartition template( 11 subpartition sub1 tablespace ph_usg_ts1, 12 subpartition sub2 tablespace ph_usg_ts2, 13 subpartition sub3 tablespace ph_usg_ts3, 14 subpartition sub4 tablespace ph_usg_ts4) 15 (partition phoneusg_200601 values less than 16 (to_date('2006-02- 01','YYYY-MM-DD')), 17 partition phoneusg_200602 values less than 18 (to_date('2006-03- 01','YYYY-MM-DD')), 19 partition phoneusg_200603 values less than 20 (to_date('2006-04- 01','YYYY-MM-DD')), 21 partition phoneusg_200604 values less than 22 (to_date('2006-05- 01','YYYY-MM-DD')), 23 partition phoneusg_200605 values less than 24 (to_date('2006-06- 01','YYYY-MM-DD')), 25 partition phoneusg_200606 values less than 26 (to_date('2006-07- 01','YYYY-MM-DD')), 27 partition phoneusg_max values less than (maxvalue));
Defines the higher-level partitioning type (for example, range) and its
partition key (for instance, call_date).
Specifies the secondary partitioning type (in this case, hash) and its
partition key (here, sub_id).
Specify a template that will be used to define the tablespace names
for each subpartition, as well as the tablespace names. The name
of each subpartition will be composed of the higher-level partition
name concatenated with an underscore, then the subpartition name
specified in the template. For example, data for January 2006 will be
placed in tablespace ph_usg_ts1 and divided into four subpartitions
called PHONEUSG_200601_SUB1, PHONEUSG_200601_SUB2,
PHONEUSG_200601_SUB3, and PHONEUSG_200601_SUB4.
Specify the ranges for the higher-level partition based on the call_date
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.
Ask the Expert
Q: If the partition key of record in a partitioned table is updated and the new value means that the data belongs to a different partition, does Oracle Database 10g automatically move the record to the appropriate partition?
A: Yes, but the table must have the enable row movement option before the update is made. This option is invoked as either part of the create table statement, or using an alter table statement. Otherwise, the update statement will generate an Oracle error.