11g Partitioning for Developers

Partition Types

Oracle offers 6 major types of partitions and a set of composite partitions. A composite partition allows you to combine partition types into partitions and sub-partitions.

List Partitions

A list partition is probably the simplest partition type to implement and understand. If your data has a column that would make sense looking at it as a list (or as a look up table), it would probably make a good list partition. State, country, color, product type, sic code, or other industry codes all would make good list partition keys.

For a list partition, you must enumerate the list. By that I mean you must specifically say what the elements in the list are. You can create a default partition to accept all values not included as values.

I don't want to get into the code yet but a pseudo partition declaration for a list type (using state as an example) would be something like:

   Partition by LIST (state_column)
     Partition 1 stores ('AZ', 'AK', 'AL')
     Partition 2 stores ('NM', 'NY', 'MD')
     Partition 3 stores ('LA', 'TX', 'MS')
     Partition 4 stores (ALL OTHER VALUES) <- Default partition

Range Partitions

In my experience, range partitions tend to be the most common. A range partition key will be a value that maps to a range data value. The example above, based on sale_date, is an example of a range partition. Like a list partition, all values must be enumerated. Unlike a list though, you specify the data as a range using a less than operator. Range partitioning allows a default partition to store any records that are greater than the highest partition.

A pseudo declaration of a range partition might look like:

  Partition by RANGE (sale_date)
    Partition 1 stores sale_date < INFINITY
    Partition 2 stores sale_date < 01-jan-2008
    Partition 2 stores sale_date < 01-jan-2007
    Partition 2 stores sale_date < 01-jan-2006
    Partition 2 stores sale_date < 01-jan-2005

Hash Partitions

Hash partitioning allows you store your data across multiple files when you don't otherwise have an obvious partition key. If your table is not storing historical data and is not queried by a list column, you may want to use a hash partition. A table with a sequence as a primary key can use that key as the partition key, but more likely will be a customer ID or product ID. A hash partition is less likely to be needed now that Oracle has added reference partitions.

When using a hash partition, Oracle tries to balance the data evenly across the number of partitions defined. This can be a dramatic benefit to maintenance as a DBA can operate on individual partitions instead of a single huge table.
A pseudo declaration of a hash partition might look like:

  Partition by hash (seq_pk)
    Create 5 Partitions

Reference Partitions

One of the most significant enhancements in Oracle 11g is the addition of reference partitions. A reference partition is a partition based on a foreign key.

A good example is an order entry system. Our orders table may be partitioned by order date. Let's say we have 1 billion rows in our table (we are exceptionally successful). Now, we have a child table, order lines, that has 10 times the number of rows but does not have a good partition key. We normally join orders to order_lines on order ID.

In the past we would add order date to the child table so that we could partition using the same key. The benefit of this, in addition to breaking the table into smaller chunks, is that we can do a partition-wise join. When we do partition pruning, we would know to only join with matching child partitions. The downside is that we make our table bigger by adding order date and we now have to maintain order date in two places.

In Oracle 11g, we can declare a reference partition. As the developer, you no longer need to maintain the order date in the order lines table. As long as both tables have active primary keys and an enforced foreign key constraint, you can define the parent partition using the best method available and then define the child constraint as a reference. Oracle will maintain the metadata of the relationship and allow you to do partition-wise joins.

A pseudo declaration of a reference partition might look like:

   Partition by reference (foreign_key_name)
   

Systems Partitions

System partitioning allows third party development access to partitioning. That means that it allows an application to control data placement within a partition. System partitioning is outside the scope of this presentation.

Interval Partitions

An interval partition is not really a different partition type as much as it is an extension to a range partition. Because we need to list all of the potential ranges, we have regular maintenance on range partitioned tables. If we partition by month, we may create 12 months in advance but when those run out, we need to create more.

Rather than list all of the potential ranges, interval partitions let us tell Oracle how we want the ranges created and we let Oracle figure out when we need a new partition.

