Home arrow Oracle arrow 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).

  1. Large Database Features In Oracle
  2. Implement Data Partitioning
  3. Select the Type of Partitioning
  4. Define the Indexing Strategy
  5. Project 9-1 Creating a Range-Partitioned Table and a Local Partitioned Index
  6. Data Compression
  7. Parallel Processing Configuration
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 26
December 08, 2005

print this article



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.


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-
13                      tablespace
14 partition sales_200502 values less than 15                      (to_date('2005-03-
16                      tablespace
17 partition sales_200503 values less than 18                      (to_date('2005-04-
19                      tablespace
113 partition sales_200711 values less than 114                     (to_date('2007-12-
115                     tablespace
116 partition sales_200712 values less than 117                     (to_date('2008-01-
118                     tablespace
119 partition sales_max values less than
120                     tablespace


Important Points


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


Important Points


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.







TABLE 9-5.  Insurance Coverage Groupings


Grouping Life






Disability Disability Investment

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


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


Important Points


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


Important Points


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 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.

 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
16                      (to_date('2006-02-
17 partition phoneusg_200602 values less
18                      (to_date('2006-03-
19 partition phoneusg_200603 values less
20                      (to_date('2006-04-
21 partition phoneusg_200604 values less
22                      (to_date('2006-05-
23 partition phoneusg_200605 values less
24                      (to_date('2006-06-
25 partition phoneusg_200606 values less
26                      (to_date('2006-07-
27 partition phoneusg_max values less than


Important Points


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

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.

>>> More Oracle Articles          >>> More By McGraw-Hill/Osborne

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: