Learn how to use the various partitioning methods in Oracle 9i Release 2.

This is the second part of a two-part article addressing "How To" partition in Oracle 9i Release 2. Part 1 covers the basics of partitioning and how to partition tables. Part 2 will cover the partitioning of indexes. Part 2 will also draw together the concepts from the entire article into real life examples.

Introduction

In Part 1 of "Partitioning in Oracle 9i Release 2," we learned how to use the various table partitioning methods in the latest release of Oracle. We will now continue on and learn about Globally Partitioned and Locally Partitioned Indexes. We will cover:

• Background/Overview

• Globally Partitioned Indexes

• Locally Partitioned Indexes

• When To Use Which Partitioning Method

• Real-Life Example

Background

This article assumes that Oracle 9i Release 2 is properly installed and running. You will also need to have a user account that has a minimum of Create Index, Alter Index and Drop Index privileges. In addition to the basic privileges listed above, the creation of five small tablespaces (ITS01, ITS02, ITS03, ITS04, ITS05) or changes to the tablespace clause will need to be done to use the examples provided in this article.

Ideally, you should try each of the scripts in this article under a DBA role. All scripts have been tested on Oracle 9i Release 2 (9.2) running on Windows 2000. The examples below build off of the examples that were used in Part 1 of this article.

Globally Partitioned Indexes

There are two types of global indexes, non-partitioned and partitioned. Global non-partitioned indexes are those that are commonly used in OLTP databases (refer to Figure1). The syntax for a globally non-partitioned index is the exactly same syntax used for a "regular" index on a non-partitioned table. Refer to gnpi_me.sql (http://www.dbazine.com/code/GNPI_ME.SQL) for an example of a global non-partitioned index.

The other type of global index is the one that is partitioned. Globally partitioned indexes at this time can only be ranged partitioned and has similar syntactical structure to that of a range-partitioned table. gpi_me.sql (http://www.dbazine.com/code/GPI_ME.SQL) is provides for an example of a globally partitioned index. Note that a globally partitioned index can be applied to any type of partitioned table. Each partition of the globally partitioned index can and may refer to one or more partitions at the table level. For a visual representation of a global partitioned index refer to Figure 2.

The maintenance on globally partitioned indexes is a little bit more involved compared to the maintenance on locally partitioned indexes. Global indexes need to be rebuilt when there is DDL activity on the underlying table. The reason why they must be rebuilt is that DDL activity often causes the global indexes to be usually marked as UNUSABLE. To correct this problem there are two options to choose from:

• Use ALTER INDEX <index_name> REBUILD; or

• Use UPDATE GLOBAL INDEX clause when using ALTER TABLE.

The syntax for the ALTER INDEX statement is relatively straightforward so we will only focus on the UPDATE GLOBAL INDEX clause of the ALTER TABLE statement. The UPDATE GLOBAL INDEX is between the partition specification and the parallel clause. The partition specification can be any of the following:

• ADD PARTITION | SUBPARTITION (hash only)

• COALESCE PARTITION | SUBPARTITION

• DROP PARTITION

• EXCHANGE PARTITION | SUBPARTITION

• MERGE PARTITION

• MOVE PARTITION | SUBPARTITION

• SPLIT PARTITION

• TRUNCATE PARTITION | SUBPARTITION

For example:  

ALTER TABLE <TABLE_NAME>
<PARTITION SPECIFICATION>
UPDATE GLOBAL INDEX
PARALLEL (DEGREE #)

Locally Partitioned Indexes

Locally partitioned indexes are for the most part very straightforward. The lpi_me.sql (http://www.dbazine.com/code/LPI_ME.SQL) script shows examples of this type of index. In the script, locally partitioned indexes are created on three differently partitioned tables (range, hash, and list). Figure 3 gives a visual representation of how a locally partitioned index works.

Extra time should be allocated when creating locally partitioned indexes on range-hash or range-list partitioned tables. There are a couple reasons that extra time is needed for this type of index. One of the reasons is a decision needs to be made on what the index will be referencing in regards to a range-hash or range-list partitioned tables. A locally partitioned index can be created to point to either partition level or subpartition level.

Script lpi4cpt1_me.sql (http://www.dbazine.com/code/LPI4CPT1_ME.SQL) is the example for the creation of two locally partitioned indexes. This scripts show how to create a locally partitioned index on both a range-hash and range-list partitioned tables at the partition level. Each of the partitions of the locally partitioned indexes is assigned to its own tablespace for improved performance.

When creating a locally partitioned index one needs to keep in mind the number of subpartitions of the range-hash or range-list partitioned table being indexed. Reason being, is that the locally partitioned index will need to reference each subpartition of the range-hash or range-list partitioned table. So, for the locally partitioned index created by lpi4cpt2.me.sql (http://www.dbazine.com/code/LPI4CPT2_ME.SQL), this means that one index references twenty-five different subpartitions. For a visual representation of this refer to Figure 4. Script lpi4cpt3_me.sql (http://www.dbazine.com/code/LPI4CPT3_ME.SQL) is provided as an example of locally partitioned index on a range-list partition table.

Note: At this time Oracle has not implemented a SUBPARTITION TEMPLATE clause for the creation of locally partitioned indexes on range-hash or range-list partition tables. This means that you need to type everything out as in the examples in lpi4cpt2_me.sql and lpi4cpt3_me.sql.

Maintenance of locally partitioned indexes is much easier than the maintenance of globally partitioned indexes. Whenever there is DDL activity on the underlying indexed table Oracle rebuilds the locally partitioned index.

This automatic rebuilding of locally partitioned indexes is one reason why most DBAs prefer locally partitioned indexes.

When to Use Which Partitioning Method

There are five different table partitioning methods (range, hash, list, range-hash and range-list) and three for indexes (global non-partitioned, global partitioned and locally partitioned). So, the obvious question is: "When do I use which combination of table and index partitioning?" There is no concrete answer for that question. However, here are some general guidelines on mixing and matching table and index partitioning.

• First determine if you need to partition the table.
  • Refer to Part 1 of this article under "When To Partition"

• Next decide which table partitioning method is right for your situation.
  • Each method is described in Part 1 of this article under "Different Methods of Partitioning"

• Determine how volatile the data is.
  • How often are there inserts, updates and deletes?

• Choose your indexing strategy: global or local partitioned indexes.
  • Each type has its own maintenance consideration.

These guidelines are good place to start when developing a partitioning solution.

Real Life Example

The "rolling window" concept of only retaining a certain amount of data is the norm in most data warehousing environments. This rolling window can also used to archive data from an OLTP system. For our example we will assume that there is a twelve month rolling window.

Our example will cover the following steps:

• Create a range partition table that has a locally partitioned index.

• Use "CREATE TABLE . . AS" to copy the data into a separate table.

• Archive off the table created to hold the rolled off data.

• Drop last month partition.

• Add new months partition.

Script example.sql is an annotated code of the example above.

Conclusion

During the course of this two part article we have covered the "How to" of partitioning in Oracle 9i Release 2. Part 1 covered the basics of table partitioning. Part 2 followed with partitioning of indexes. We then brought together both partitioning methods and evaluated when to use each method. Near the end of this article we applied what we have learned in a real life example. I hope that by reading this that this article give you the basic knowledge to evaluate and use partitioning in your next design and implementation of Oracle 9i Release 2.

Read Part 1 of Partitioning in Oracle 9i, Release 2 here