10.4 Partitioning Methods

To horizontally partition a table (or index), you must specify a set of rules so that Oracle can determine in which partition a given row should reside. The following sections explore the five types of partitioning available in Oracle Database 10g.

10.4.1 Range Partitioning

The first partitioning scheme, introduced in Oracle8 and known as range partitioning, allows a table to be partitioned over ranges of values for one or more columns of the table. The simplest and most widely implemented form of range partitioning is to partition using a single date column. Consider the following DDL statement:

CREATE TABLE cust_order (

  order_nbr NUMBER(7) NOT NULL,

  cust_nbr NUMBER(5) NOT NULL,

  order_dt DATE NOT NULL,

  sales_emp_id NUMBER(5) NOT NULL,

  sale_price NUMBER(9,2),

  expected_ship_dt DATE,

  cancelled_dt DATE,

  ship_dt DATE,

  status VARCHAR2(20)

  )

PARTITION BY RANGE (order_dt)

 (PARTITION orders_2000 

    VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY'))

      TABLESPACE ord1,

  PARTITION orders_2001 

    VALUES LESS THAN (TO_DATE('01-JAN-2002','DD-MON-YYYY'))

      TABLESPACE ord2,

  PARTITION orders_2002 

    VALUES LESS THAN (TO_DATE('01-JAN-2003','DD-MON-YYYY'))

      TABLESPACE ord3);

Using this partitioning scheme, all orders prior to 2001 will reside in the orders_2000 partition; orders from 2001 will reside in the orders_2001 partition; and orders for the year 2002 will reside in the orders_2002 partition.

10.4.2 Hash Partitioning

In some cases, you may wish to partition a large table, but there are no columns for which range partitioning is suitable. Available in Oracle8i, hash partitioning allows you to specify the number of partitions and the partition columns (the partition key), but leaves the allocation of rows to partitions up to Oracle. As rows are inserted into the partitioned table, Oracle attempts to evenly spread the data across the partitions by applying a hashing function to the data in the partition key; the value returned by the hashing function determines the partition that hosts the row. If the partition columns are included in the WHERE clause of a SELECT, DELETE, or UPDATE statement, Oracle can apply the hash function to determine which partition to search. The following DDL statement demonstrates how the part table might be partitioned by hashing the part_nbr column:

CREATE TABLE part (

  part_nbr VARCHAR2(20) NOT NULL,

  name VARCHAR2(50) NOT NULL,

  supplier_id NUMBER(6) NOT NULL,

  inventory_qty NUMBER(6) NOT NULL,

  status VARCHAR2(10) NOT NULL,

  unit_cost NUMBER(8,2),

  resupply_date DATE

  )

PARTITION BY HASH (part_nbr)

 (PARTITION part1 TABLESPACE p1,

  PARTITION part2 TABLESPACE p2,

  PARTITION part3 TABLESPACE p3,

  PARTITION part4 TABLESPACE p4);

For the data to be evenly distributed across the partitions, it is important to choose columns with high cardinality as partition keys. A set of columns is said to have high cardinality if the number of distinct values is large compared to the size of the table. Choosing a high cardinality column for your partition key ensures an even distribution across your partitions; otherwise, the partitions can become unbalanced, causing performance to be unpredictable and making administration more difficult.

A unique key has the highest cardinality, since every row in the table has a distinct value. An example of a low cardinality column might be the country column in a customer table with millions of entries.

10.4.3 Composite Range-Hash Partitioning

If you are torn between whether to apply range or hash partitioning to your table, you can do some of each. Composite partitioning, also unveiled with Oracle8i, allows you to create multiple range partitions, each of which contains two or more hash subpartitions. There are two types of composite partitioning, range-hash partitioning, which was unveiled in Oracle8i, and range-list partitioning, which we'll talk about later in this chapter.

Composite partitioning is often useful when range partitioning is appropriate for the type of data stored in the table, but you want a finer granularity of partitioning than is practical using range partitioning alone. For example, it might make sense to partition your order table by year based on the types of queries against the table. If a year's worth of data proves too cumbersome for a single partition, however, you could subpartition each year by hashing the customer number across four buckets. The following example expands on the range-partitioning example shown earlier by generating subpartitions based on a hash of the customer number:

CREATE TABLE cust_order (

  order_nbr NUMBER(7) NOT NULL,

  cust_nbr NUMBER(5) NOT NULL,

  order_dt DATE NOT NULL,

  sales_emp_id NUMBER(5) NOT NULL,

  sale_price NUMBER(9,2),

  expected_ship_dt DATE,

  cancelled_dt DATE,

  ship_dt DATE,

  status VARCHAR2(20)

  )

PARTITION BY RANGE (order_dt)

SUBPARTITION BY HASH (cust_nbr) SUBPARTITIONS 4

