Home arrow Oracle arrow Page 5 - Large Database Features In Oracle

Project 9-1 Creating a Range-Partitioned Table and a Local Partitioned Index - 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



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
                           tablespace inv_ts_2007Q1,
    partition INV_2007Q2 values less than 
                            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)
    (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);


Column Name

Data Type









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.


Partition Name

Tablespace Name

Upper Range Limit

Data Partitions



Apr 1, 2007




July 1, 2007





Index Partitions



Apr 1, 2007




July 1, 2007





TABLE 9-12.  INVOICE Table Data and Index


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?



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

Progress Check Answers
  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.

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