This is the first part of a two-part article addressing “How To” partition in Oracle9i, Release 2. Part 1 will cover 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
Oracle DBAs face an ever growing and demanding work environment. The only thing that may outpace the demands of the work place is the size of the databases themselves. Database size has grown to a point where they are now measured in the hundreds of gigabytes, and in some cases, several terabytes. The characteristics of very large databases (VLDB) demand a different style of administration. Administering VLDB often includes using the ability to partition tables and indexes.

Since partitioning is such an integral part of VLDB, the remainder of this article will focus on how to partition; specifically, the partitioning of tables in an Oracle9i Release 2 environment. Part 2 of this article will focus on the partitioning of indexes. The complete article will cover:

• Partitioning defined
• When to partition
• Different methods of partitioning
• Partitioning tables
• Partitioning indexes

The organization of this article is modular so you can skip to a specific topic of interest. Each of the table partitioning methods (Range, Hash, List, Range-Hash and Range-List) will have its own section that includes code examples and check scripts.

Background
This article assumes that Oracle9i Release 2 is properly installed and running. You will also need to have a user account that has a minimum of Create Table, Alter Table and Drop Table privileges. In addition to the basic privileges listed above, you will need to create five small tablespaces (TS01, TS02, TS03, TS04, TS05) or changes to the tablespace clause 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 Oracle9i Release 2 (9.2) running on Windows 2000.

Partitioning Defined
The concept of divide and conquer has been around since the times of Sun Tzu (500 B.C.). Recognizing the wisdom of this concept, Oracle applied it to the management of large tables and indexes. Oracle has continued to evolve and refine its partitioning capabilities since its first implementation of range partitioning in Oracle8. In Oracle8i and 9i, Oracle has continued to add both functionality and new partitioning methods. The current version of Oracle9i Release 2 continues this tradition by adding new functionality for list partitioning and the new range-list partitioning method.

When to Partition
There are two main reasons to use partitioning in a VLDB environment. These reasons are related to management and performance improvement. Partitioning offers:

• Management at the individual partition level for data loads, index creation and rebuilding, and backup/recovery. This can result in less down time because only individual partitions being actively managed are unavailable.
• Increased query performance by selecting only from the relevant partitions. This weeding out process eliminates the partitions that do not contain the data needed by the query through a technique called partition pruning.

The decision about exactly when to use partitioning is rather subjective. Some general guidelines that Oracle and I suggest are listed below. Use partitioning:

• When a table reaches a “large” size. Large is defined relative to your environment. Tables greater than 2GB should always be considered for partitioning.
• When performance benefits outweigh the additional management issues related to partitioning.
• When the archiving of data is on a schedule and is repetitive. For instance, data warehouses usually hold data for a specific amount of time (rolling window). Old data is then rolled off to be archived.

Take a moment and evaluate the criteria above to make sure that partitioning is advantageous for your environment. In larger environments partitioning is worth the time to investigate and implement.

Different Methods of Partitioning
Oracle9i, Release 2, has five partitioning methods for tables. They are listed in the table below with a brief description.

Partitioning Method	Brief Description
Range Partitioning	Used when there are logical ranges of data. Possible usage: dates, part numbers, and serial numbers.
Hash Partitioning	Used to spread data evenly over partitions. Possible usage: data has no logical groupings.
List Partitioning	Used to list together unrelated data into partitions. Possible usage: a number of states list partitioned into a region.
Composite Range-Hash Partitioning	Used to range partition first, then spreads data into hash partitions. Possible usage: range partition by date of birth then hash partition by name; store the results into the hash partitions.
Composite Range-List Partitioning	Used to range partition first, then spreads data into list partitions. Possible usage: range partition by date of birth then list partition by state, then store the results into the list partitions.

For partitioning indexes, there are global and local indexes. Global indexes provide greater flexibility by allowing indexes to be independent of the partition method used on the table. This allows for the global index to reference different partitions of a single table. Local indexes (while less flexible than global) are easier to manage. Local indexes are mapped to a specific partition. This one-to-one relationship between local index partitions and table partitions allows Oracle the ability to manage local indexes. Partitioning indexes will be the focus of Part 2 of this article.

Detailed examples and code will be provided for each partitioning method in their respective sections. The use of the ENABLE ROW MOVEMENT clause is included in all of the examples of table partitioning to allow row movement if the partition key is updated.

Partitioning Tables
Range Partitioning
Range partitioning was the first partitioning method supported by Oracle in Oracle8. Range partitioning was probably the first partition method because data normally has some sort of logical range. For example, business transactions can be partitioned by various versions of date (start date, transaction date, close date, or date of payment). Range partitioning can also be performed on part numbers, serial numbers, or any other ranges that can be discovered.