A pseudo declaration of an interval partition might look like:

   Partition by RANGE (sale_date
     Interval of 1 year
     Start with partition 1 sale_date < 01-jan-2008

If we insert a record outside of 2008, Oracle will automatically create a new partition.

Composite Partitions

A composite partition is not a new type of partition. For performance and management reasons, you might need to partition by one method and then sub-partition your data even further.

An example might be a worldwide sales database. The majority of queries are by year but local departments may also want to see data by region. You can range partition by sale date and sub-partition by region code.

A pseudo code of this is:

   Partition by RANGE (sale_date)
     Subpartition by LIST (region_code)
       Subpartition region1 region_code = 1
       Subpartition region2 region_code = 2
       Subpartition region3 region_code = 3
       Subpartition region4 region_code = 4
     Partition 1 stores sale_date < INFINITY
     Partition 2 stores sale_date < 01-jan-2008
     Partition 2 stores sale_date < 01-jan-2007
     Partition 2 stores sale_date < 01-jan-2006
     Partition 2 stores sale_date < 01-jan-2005

I won't get much into the details of composite partitions. They are basically just an extension of the partition types that I will be talking about. I will provide one syntactically correct example below.

Indexing Partitions

Partitioned tables, like any other table, can be indexed for faster access. With a partitioned table, you can either index the entire table (a global index) or you can index the partitions (a local index). When you create a global index, you may create a global partitioned index or a global non-partitioned index.

Global Non-Partitioned Index

A global index is an index across the entire table. Unless a primary key column is included in your partition key, your table's primary key will be a global index. A non-partitioned index is a regular index that would exist on a non-partitioned table. In a global non-partitioned index, maintenance and availability is sacrificed at the cost of transactional performance. For this reason, global indexes tend to be preferred for OLTP applications.

Global Partitioned Index

Like a global non-partitioned index, a global index is an index across the entire table but the index itself is partitioned with a different partition key from the table. Global partitioned indexes can only be partitioned by hash or range. Partitioning by these methods is completely separate from the type of table partition. A table can be partitioned by LIST and still have a global range partition index.

Local Index

Local indexes are indexes create on each partition in a table. A local index automatically creates an index partition for each partition in the table. The index is partitioned by the same key as the partition key of the table.

A local index is always partitioned by the same partition key as the parent table. You cannot add or remove partitions in a local index, or in a global index for that matter. You must add and remove partitions from the parent table. A local index does not need to include the partition key in the list of indexed columns.

Local indexes provide the best throughput of a query and are used primarily in OLAP and DSS type environments.

Choosing an Index

Rather than try to come up with a new way to say this, I will let Oracle's documentation say it for me.

Just like partitioned tables, partitioned indexes improve manageability, availability, performance, and scalability. They can either be partitioned independently (global indexes) or automatically linked to a table's partitioning method (local indexes). In general, you should use global indexes for OLTP applications and local indexes for data warehousing or DSS applications. Also, whenever possible, you should try to use local indexes because they are easier to manage. When deciding what kind of partitioned index to use, you should consider the following guidelines in order:

1. If the table partitioning column is a subset of the index keys, use a local index. If this is the case, you are finished. If this is not the case, continue to guideline 2.

2. If the index is unique and does not include the partitioning key columns, then use a global index. If this is the case, then you are finished. Otherwise, continue to guideline 3.

3. If your priority is manageability, use a local index. If this is the case, you are finished. If this is not the case, continue to guideline 4.

4. If the application is an OLTP one and users need quick response times, use a global index. If the application is a DSS one and users are more interested in throughput, use a local index.

When should I use Partitioning?

Oracle recommends that any table over 2gb be considered for partitioning. That is a very good baseline number. Partitioning is an add-on package that adds cost to your database so you should consider that also. If you are already paying for partitioning, I would recommend that any table in a data warehouse with a logical partition key (except for very small tables, small < 100k rows) be considered for partitioning regardless of disk size.

In an OLTP database, I would recommend being a bit stricter on choosing tables to partition. Like parallelization, partitioning can actually hurt you in a transaction processing database. In some cases, I have seen very hot tables that were not very large, benefit from hash and list partitioning. Very large tables will almost always benefit from partitioning.

When scanning a partitioned table, Oracle is able to make the best use of the parallel features in the database. It's beyond the scope of this presentation but combining parallelism with partitioning on data loads (using things like merge from external tables) can dramatically improve overall load times.

Page: 1 2 3