STORE IN (order_sub1, order_sub2, order_sub3, order_sub4)

 (PARTITION orders_2000 

    VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY'))

     (SUBPARTITION orders_2000_s1 TABLESPACE order_sub1,

      SUBPARTITION orders_2000_s2 TABLESPACE order_sub2,

      SUBPARTITION orders_2000_s3 TABLESPACE order_sub3,

      SUBPARTITION orders_2000_s4 TABLESPACE order_sub4),

  PARTITION orders_2001 

    VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY'))

     (SUBPARTITION orders_2001_s1 TABLESPACE order_sub1,

      SUBPARTITION orders_2001_s2 TABLESPACE order_sub2,

      SUBPARTITION orders_2001_s3 TABLESPACE order_sub3,

      SUBPARTITION orders_2001_s4 TABLESPACE order_sub4),

  PARTITION orders_2002 

    VALUES LESS THAN (TO_DATE('01-JAN-2002','DD-MON-YYYY'))

     (SUBPARTITION orders_2002_s1 TABLESPACE order_sub1,

      SUBPARTITION orders_2002_s2 TABLESPACE order_sub2,

      SUBPARTITION orders_2002_s3 TABLESPACE order_sub3,

      SUBPARTITION orders_2002_s4 TABLESPACE order_sub4));

Interestingly, when composite partitioning is used, all of the data is physically stored in the subpartitions, while the partitions, just like the table, become virtual.

10.4.4 List Partitioning

Introduced in Oracle9i, list partitioning allows a table to be partitioned by one or more distinct values of a particular column. For example, a warehouse table containing sales summary data by product, state, and month/year could be partitioned into geographic regions, as in:

CREATE TABLE sales_fact (

  state_cd VARCHAR2(3) NOT NULL,

  month_cd NUMBER(2) NOT NULL,

  year_cd NUMBER(4) NOT NULL,

  product_cd VARCHAR2(10) NOT NULL,

  tot_sales NUMBER(9,2) NOT NULL

  )

PARTITION BY LIST (state_cd)

 (PARTITION sales_newengland VALUES ('CT','RI','MA','NH','ME','VT')

    TABLESPACE s1,

  PARTITION sales_northwest VALUES ('OR','WA','MT','ID','WY','AK')

    TABLESPACE s2,

  PARTITION sales_southwest VALUES ('NV','UT','AZ','NM','CO','HI')

    TABLESPACE s3,

  PARTITION sales_southeast VALUES ('FL','GA','AL','SC','NC','TN','WV')

    TABLESPACE s4,

  PARTITION sales_east VALUES ('PA','NY','NJ','MD','DE','VA','KY','OH')

    TABLESPACE s5,

  PARTITION sales_california VALUES ('CA')

    TABLESPACE s6,

  PARTITION sales_south VALUES ('TX','OK','LA','AR','MS')

    TABLESPACE s7,

  PARTITION sales_midwest VALUES ('ND','SD','NE','KS','MN','WI','IA',

    'IL','IN','MI','MO')

    TABLESPACE s8);

List partitioning is appropriate for low cardinality data in which the number of distinct values of a column is small relative to the number of rows. Unlike range and hash partitioning, where the partition key may contain several columns, list partitioning is limited to a single column.

10.4.5 Composite Range-List Partitioning

Available in the Oracle Database 10g release, range-list composite partitioning allows you to partition your data by range, and then subpartition via a list. This might be an excellent strategy for partitioning data in a sales warehouse so that you could partition your data both on sales periods (i.e., years, quarters, months) and on sales regions (i.e., states, countries, districts). The following example expands on the list partitioning example by adding yearly partitions:

CREATE TABLE sales_fact (

  state_cd VARCHAR2(3) NOT NULL,

  month_cd NUMBER(2) NOT NULL,

  year_cd NUMBER(4) NOT NULL,

  product_cd VARCHAR2(10) NOT NULL,

  tot_sales NUMBER(9,2) NOT NULL

  )

PARTITION BY RANGE (year_cd)