The example provided for range partition will be on a table named PARTITION_BY_RANGE (what else would I call it?). The PARTITION_BY_RANGE table holds records that contain the simple personnel data of FIRST_NAME, MIDDLE_INIT, LAST_NAME, BIRTH_MM, BIRTH_DD, and BIRTH_YYYY. The actual partitioning is on the following columns BIRTH_YYYY, BIRTH_MM, and BIRTH_DD. The complete DDL for the PARTITION_BY_RANGE table is provided in the script RANGE_ME.SQL.

A brief explanation of the code follows. Each partition is assigned to its own tablespace. The last partition is the “catch all” partition. By using MAXVALUE, the last partition will contain all the records with values over the second to last partition.

Hash Partitioning
Oracle’s hash partitioning distributes data by applying a proprietary hashing algorithm to the partition key and then assigning the data to the appropriate partition. By using hash partitioning, DBAs can partition data that may not have any logical ranges. Also, DBAs do not have to know anything about the actual data itself. Oracle handles all of the distribution of data once the partition key is identified.

The HASH_ME.SQL script is an example of a hash partition table. Please note that the data may not appear to be distributed evenly because of the limited number of inserts applied to the table.
A brief explanation of the code follows. The PARTITION BY HASH line is where the partition key is identified. In this example the partition key is AGE. Once the hashing algorithm is applied each record is distributed to a partition. Each partition is specifically assigned to its own tablespace.

List Partitioning
List partitioning was added as a partitioning method in Oracle9i, Release 1. List partitioning allows for partitions to reflect real-world groupings (e.g., business units and territory regions). List partitioning differs from range partition in that the groupings in list partitioning are not side-by-side or in a logical range. List partitioning gives the DBA the ability to group together seemingly unrelated data into a specific partition.

The LIST_ME.SQL script provides an example of a list partition table. Note the last partition with the DEFAULT value. This DEFAULT value is new in Oracle9i, Release 2.

A brief explanation of the code follows. The PARTITION BY LIST line is where the partition key is identified. In this example, the partition key is STATE. Each partition is explicitly named, contains a specific grouping of VALUES and is contained in its own tablespace. The last partition with the DEFAULT is the “catch all” partition. This catch all partition should be queried periodically to make sure that proper data is being entered.

Composite Range-Hash Partitioning
Composite range-hash partitioning combines both the ease of range partitioning and the benefits of hashing for data placement, striping, and parallelism. Range-hash partitioning is slightly harder to implement. But, with the example provided and a detailed explanation of the code, one can easily learn how to use this powerful partitioning method.

The RANGE_HASH_ME.SQL script provides an example of a composite range-hash partition table.
A brief explanation of the code follows. The PARTITION BY RANGE clause is where we shall begin. The partition key is (BIRTH_YYYY, BIRTH_MM, BIRTH_DD) for the partition. Next, the SUBPARTITION BY HASH clause indicates what the partition key is for the subpartition (in this case FIRST_NAME, MIDDLE_INIT, LAST_NAME). A SUBPARTITION TEMPLATE then defines the subpartition names and their respective tablespace. Subpartitions are automatically named by Oracle by concatenating the partition name, an underscore, and the subpartition name from the template. Remember that the total length of the subpartition name should not be longer than 30 characters including the underscore.

I suggest that, when you actually try to build a range-hash partition table, you do it in the following steps:
1. Determine the partition key for the range.
2. Design a range partition table.
3. Determine the partition key for the hash.
4. Create the SUBPARTITION BY HASH clause.
5. Create the SUBPARTITION TEMPLATE.

Do Steps 1 and 2 first. Then you can insert the code created in Steps 3 — 5 in the range partition table syntax.
Composite Range-List Partitioning Composite range-list partitioning combines both the ease of range partitioning and the benefits of list partitioning at the subpartition level. Like range-hash partitioning, range-list partitioning needs to be carefully designed. The time used to properly design a range-list partition table pays off during the actual creation of the table.

The RANGE_LIST_ME.SQL script provides an example of a composite range-list partition table.
A brief explanation of the code follows. The PARTITION BY RANGE clause identifies the partition key (BIRTH_YYYY, BIRTH_MM, BIRTH_DD). A SUBPARTITION TEMPLATE then defines the subpartition names and their respective tablespace. Subpartitions are automatically named by Oracle by concatenating the partition name, an underscore, and the subpartition name from the template. Remember that the total length of the subpartition name should not be longer than 30 characters including the underscore. When building a range-list partition table, you may want to refer to the steps mentioned at the end of the Composite Range-List section. The only difference is in Step 4. Instead of “Create the SUBPARTITION BY HASH clause” it would read, “Create the SUBPARTITION BY LIST clause” for the range-list partition table.

Conclusion
This is the first of a two-part article suggesting the use of partition tables in VLDB environments. Part two of this article will cover partition indexes. In part two both methods (partition tables and indexes) will be brought together in real life examples.