Team LiB   Previous Section   Next Section

10.1 Table Partitioning

Over the past 15 years, hard disk capacities have evolved from around 10 megabytes to over 100 gigabytes, and capacities are still growing. Disk arrays are fast approaching the 100 terabyte range. No matter how much storage is available, however, there is always a way to exhaust it. As databases grow in size, day-to-day operations become more and more challenging. For example, finding the time and resources to rebuild an index containing 100 million entries can prove quite demanding. Prior to Oracle8, database administrators would meet this challenge by manually breaking a large table into several smaller tables. Although the pieces could be hidden behind a special type of view (called a partition view) during a query, all DML statements had to be performed against the individual tables, thereby exposing the partitioning scheme to the database developers and users.

Starting with Version 8.0, Oracle provided a means for breaking a table into multiple pieces while preserving the look and feel of a single table. Each piece is called a partition, and, although every partition must share the same columns, constraints, indexes, and triggers, each partition can have its own unique storage parameters. While administrators generally deal with individual partitions when allocating storage and performing backups, developers may choose to deal with either the entire table or with individual partitions.

10.1.1 Partitioning Concepts

Database designers and administrators have been partitioning tables since long before Oracle8 hit the scene. In general, 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 non-overlapping 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.

10.1.2 Partitioning Tables

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 clause to determine which partitions to search during SELECT, UPDATE, and DELETE operations (see Section 10.1.6 later in the chapter for more information).

10.1.3 Partitioning Indexes

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 global indexes, 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:

  1. Use a global index, if one is available and its columns are referenced in the SQL statement, to find the target rows across one or more partitions.

  2. Search a local index on every partition to identify whether any particular partition contains target rows.

  3. Define a subset of the partitions that might contain target rows, and then access local indexes on those partitions.

While global indexes might seem to be the simplest solution, they can be problematic. Because global indexes span all of the partitions of a table, they are adversely affected by partition maintenance operations. For example, if a partition is split into multiple pieces, or if two partitions are merged into one, all global indexes on the partitioned table are marked as UNUSABLE and must be rebuilt before they can be used again. This is especially troubling when you consider that primary key constraints on partitioned tables utilize global indexes by default. Instead of global indexes, consider using local indexes. You may also want to explore the use of local unique indexes as the mechanism for maintaining integrity for your partitioned tables.[1]

[1] When creating a primary key constraint, you can name an existing index rather than have Oracle build a new global index.

10.1.4 Partitioning Methods

In order 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 four types of partitioning available in Oracle9i.

10.1.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_1999 
    VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
      TABLESPACE ord1,
  PARTITION orders_2000 
    VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY'))
      TABLESPACE ord2,
  PARTITION orders_2001 
    VALUES LESS THAN (TO_DATE('01-JAN-2002','DD-MON-YYYY'))
      TABLESPACE ord3);

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

10.1.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 partition 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,
  inventory_qty NUMBER(6),
  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);

In order 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.[2] 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.

[2] 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.1.4.3 Composite 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. 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 you want more than one partition per year, however, you could subpartition each year by hashing the customer number across four buckets. The following example expands on the range-partitioning example shown earler 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_1999 
    VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
     (SUBPARTITION orders_1999_s1 TABLESPACE order_sub1,
      SUBPARTITION orders_1999_s2 TABLESPACE order_sub2,
      SUBPARTITION orders_1999_s3 TABLESPACE order_sub3,
      SUBPARTITION orders_1999_s4 TABLESPACE order_sub4),
  PARTITION orders_2000 
    VALUES LESS THAN (TO_DATE('01-JAN-2001','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-2002','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));

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.1.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. While it seems reasonable that composite partitioning could employ either range or list partitioning at the first level, only range/hash composite partitioning has been implemented by Oracle at this time.

10.1.5 Specifying Partitions

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;

Note that 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 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 the optimizer determines that two or more partitions are needed to satisfy the WHERE clause of a SELECT, UPDATE, or DELETE statement, the table and/or index partitions may be scanned in parallel. Therefore, depending on the system resources available to Oracle, scanning every partition of a partitioned table could be much faster than scanning an entire unpartitioned table.

  • Because hash partitioning spreads data randomly across the partitions,[3] it is unclear why you would want to use the PARTITION clause for hash-partitioned tables or the SUBPARTITON clause for composite-partitioned tables, since you don't know what data you are working on. The only reasonable scenario that comes to mind might be when you want to modify every row in the table, but you don't have enough rollback available to modify every row in a single transaction. In this case, you can perform an UPDATE or DELETE on each partition or subpartition and issue a COMMIT after each statement completes.

    [3] It isn't actually random, but it will seem that way to you, since you don't have access to the hash function.

  • Partitions can be merged, split, or dropped at any time by the DBA. Therefore, use caution when explicitly naming partitions in your DML statements. Otherwise, you may find your statements failing, or worse, your statements might work on the wrong set of data because partitions have been merged or split without your knowledge. You may want to check with your DBA to determine her policy concerning naming partitions in your DML statements.

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 the view:

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

10.1.6 Partition Pruning

Even when you don't name a specific partition in your SQL statement, the fact that a table is partitioned might still influence the manner in which you access the table. When an 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,[4] speeds statement execution by ignoring any partitions that cannot satisfy the statement's WHERE clause.

[4] Also known as partition elimination.

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.

In order 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. While 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 in order 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 will 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 operator column along with the value of ITERATOR for the options column indicates that more than one partition will be involved in the execution plan.[5] 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.[6] Given that our 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.

[5] If the optimizer had pruned all but one partition, the options column would contain the value 'SINGLE'. If no partitions were pruned, the options column would contain the value 'ALL'.

[6] The number shown in the partition_start and partition_end columns correlates to the partition_position column in the user_tab_partitions table, so you can query this table to identify the names of the partitions that are included in the execution plan.
    Team LiB   Previous Section   Next Section