Partitioning enables administration of an object either collectively or individually.

From the perspective of a database administrator, a partitioned object has multiple pieces that can be managed either collectively or individually. This gives an administrator considerable flexibility in managing partitioned objects. However, from the perspective of the application, a partitioned table is identical to a nonpartitioned table; no modifications are necessary when accessing a partitioned table using SQL queries and DML statements.

Figure 2-1 offers a graphical view of how partitioned tables differ from nonpartitioned tables.

Figure 2-1 Views of Partitioned and Nonpartitioned Tables

Description of "Figure 2-1 Views of Partitioned and Nonpartitioned Tables"

Each row in a partitioned table is unambiguously assigned to a single partition using a key.

The partitioning key consists of one or more columns that determine the partition where each row is stored. Oracle automatically directs insert, update, and delete operations to the appropriate partition with the partitioning key.

Most tables can be partitioned.

Any table can be partitioned up to a million separate partitions except those tables containing columns with LONG or LONG RAW data types. You can, however, use tables containing columns with CLOB or BLOB data types.

The following topics are discussed:

• When to Partition a Table

• When to Partition an Index

Partitioned index-organized tables are very useful for providing improved performance, manageability, and availability for index-organized tables.

For partitioning an index-organized table:

• Partition columns must be a subset of the primary key columns.

• Secondary indexes can be partitioned (both locally and globally).

• OVERFLOW data segments are always equipartitioned with the table partitions.

System partitioning enables application-controlled partitioning without having the database controlling the data placement.

The database simply provides the ability to break down a table into partitions without knowing what the individual partitions are going to be used for. All aspects of partitioning have to be controlled by the application. For example, an attempt to insert into a system partitioned table without the explicit specification of a partition fails.

System partitioning provides the well-known benefits of partitioning (scalability, availability, and manageability), but the partitioning and actual data placement are controlled by the application.

Information Lifecycle Management (ILM) is concerned with managing data during its lifetime.

Partitioning plays a key role in ILM because it enables groups of data (that is, partitions) to be distributed across different types of storage devices and managed individually.

Partitioned hash clusters are supported in Oracle Database.

Only single-level range partitioning is supported for partitioned hash clusters.

Unstructured data, such as images and documents, which is stored in a LOB column in a database can also be partitioned.

When a table is partitioned, all of the columns reside in the tablespace for that partition, except LOB columns, which can be stored in their own tablespace.

This technique is very useful when a table consists of large LOBs because they can be stored separately from the main data. This can be beneficial if the main data is being frequently updated but the LOB data is not. For example, an employee record may contain a photo which is unlikely to change frequently. However, the employee personnel details (such as address, department, manager, and so on) could change. This approach also means that you can use less expensive storage for storing the LOB data and more expensive, faster storage can be used for the employee record.

Partitioning when using XMLType and object tables and columns offers the standard advantages of partitioning, such as enabling tables and indexes to be subdivided into smaller pieces, thus enabling these database objects to be managed and accessed at a finer level of granularity.

When you partition an XMLType table or a table with an XMLType column using list, range, or hash partitioning, any ordered collection tables (OCTs) within the data are automatically partitioned accordingly, by default. This equipartitioning means that the partitioning of an OCT follows the partitioning scheme of its parent (base) table. There is a corresponding collection-table partition for each partition of the base table. A child element is stored in the collection-table partition that corresponds to the base-table partition of its parent element.

If you partition a table that has a nested table, then Oracle Database uses the partitioning scheme of the original base table as the basis for how the nested table is partitioned. This partitioning of one base table partition for each nested table partition is called equipartitioning. By default, nested tables are automatically partitioned when the base table is partitioned. Note, however, that composite partitioning is not supported for OCTs or nested tables.

You can use partitioning to improve performance.

By limiting the amount of data to be examined or operated on, and by providing data distribution for parallel execution, partitioning provides multiple performance benefits. Partitioning features include:

• Partition Pruning for Performance

• Partition-Wise Joins for Performance

Partitioning enables you to partition tables and indexes into smaller, more manageable units, providing database administrators with the ability to pursue a divide and conquer approach to data management.

With partitioning, maintenance operations can be focused on particular portions of tables. For example, you could back up a single partition of a table, rather than back up the entire table. For maintenance operations across an entire database object, it is possible to perform these operations on a per-partition basis, thus dividing the maintenance process into more manageable chunks.

A typical usage of partitioning for manageability is to support a rolling window load process in a data warehouse. Suppose that you load new data into a table on a weekly basis. That table could be partitioned so that each partition contains one week of data. The load process is simply the addition of a new partition using a partition exchange load. Adding a single partition is much more efficient than modifying the entire table, because you do not need to modify any other partitions.

