Project 9-1 Creating a Range-Partitioned Table and a Local Partitioned Index - Oracle

Data and index partitioning are an important part in maintaining large databases. We have discussed the reasons for partitioning and shown the steps to implement it. In this project, you will create a range-partitioned table and a related local partitioned index.

Step by Step

1. Create two tablespaces called inv_ts_2007q1 and inv_2007q2 using the following SQL statements. These will be used to store data partitions.
   
   create tablespace inv_ts_2007q1
       datafile 'inv_ts_2007q1_1.dat' size 10m; create tablespace inv_ts_2007q2
       datafile 'inv_ts_2007q2_1.dat' size 10m;
   
2. Create two tablespaces called inv_idx_ts_2007q1 and inv_idx_2007q2 using the following SQL statements. These will be used to store index partitions.
   
   create tablespace inv_idx_ts_2007q1 
      datafile 'inv_idx_ts_2007q1_f1.dat' size 10m;
   create tablespace inv_idx_ts_2007q2
      datafile 'inv_idx_ts_2007q2_f1.dat' size 10m;
   
3. Create a partitioned table called INVOICE using the following listing, based on the following information: 
   
   a.  Define the table with the columns identified in Table 9-11.
   
   b.  Use order_date as the partition key, and subset the data into the first and second calendar quarters 2007.
   
   c.  Define the table with the data partitions and tablespaces identified in Table 9-12.
   
   d.  Use the enable row movement option:
   
   create table invoice(
     invoice_id  number,
     customer_id number,
     order_date  date,
     ship_date   date)
   partition by range (order_date) (partition INV_2007Q1 values less than
                            (to_date(2007-04-01','YYYY-MM-DD'))
                          tablespace inv_ts_2007Q1,
   partition INV_2007Q2 values less than 
                            (to_date('2007-07-01','YYYY-MM-DD'))
                           tablespace inv_ts_2007Q2,
   partition inv_max values less than (maxvalue)
                           tablespace inv_ts_max)
   enable row movement;
   
4. Create a local partitioned index called inv_order_dt_idx on call_date using the following listing as well as the index partitions and tablespaces identified in Table 9-12.
   
   create index inv_order_dt_idx on invoice(order_date)
   local
   (partition inv_idx_2007q1 tablespace inv_idx_ts_2007q1,
   partition inv_idx_2007q2  tablespace inv_idx_ts_2007q2,
   partition inv_idx_max tablespace inv_idx_ts_max);

TABLE 9-11.  INVOICE Table Columns

Project Summary

The steps in this project reinforce some of the more common scenarios you will encounter: range-based partitioning and prefixed local partitioned indexes. Separate tablespaces were used for data and indexes, quarterly partitions were defined, a local index was defined, and the enable row movement was used to allow the database to automatically redistribute rows to their related partitions in the event of an update to the partition key.

Well, we have certainly covered a lot in this section. Having the background information on these topics will serve you well when maintaining and tuning large databases. Before we move on to the next section, let’s take a quick progress check to make sure it all sank in.

----------------------------------------------------------

Progress Check

1. List at least three DML commands that can be applied to partitions as well as tables.
   
2. What does partition pruning mean?
   
3. How many table attributes can be used to define the partition key in list partitioning?
   
4. Which type of partitioning is most commonly used with a date-based partition key?
   
5. Which partitioning types cannot be combined together for composite partitioning?
   
6. How many partition keys can be defined for a partitioned table?
   
7. Which type of partitioned index has a one-to-one relationship between the data and index partitions?
   
8. What is meant by a prefixed partitioned index?

----------------------------------------------------------

CRITICAL SKILL 9.3

Compress Your Data

As you load more and more data into your database, performance and storage maintenance can quickly become concerns. Usually at the start of an implementation of a database, data volumes are estimated and projected a year or two ahead. However, often times these estimates turn out to be on the low side and you find yourself

1. The following DML commands can be applied to partitions as well as tables: delete, insert, select, truncate, and update.
   
2. Partition pruning is the process of eliminating data not belonging to the subset defined by the criteria of a query.
   
3. Only one table attribute can be used to define the partition key in list partitioning.
   
4. Range partitioning is most commonly used with a date-based partition key.
   
5. List and hash partitioning cannot be combined for composite partitioning.
   
6. Only one partition key may be defined.
   
7. Local partitioned indexes have a one-to-one relationship between the data and index partitions.
   
8. A partitioned index is prefixed when the leftmost column of the index key is the same as the leftmost column of the index partition key.

scrambling for more space in order to load new data. In addition to the partitioning abilities discussed in the previous section, Oracle Database 10g has the ability to compress your data and indexes to further address the concerns of performance and maintenance.

Compression can be performed at the data or index levels. In this section, we will discuss the options available with Oracle Database 10g and their impacts.