Database designers and administrators have been partitioning tables since long before Oracle8 hit the scene. Generally, table partitioning within a single database is done to improve performance and simplify administration tasks, while table partitioning between databases is meant to facilitate data distribution. For example, sales data might be partitioned by region and each partition hosted in a database housed at its respective regional sales office. Whereas a central data warehouse might gather sales data from each office for reporting and decision-support queries, it might be perfectly reasonable for the operational sales data to be distributed across multiple sites.

Partitioning by sets of rows such as in the sales data example, in which the value of the sales office column determines where the data resides, is known as horizontal partitioning. Partitioning may also be accomplished by splitting up sets of columns, in which case it is called vertical partitioning. For example, sensitive data such as salary information and social security numbers may be split off from the employee table into a separate table with restricted access. When partitioning vertically, primary key columns must be included in the set of columns for every partition. Therefore, unlike horizontal partitioning, where each partition contains nonoverlapping subsets of data, vertical partitioning mandates that some data be duplicated in each partition.

While both vertical and horizontal partitioning may be accomplished manually within and between Oracle databases, the Partitioning Option introduced in Oracle8 specifically deals with horizontal partitioning within a single database.

When partitioning is employed, a table changes from a physical object to a virtual concept. There isn’t really a table anymore, just a set of partitions. Since all of the partitions must share the same attribute and constraint definitions, however, it is possible to deal with the set of partitions as if they were a single table. The storage parameters, such as extent sizes and tablespace placement, are the only attributes that may differ among the partitions. This situation can facilitate some interesting storage scenarios, such as hosting infrequently accessed partitions on a CD jukebox while the heavily-hit data partitions reside on disk. You can also take advantage of Oracle’s segmented buffer cache to keep the most active partitions in the keep buffer so they are always in memory, while the rest of the partitions can be targeted for the recycle or default buffers. Additionally, individual partitions may be taken offline without affecting the availability of the rest of the partitions, giving administrators a great deal of flexibility.

Depending on the partitioning scheme employed, you must choose one or more columns of a table to be the partition key. The values of the columns in the partition key determine the partition that hosts a particular row. Oracle also uses the partition key information in concert with your WHERE clauses to determine which partitions to search during SELECT, UPDATE, and DELETE operations (see Section 10.6 later in the chapter for more information).

So what, you may wonder, happens to the indexes on partitioned tables? The answer is that you have to choose whether each index will stay intact (referred to as a global index), or be split into pieces corresponding to the table partitions (referred to as a local index). Furthermore, with a global index, you can choose to partition the index in a different manner than the table was partitioned. When you throw the fact that you can partition both b-tree and bit-map indexes into the mix, things can become overwhelming. When you issue a SELECT, UPDATE, or DELETE statement against a partitioned table, the optimizer can take several routes to locate the target rows:

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.

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);

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);

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));

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);

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
    ));

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)
  );

When you are writing SQL against partitioned tables, you have the option to treat the partitions as single, virtual tables, or to specify partition names within your SQL statements. If you write DML against a virtual table, the Oracle optimizer determines the partition or partitions that need to be involved. For an INSERT statement, the optimizer uses the values provided for the partition key to determine where to put each row. For UPDATE, DELETE, and SELECT statements, the optimizer uses the conditions from the WHERE clause along with information on local and global indexes to determine the partition or partitions that need to be searched.

If you know that your DML statement will utilize a single partition, and you know the name of the partition, you can use the PARTITION clause to tell the optimizer which partition to use. For example, if you want to summarize all orders for the year 2000, and you know that the cust_order table is range-partitioned by year, you could issue the following query:

SELECT COUNT(*) tot_orders, SUM(sale_price) tot_sales
FROM cust_order PARTITION (orders_2000)
WHERE cancelled_dt IS NULL;

This query’s WHERE clause doesn’t specify a date range, even though the table contains data spanning multiple years. Because you specified the orders_2000 partition, you know that the query will only summarize orders from 2000, so there is no need to check each order’s date.

If your table is composite-partitioned, you can use the SUBPARTITION clause to focus on a single subpartition of the table. For example, the following statement deletes all rows from the orders_2000_s1 subpartition of the range-hash composite-partitioned version of the cust_order table:

DELETE FROM cust_order SUBPARTITION (orders_2000_s1);

You can also use the PARTITION clause to delete the entire set of subpartitions that fall within a given partition:

DELETE FROM cust_order PARTITION (orders_2000);

This statement would delete all rows from the orders_2000_s1, orders_2000_s2, orders_2000_s3, and orders_2000_s4 subpartitions of the cust_order table.

Here are a few additional things to consider when working with partitioned tables:

If you need to access a single partition or subpartition but don’t like having partition names sprinkled throughout your code, consider creating views to hide the partition names, as in the following:

CREATE VIEW cust_order_2000 AS
SELECT *
FROM cust_order PARTITION (orders_2000);

You can then issue your SQL statements against such views:

SELECT order_nbr, cust_nbr, sale_price, order_dt
FROM cust_order_2000
WHERE quantity > 100;

Even when you don’t name a specific partition in a SQL statement, the fact that a table is partitioned might still influence the manner in which the statement accesses the table. When a SQL statement accesses one or more partitioned tables, the Oracle optimizer attempts to use the information in the WHERE clause to eliminate some of the partitions from consideration during statement execution. This process, called partition pruning, speeds statement execution by ignoring any partitions that cannot satisfy the statement’s WHERE clause. To do so, the optimizer uses information from the table definition combined with information from the statement’s WHERE clause. For example, given the following table definition:

CREATE TABLE tab1 (
  col1 NUMBER(5) NOT NULL,
  col2 DATE NOT NULL,
  col3 VARCHAR2(10) NOT NULL)
PARTITION BY RANGE (col2)
 (PARTITION tab1_1998 
    VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY'))
      TABLESPACE t1,
  PARTITION tab1_1999 
    VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
      TABLESPACE t1,
  PARTITION tab1_2000 
    VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY'))
      TABLESPACE t3,
  PARTITION tab1_2001 
    VALUES LESS THAN (TO_DATE('01-JAN-2002','DD-MON-YYYY'))
      TABLESPACE t4);

and the following query:

SELECT col1, col2, col3
FROM tab1
WHERE col2 > TO_DATE('01-OCT-2000','DD-MON-YYYY'),

the optimizer would eliminate partitions tab1_1998 and tab1_1999 from consideration, since neither partition could contain rows with a value for col2 greater than October 1, 2000.

For the optimizer to make these types of decisions, the WHERE clause must reference at least one column from the set of columns that comprise the partition key. Although this might seem fairly straightforward, not all queries against a partitioned table naturally include the partition key. If a unique index exists on the col1 column of the tab1 table from the previous example, for instance, the following query would generally offer the most efficient access:

SELECT col1, col2, col3
FROM tab1
WHERE col1 = 1578;

If the index on col1 had been defined as a local index, however, Oracle would need to visit each partition’s local index to find the one that holds the value 1578. If you also have information about the partition key (col2 in this case), you might want to consider including it in the query so that the optimizer can eliminate partitions, as in the following:

SELECT col1, col2, col3
FROM tab1
WHERE col1 = 1578 
  AND col2 > TO_DATE('01-JAN-2001','DD-MON-YYYY'),

With the additional condition, the optimizer can now eliminate the tab1_1998, tab1_1999, and tab1_2000 partitions from consideration. Oracle will now search a single unique index on the tab1_2001 partition instead of searching a unique index on each of the four table partitions. Of course, you would need to know that data pertaining to the value 1578 also had a value for col2 greater then January 1, 2001. If you can reliably provide additional information regarding the partition keys, than you should do so; otherwise, you’ll just have to let the optimizer do its best. Running EXPLAIN PLAN on your DML statements against partitioned tables will allow you to see which partitions the optimizer decided to utilize.

When checking the results of EXPLAIN PLAN, there are a couple of partition specific columns that you should add to your query against plan_table to see which partitions are being considered by the optimizer. To demonstrate, we’ll explain the following query against tab1:

EXPLAIN PLAN 
SET STATEMENT_ID = 'qry1' FOR
SELECT col1, col2, col3
FROM tab1
WHERE col2 BETWEEN TO_DATE('01-JUL-1999','DD-MON-YYYY')
  AND TO_DATE('01-JUL-2000','DD-MON-YYYY'),

When querying the plan_table table, you should include the partition_start and partition_end columns whenever the operation field starts with 'PARTITION':

SELECT lpad(' ',2 * level) || operation || ' ' ||
  options || ' ' || object_name || 
  DECODE(SUBSTR(operation, 1, 9), 'PARTITION',
    ' FROM ' || partition_start || 
    ' TO ' || partition_stop, ' ') "exec plan"
FROM plan_table
CONNECT BY PRIOR id = parent_id
START WITH id = 0 AND statement_id = 'qry1';

exec plan
------------------------------------------------------
  SELECT STATEMENT
    PARTITION RANGE ITERATOR  FROM 2 TO 3
      TABLE ACCESS FULL TAB1

The value of PARTITION RANGE for the operation column along with the value of ITERATOR for the options column indicates that more than one partition will be involved in the execution plan.^[2] The values of the partition_start and partition_end columns (2 and 3, respectively) indicate that the optimizer has decided to prune partitions 1 and 4, which correlate to the tab1_1998 and tab1_2001 partitions.^[3] Given that the WHERE clause specifies a date range of July 1, 1999 to July 1, 2000, the optimizer has correctly pruned all partitions that cannot contribute to the result set.