SUBPARTITION BY LIST (state_cd)

 (PARTITION sales_2000 

    VALUES LESS THAN (2001)

   (SUBPARTITION sales_2000_newengland 

      VALUES ('CT','RI','MA','NH','ME','VT') TABLESPACE s1,

    SUBPARTITION sales_2000_northwest 

      VALUES ('OR','WA','MT','ID','WY','AK') TABLESPACE s2,

    SUBPARTITION sales_2000_southwest 

      VALUES ('NV','UT','AZ','NM','CO','HI') TABLESPACE s3,

    SUBPARTITION sales_2000_southeast 

      VALUES ('FL','GA','AL','SC','NC','TN','WV') TABLESPACE s4,

    SUBPARTITION sales_2000_east 

      VALUES ('PA','NY','NJ','MD','DE','VA','KY','OH') TABLESPACE s5,

    SUBPARTITION sales_2000_california 

      VALUES ('CA') TABLESPACE s6,

    SUBPARTITION sales_2000_south 

      VALUES ('TX','OK','LA','AR','MS') TABLESPACE s7,

    SUBPARTITION sales_2000_midwest 

      VALUES ('ND','SD','NE','KS','MN','WI','IA', 'IL','IN','MI','MO')

      TABLESPACE s8

    ),

  PARTITION sales_2001 

    VALUES LESS THAN (2002)

   (SUBPARTITION sales_2001_newengland 

      VALUES ('CT','RI','MA','NH','ME','VT') TABLESPACE s1,

    SUBPARTITION sales_2001_northwest 

      VALUES ('OR','WA','MT','ID','WY','AK') TABLESPACE s2,

    SUBPARTITION sales_2001_southwest 

      VALUES ('NV','UT','AZ','NM','CO','HI') TABLESPACE s3,

    SUBPARTITION sales_2001_southeast 

      VALUES ('FL','GA','AL','SC','NC','TN','WV') TABLESPACE s4,

    SUBPARTITION sales_2001_east 

      VALUES ('PA','NY','NJ','MD','DE','VA','KY','OH') TABLESPACE s5,

    SUBPARTITION sales_2001_california 

      VALUES ('CA') TABLESPACE s6,

    SUBPARTITION sales_2001_south 

      VALUES ('TX','OK','LA','AR','MS') TABLESPACE s7,

    SUBPARTITION sales_2001_midwest 

      VALUES ('ND','SD','NE','KS','MN','WI','IA', 'IL','IN','MI','MO')

      TABLESPACE s8

    ),

  PARTITION sales_2002 

    VALUES LESS THAN (2003)

   (SUBPARTITION sales_2002_newengland 

      VALUES ('CT','RI','MA','NH','ME','VT') TABLESPACE s1,

    SUBPARTITION sales_2002_northwest 

      VALUES ('OR','WA','MT','ID','WY','AK') TABLESPACE s2,

    SUBPARTITION sales_2002_southwest 

      VALUES ('NV','UT','AZ','NM','CO','HI') TABLESPACE s3,

    SUBPARTITION sales_2002_southeast 

      VALUES ('FL','GA','AL','SC','NC','TN','WV') TABLESPACE s4,

    SUBPARTITION sales_2002_east 

      VALUES ('PA','NY','NJ','MD','DE','VA','KY','OH') TABLESPACE s5,

    SUBPARTITION sales_2002_california 

      VALUES ('CA') TABLESPACE s6,

    SUBPARTITION sales_2002_south 

      VALUES ('TX','OK','LA','AR','MS') TABLESPACE s7,

    SUBPARTITION sales_2002_midwest 

      VALUES ('ND','SD','NE','KS','MN','WI','IA', 'IL','IN','MI','MO')

      TABLESPACE s8

    ));

Rather than specifying the same list information over and over, Oracle Database 10g now allows the use of subpartition templates so that the subpartitioning scheme can be specified just once:

CREATE TABLE sales_fact (

  state_cd VARCHAR2(3) NOT NULL,

  month_cd NUMBER(2) NOT NULL,

  year_cd NUMBER(4) NOT NULL,

  product_cd VARCHAR2(10) NOT NULL,

  tot_sales NUMBER(9,2) NOT NULL

  )

PARTITION BY RANGE (year_cd)

SUBPARTITION BY LIST (state_cd)

SUBPARTITION TEMPLATE (

  SUBPARTITION newengland 

    VALUES ('CT','RI','MA','NH','ME','VT') TABLESPACE s1,

  SUBPARTITION northwest 

    VALUES ('OR','WA','MT','ID','WY','AK') TABLESPACE s2,

  SUBPARTITION southwest 

    VALUES ('NV','UT','AZ','NM','CO','HI') TABLESPACE s3,

  SUBPARTITION southeast 

    VALUES ('FL','GA','AL','SC','NC','TN','WV') TABLESPACE s4,

  SUBPARTITION east 

    VALUES ('PA','NY','NJ','MD','DE','VA','KY','OH') TABLESPACE s5,

  SUBPARTITION california 

    VALUES ('CA') TABLESPACE s6,

  SUBPARTITION south 

    VALUES ('TX','OK','LA','AR','MS') TABLESPACE s7,

  SUBPARTITION midwest 

    VALUES ('ND','SD','NE','KS','MN','WI','IA', 'IL','IN','MI','MO')

    TABLESPACE s8

 )

 (PARTITION sales_2000 

    VALUES LESS THAN (2001),

  PARTITION sales_2001 

    VALUES LESS THAN (2002),

  PARTITION sales_2002 

    VALUES LESS THAN (2003)

  );