Team Fly

Page 305

Implement Data Partitioning

Implementing data partitioning in Oracle Database 10g is a process that requires careful planning to ensure success. You will need to understand your database environment, hardware, structures, and data before you can make the appropriate decisions. In the next few sections, we will outline the steps you will take when partitioning. Let's start by looking at the characteristics of the candidate table.

Analyze the Candidate Table

The first step in the partitioning process is to analyze and understand the candidate table, its environment and its uses. Following are some criteria to consider.

Table Structure and Data Contents You will need to look at what attributes are available and what is the distribution of the data within each attribute. You must consider currently available data as well as projected future data. The distribution of data over each attribute is important because you want to ensure that the resulting data subsets are evenly distributed across the defined partitions.

Consider a table called PHONE_USAGE that contains detailed mobile phone call records with over 300 million records per month. It has many attributes including the toll type (toll_type_cd) and the date of call (call_date). Table 9-1 shows a sample row count for a month by toll_type_cd. As you can see, using this attribute would probably not be an ideal choice by which to create subsets since the distribution is heavily skewed toward LOCAL calls.

Table 9-2 looks at the distribution of the same data by the day of the week (for example, Sunday to Saturday based on call_date).

We can see that the day of the week provides a relatively even distribution that is more suitable for partitioning.

How the Data Will Be Accessed To access the data, you will need to know what the commonest data selection criteria are. This is perhaps the most important part of the analysis because, as we stated earlier, query performance is the most noticeable

Team Fly