|Oracle9i Database Concepts
Release 1 (9.0.1)
Part Number A88856-02
This chapter describes partitioned tables and indexes. It covers the following topics:
Partitioning addresses key issues in supporting very large tables and indexes by letting you decompose them into smaller and more manageable pieces called partitions. SQL queries and DML statements do not need to be modified in order to access partitioned tables. However, once partitions are defined, DDL statements can access and manipulate individuals partitions rather than entire tables or indexes. This is how partitioning can simplify the manageability of large database objects. Also, partitioning is entirely transparent to applications.
Each partition of a table or index must have the same logical attributes, such as column names, datatypes, and constraints, but each partition can have separate physical attributes such as pctfree, pctused, and tablespaces.
Partitioning is useful for many different types of applications, particularly applications that manage large volumes of data. OLTP systems often benefit from improvements in manageability and availability, while data warehousing systems benefit from performance and manageability.
Partitioning offers these advantages:
Partition independence for partition maintenance operations lets you perform concurrent maintenance operations on different partitions of the same table or index. You can also run concurrent
SELECT and DML operations against partitions that are unaffected by maintenance operations.
For example, you could convert a nonpartitioned table to a partitioned table without needing to modify any of the
SELECT statements or DML statements which access that table. You do not need to rewrite your application code to take advantage of partitioning.
Figure 12-1 offers a graphical view of how partitioned tables differ from nonpartitioned tables.
Each row in a partitioned table is unambiguously assigned to a single partition. The partition key is a set of one or more columns that determines the partition for each row. Oracle9i automatically directs insert, update, and delete operations to the appropriate partition through the use of the partition key. A partition key:
MLSLABELpseudocolumn or a column of type
Tables can be partitioned into any number of separate partitions. Any table can be partitioned except those tables containing columns with
LONG RAW datatypes. You can, however, use tables containing columns with
CLOB or BLOB datatypes.
You can range partition index-organized tables. This feature is very useful for providing improved manageability, availability and performance for index-organized tables. In addition, data cartridges that use index-organized tables can take advantage of the ability to partition their stored data. Common examples of this are the Image and interMedia cartridges.
For partitioning an index-organized table:
OVERFLOWdata segments are always equipartitioned with the table partitions
Oracle provides the following partitioning methods:
Figure 12-2 offers a graphical view of the methods of partitioning.
Composite partitioning is a combination of other partitioning methods. Oracle currently supports range-hash composite partitioning. Figure 12-3 offers a graphical view of composite partitioning.
Oracle9i Data Warehousing Guide for more information about partitioning methods
Range partitioning maps data to partitions based on ranges of partition key values that you establish for each partition. It is the most common type of partitioning and is often used with dates. For example, you might want to partition sales data into monthly partitions.
When using range partitioning, there are a few rules to keep in mind:
VALUES LESS THANclause, which specifies a noninclusive upper bound for the partitions. Any binary values of the partition key equal to or higher than this literal are added to the next higher partition.
VALUES LESS THANclause on the previous partition.
MAXVALUEliteral can be defined for the highest partition.
MAXVALUErepresents a virtual infinite value that sorts higher than any other possible value for the partition key, including the null value.
A typical example is given below. The statement creates a table
(SALES_RANGE) that is range partitioned on the
CREATE TABLE sales_range (salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_amount NUMBER(10), sales_date DATE) PARTITION BY RANGE(sales_date) ( PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')), PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')), PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')), PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY')), )
List partitioning enables you to explicitly control how rows map to partitions. You do this by specifying a list of discrete values for the partitioning key in the description for each partition. This is different from range partitioning, where a range of values is associated with a partition and from hash partitioning, where a hash function controls the row-to-partition mapping. The advantage of list partitioning is that you can group and organize unordered and unrelated sets of data in a natural way.
The details of list partitioning can best be described with an example. In this case, let's say you want to partition a sales table by region. That means grouping states together according to their geographical location as in the following example.
CREATE TABLE sales_list (salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_state VARCHAR2(20), sales_amount NUMBER(10), sales_date DATE) PARTITION BY LIST(sales_state) ( PARTITION sales_west VALUES IN('California', 'Hawaii'), PARTITION sales_east VALUES IN ('New York', 'Virginia', 'Florida'), PARTITION sales_central VALUES IN('Texas', 'Illinois'), )
A row is mapped to a partition by checking whether the value of the partitioning column for a row falls within the set of values that describes the partition. For example, the following rows will be inserted as follows:
(10, 'Jones', 'Hawaii', 100, '05-JAN-2000')maps to partition
(21, 'Smith', 'Florida', 150, '15-JAN-2000')maps to partition
(32, 'Lee', 'Colorado', 130, '21-JAN-2000')will not map to any partition in the table
Unlike range and hash partitioning, multicolumn partition keys are not supported for list partitioning. If a table is partitioned by list, the partitioning key can only consist of a single column of the table.
Hash partitioning enables easy partitioning of data that does not lend itself to range or list partitioning. It does this with a simple syntax and is easy to implement. It is a better choice than range partitioning when:
The concepts of splitting, dropping or merging partitions do not apply to hash partitions. Instead, hash partitions can be added and coalesced.
CREATE TABLE sales_hash (salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_amount NUMBER(10), week_no NUMBER(2)) PARTITION BY HASH(salesman_id) PARTITIONS 4 STORE IN (data1, data2, data3, data4)
The above statement creates a table
SALES_HASH, which is hash partitioned on
Data1, data2, data3 and
data4 are tablespace names.
Composite partitioning partitions data using the range method, and within each partition, subpartitions it using the hash method. Composite partitioning provides the improved manageability of range partitioning and the data placement, striping, and parallelism advantages of hash partitioning. Composite partitioning supports historical operations, such as adding new range partitions, but also provides higher degrees of parallelism for DML operations and finer granularity of data placement through subpartitioning.
CREATE TABLE sales_composite (salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_amount NUMBER(10), sales_date DATE) PARTITION BY RANGE(sales_date) SUBPARTITION BY HASH(salesman_id) SUBPARTITIONS 4 PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')) ( SUBPARTITION sales_jan2000_1 TABLESPACE data1, SUBPARTITION sales_jan2000_2 TABLESPACE data2, SUBPARTITION sales_jan2000_3 TABLESPACE data3, SUBPARTITION sales_jan2000_4 TABLESPACE data4), PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),( SUBPARTITION sales_jan2000_1 TABLESPACE data1, SUBPARTITION sales_jan2000_2 TABLESPACE data2, SUBPARTITION sales_jan2000_3 TABLESPACE data3, SUBPARTITION sales_jan2000_4 TABLESPACE data4), PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')) ( SUBPARTITION sales_jan2000_1 TABLESPACE data1, SUBPARTITION sales_jan2000_2 TABLESPACE data2, SUBPARTITION sales_jan2000_3 TABLESPACE data3, SUBPARTITION sales_jan2000_4 TABLESPACE data4);
This statement creates a table
SALES_COMP that is range partitioned on the
SALES_DATE field and hash subpartitioned on
SALESMAN_ID. SALES_COMP will have 16 individual subpartitions. Figure 12-4 offers a graphical view of the above example.
Here are some suggestions for when to partition a table:
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).
Oracle9i Data Warehousing Guide for more information about partitioned indexes
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 enables Oracle to automatically keep the index partitions in sync with the table partitions, and makes each table-index pair independent. Any actions that make one partition's data invalid or unavailable will only affect a single partition.
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. Unique local indexes are useful for OLTP environments.
Figure 12-5 offers a graphical view of local partitioned indexes.
Global partitioned indexes are flexible in that the degree of partitioning and the partitioning key are independent from the table's partitioning method. They are commonly used for OLTP environments and offer efficient access to any individual record.
The highest partition of a global index must have a partition bound, all of whose values are
You cannot add a partition to a global index because the highest partition always has a partition bound of
ALTER INDEX SPLIT PARTITION statement. If a global index partition is empty, you can explicitly drop it by issuing the
ALTER INDEX DROP PARTITION statement. If a global index partition contains data, dropping the partition causes the next highest partition to be marked unusable. You cannot drop the highest partition in a global index.
By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:
In Oracle9i, Release 1 (9.0.1), these indexes can be maintained by appending the clause
UPDATE GLOBAL INDEXES to the SQL statements for the operation. There are two advantages to maintaining global indexes:
Oracle9i SQL Reference for more information about the
Figure 12-6 offers a graphical view of global partitioned indexes.
Global nonpartitioned indexes behave just like a nonpartitioned index. They are commonly used in OLTP environments and offer efficient access to any individual record.
Figure 12-7 offers a graphical view of global nonpartitioned indexes.
CREATE TABLE emp (empno NUMBER(4) NOT NULL, ename VARCHAR2(10), deptno NUMBER(2)) PARTITION BY RANGE (deptno) (PARTITION emp_part1 VALUES LESS THAN (11) TABLESPACE part1, PARTITION emp_part2 VALUES LESS THAN (21) TABLESPACE part2, PARTITION emp_part3 VALUES LESS THAN (31) TABLESPACE part3)
CREATE INDEX emp_glbbal_part_idx ON emp(empno) GLOBAL PARTITION BY RANGE(empno) (PARTITION p1 VALUES LESS THAN(5000), PARTITION p2 VALUES LESS THAN(MAXVALUE));
CREATE TABLE sales_range ( salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_amount NUMBER(10), sales_data DATE, PRIMARY KEY(sales_date, salesman_id)) ORGANIZATION INDEX INCLUDING salesman_id OVERFLOW TABLESPACE tabsp_overflow PARTITION BY RANGE(week_no) (PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')) OVERFLOW TABLESPACE p1_overflow, PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')) OVERFLOW TABLESPACE p2_overflow, PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')) OVERFLOW TABLESPACE p3_overflow, PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY')) OVERFLOW TABLESPACE p4_overflow)
You can create bitmap indexes on partitioned tables, with the restriction that the 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.
Here are a few guidelines for OLTP applications:
Here are a few guidelines for DSS applications:
Here are a few points to remember when using partitioned indexes on composite partitions:
Partitioning can help you improve performance and manageability. Some topics to keep in mind when using partitioning for these reasons are:
The Oracle server explicitly recognizes partitions and subpartitions. It then optimizes SQL statements to mark the partitions or subpartitions that need to be accessed and eliminates (prunes) unnecessary partitions or subpartitions from access by those SQL statements. In other words, partition pruning is the skipping of unnecessary index and data partitions or subpartitions in a query.
For each SQL statement, depending on the selection criteria specified, unneeded partitions or subpartitions can be eliminated. For example, if a query only involves March sales data, there is no need to retrieve data for the remaining eleven months. Such intelligent pruning can dramatically reduce the data volume, resulting in substantial improvements in query performance.
If the optimizer determines that the selection criteria used for pruning are satisfied by all the rows in the accessed partition or subpartition, it removes those criteria from the predicate list
(WHERE clause) during evaluation in order to improve performance. However, the optimizer cannot prune partitions if the SQL statement applies a function to the partitioning column (with the exception of the
TO_DATE function). Similarly, the optimizer cannot use an index if the SQL statement applies a function to the indexed column, unless it is a function-based index.
Pruning can eliminate index partitions even when the underlying table's partitions cannot be eliminated, but only when the index and table are partitioned on different columns. You can often improve the performance of operations on large tables by creating partitioned indexes that reduce the amount of data that your SQL statements need to access or modify.
(<, >, between) and
IN-list predicates are considered for partition pruning with range partitioning, and equality and IN-list predicates are considered for partition pruning with hash partitioning.
We have a partitioned table called
ORDERS has 6 months of data, January to June, with a partition for each month of data. If the following query is executed
Partition pruning is achieved by:
A partition-wise join is a join optimization that you can use when joining two tables that are both partitioned along the join column(s). With partition-wise joins, the join operation is broken into smaller joins that are performed sequentially or in parallel. Another way of looking at partition-wise joins is that they minimize the amount of data exchanged among parallel slaves during the execution of parallel joins by taking into account data distribution.
Oracle9i Data Warehousing Guide for more information about partitioning methods and partition-wise joins
Parallel execution dramatically reduces response time for data-intensive operations on large databases typically associated with decision support systems and data warehouses. In addition to conventional tables, you can use parallel query and parallel DML with range- and hash-partitioned tables. By doing so, you can enhance scalability and performance for batch operations.
The semantics and restrictions for parallel DML sessions are the same whether you are using index-organized tables or not.
Oracle9i Data Warehousing Guide for more information about parallel DML and its use with partitioned tables