Partitioned database objects provide partition independence. This characteristic of partition independence can be an important part of a high-availability strategy.

For example, if one partition of a partitioned table is unavailable, then all of the other partitions of the table remain online and available. The application can continue to execute queries and transactions against the available partitions for the table, and these database operations can run successfully, provided they do not need to access the unavailable partition.

The database administrator can specify that each partition be stored in a separate tablespace; the most common scenario is having these tablespaces stored on different storage tiers. Storing different partitions in different tablespaces enables you to do backup and recovery operations on each individual partition, independent of the other partitions in the table. Thus allowing the active parts of the database to be made available sooner so access to the system can continue, while the inactive data is still being restored. Moreover, partitioning can reduce scheduled downtime. The performance gains provided by partitioning may enable you to complete maintenance operations on large database objects in relatively small batch windows.

Single-level partitioning includes range, hash, and list partitioning.

A table is defined by specifying one of the following data distribution methodologies, using one or more columns as the partitioning key:

• Range Partitioning

• Hash Partitioning

• List Partitioning

For example, consider a table with a column of type NUMBER as the partitioning key and two partitions less_than_five_hundred and less_than_one_thousand. The less_than_one_thousand partition contains rows where the following condition is true:

500 <= partitioning key < 1000

Figure 2-2 offers a graphical view of the basic partitioning strategies for a single-level partitioned table.

Figure 2-2 List, Range, and Hash Partitioning

Description of "Figure 2-2 List, Range, and Hash Partitioning"

Composite partitioning is a combination of the basic data distribution methods.

With composite partitioning, a table is partitioned by one data distribution method and then each partition is further subdivided into subpartitions using a second data distribution method. All subpartitions for a given partition represent a logical subset of the data.

Composite partitioning supports historical operations, such as adding new range partitions, but also provides higher degrees of potential partition pruning and finer granularity of data placement through subpartitioning. Figure 2-3 offers a graphical view of range-hash and range-list composite partitioning, as an example.

Figure 2-3 Composite Range—List Partitioning

Description of "Figure 2-3 Composite Range—List Partitioning"

The types of composite partitioning are:

• Composite Range-Range Partitioning

• Composite Range-Hash Partitioning

• Composite Range-List Partitioning

• Composite List-Range Partitioning

• Composite List-Hash Partitioning

• Composite List-List Partitioning

• Composite Hash-Hash Partitioning

• Composite Hash-List Partitioning

• Composite Hash-Range Partitioning

Manageability extensions for partitioning are introduced in this topic.

The following extensions significantly enhance the manageability of partitioned tables:

• Interval Partitioning

• Partition Advisor

Extensions to partitioning keys are introduced in this topic.

The following extensions extend the flexibility in defining partitioning keys:

• Reference Partitioning

• Virtual Column-Based Partitioning

The type of partitioned index to use should be chosen after reviewing various factors.

When deciding what kind of partitioned index to use, you should consider the following guidelines in this order:

1. If the table partitioning column is a subset of the index keys, then use a local index. If this is the case, then you are finished. If this is not the case, then 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, then consider a local index. If this is the case, then you are finished. If this is not the case, continue to guideline 4.

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

Local partitioned indexes are easier to manage than other types of partitioned indexes.

They also offer greater availability and are common in DSS environments. The reason for this is equipartitioning: each partition of a local index is associated with exactly one partition of the table. This functionality enables Oracle to automatically keep the index partitions synchronized with the table partitions, and makes each table-index pair independent. Any actions that make one partition's data invalid or unavailable only affect a single partition.

Local partitioned indexes support more availability when there are partition or subpartition maintenance operations on the table. A type of index called a local nonprefixed index is very useful for historical databases. In this type of index, the partitioning is not on the left prefix of the index columns.

You cannot explicitly add a partition to a local index. Instead, new partitions are added to local indexes only when you add a partition to the underlying table. Likewise, you cannot explicitly drop a partition from a local index. Instead, local index partitions are dropped only when you drop a partition from the underlying table.

A local index can be unique. However, in order for a local index to be unique, the partitioning key of the table must be part of the index's key columns.

Figure 2-6 offers a graphical view of local partitioned indexes.

Figure 2-6 Local Partitioned Index

Description of "Figure 2-6 Local Partitioned Index"

Global partitioned indexes are introduced in the topic.

Oracle offers global range partitioned indexes and global hash partitioned indexes, discussed in the following topics:

• Global Range Partitioned Indexes

• Global Hash Partitioned Indexes

• Maintenance of Global Partitioned Indexes

Global nonpartitioned indexes behave just like local nonpartitioned indexes.

Figure 2-8 offers a graphical view of global nonpartitioned indexes.

Figure 2-8 Global Nonpartitioned Index

Description of "Figure 2-8 Global Nonpartitioned Index"

You can create bitmap indexes on partitioned tables, with some restrictions.

Bitmap indexes must be local to the partitioned table. They cannot be global indexes.

Global indexes can be unique. Local indexes can only be unique if the partitioning key is a part of the index key.

You can create local and global indexes on a subset of the partitions of a table, enabling more flexibility in index creation.

This feature is supported using a default table indexing property. When a table is created or altered, a default indexing property can be specified for the table or its partitions. The table indexing property is only considered for partial indexes.

When an index is created as PARTIAL on a table:

• Local indexes: An index partition is created usable if indexing is turned on for the table partition, and unusable otherwise. You can override this behavior by specifying USABLE/UNUSABLE at the index or index partition level.

• Global indexes: Includes only those partitions for which indexing is turned on, and exclude the others.

This feature is not supported for unique indexes, or for indexes used for enforcing unique constraints. FULL is the default if neither FULL nor PARTIAL is specified.

By default, any index is created as FULL index, which decouples the index from the table indexing property.

The INDEXING clause may also be specified at the partition and subpartition levels.

The following SQL DDL creates a table with these items:

• Partitions ORD_P1 and ORD_P3 are included in all partial global indexes

• Local index partitions (for indexes created PARTIAL) corresponding to the above two table partitions are created usable by default.

• Other partitions are excluded from all partial global indexes, and created unusable in local indexes (for indexes created PARTIAL).

CREATE TABLE orders (
  order_id NUMBER(12),
  order_date DATE CONSTRAINT order_date_nn NOT NULL,
  order_mode VARCHAR2(8),
  customer_id NUMBER(6) CONSTRAINT order_customer_id_nn NOT NULL,
  order_status NUMBER(2),
  order_total NUMBER(8,2),
  sales_rep_id NUMBER(6),
  promotion_id NUMBER(6),
  CONSTRAINT order_mode_lov CHECK (order_mode in ('direct','online')),
  CONSTRAINT order_total_min CHECK (order_total >= 0))
   INDEXING OFF
   PARTITION BY RANGE (ORDER_DATE)
   (PARTITION ord_p1 VALUES LESS THAN (TO_DATE('01-MAR-1999','DD-MON-YYYY')) 
     INDEXING ON,
   PARTITION ord_p2 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY')) 
     INDEXING OFF,
   PARTITION ord_p3 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY')) 
     INDEXING ON,
   PARTITION ord_p4 VALUES LESS THAN (TO_DATE('01-MAR-2000','DD-MON-YYYY')),
   PARTITION ord_p5 VALUES LESS THAN (TO_DATE('01-MAR-2010','DD-MON-YYYY')));

A local or global partial index, can be created to follow the table indexing properties of the previous SQL example by specification of the INDEXING PARTIAL clause.

CREATE INDEX ORDERS_ORDER_TOTAL_GIDX ON ORDERS (ORDER_TOTAL)
   GLOBAL INDEXING PARTIAL;

The ORDERS_ORDER_TOTAL_GIDX index is created to index only those partitions that have INDEXING ON, and excludes the remaining partitions.

Updates to views include the following:

• Table Indexing Property - The column INDEXING is added to *_PART_TABLES, *_TAB_PARTITIONS, and *_TAB_SUBPARTITIONS views.

  This column has one of two values ON or OFF, specifying indexing on or indexing off.

• Partial Global Indexes as an Index Level Property - A new column INDEXING is added to the USER_INDEXES view. This column can be set to FULL or PARTIAL.

• Partial Global Index Optimization - The column ORPHANED_ENTRIES is added to the dictionary views USER_INDEXES and USER_IND_PARTITIONS to represent if a global index (partition) contains stale entries owing to deferred index maintenance during DROP/TRUNCATE PARTITION, or MODIFY PARTITION INDEXING OFF. The column can have one of the following values:

  • YES => the index (partition) contains orphaned entries

  • NO => the index (partition) does not contain any orphaned entries

There a few items to consider when partitioned indexes on composite partitions

When using partitioned indexes on composite partitions, note the following:

• Subpartitioned indexes are always local and stored with the table subpartition by default.

• Tablespaces can be specified at either index or index subpartition levels.