Skip Headers

Oracle® Dat abase Concepts
10g Release 1 (10.1)
Part Number B10743-01
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Master Index
Master Index		 Go to Feedback page
Feedback< /font>
Go to previous page
Previous		 Go to next page
Next
 View PDF

18 Partitioned Tables and Indexes

This chapter describes partitioned tables and indexes. It covers the following topics:

Introduction to Partitioning

Partitioning addresses key issues in supporting very large table s and indexes by letting you decompose them into smaller and more manageable pieces called partitions. SQL queries a nd DML statements do not need to be modified in order to access partitioned tables. However, after partitions are defined, DDL statem ents 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 tabl e or index must have the same logical attributes, such as column names, datatypes, and constraints, but each partition can have separ ate physical attributes such as pctfree, pctused, and tablespaces.

Partitioning is useful for many different types of applicat ions, 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.


Note:

All partitions of a partitioned object must reside in tablespac es of a single block size.


See Also:


Partitioning offers these advantages:

  • Partitioning enables d ata management operations such data loads, index creation and rebuilding, and backup/recovery at the partition level, rather than on the entire table. This results in significantly reduced times for these operations.

  • Partitioning improve s query performance. In many cases, the results of a query can be achieved by accessing a subset of partitions, rather than the entir e table. For some queries, this technique (called partition pruning) can provide order-of-magnitude gains in performance.

  • Partitioning can significantly reduce the impact of scheduled downtime for mainte nance operations.

    Partition independence for partition maintenance operations lets you perform concurrent maintenance operatio ns on different partitions of the same table or index. You can also run concurrent SELECT and DML operations against par titions that are unaffected by maintenance operations.

  • Partitioning increases the availability of missio n-critical databases if critical tables and indexes are divided into partitions to reduce the maintenance windows, recovery times, an d impact of failures.

  • Partitioning can be implemented without requiring any modifications to your applic ations. For example, you could convert a nonpartitioned table to a partitioned table without needing to modify any of the SELEC T statements or DML statements which access that table. You do not need to rewrite your application code to take advantage of partitioning.

Figure 18-1 offers a graphical view of how partitioned tables differ from nonpa rtitioned tables.

Figure 18-1 A View of Partitioned Tables

Description of cncpt162.gif follows
Description o f the illustration cncpt162.gif

Partition Key

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 ea ch row. Oracle automatically directs insert, update, and delete operations to the appropriate partition through the use of the partit ion key. A partition key:

  • Consists of an ordered list of 1 to 16 columns

  • Ca nnot contain a LEVEL, ROWID, or MLSLABEL pseudocolumn or a column of type ROWID

  • Can contain columns that are NULLable

Partitioned Tables

Tables can be partitioned into up to 64,000 separate partitions. Any table can be partitioned except those tables conta ining columns with LONG or LONG RAW datatypes. You can, however, use tables containing columns with CLOB or BLOB datatypes.




Note:

To reduce disk use and memory use (specifically, the buffer cache), you can sto re tables and partitioned tables in a compressed format inside the database. This often leads to a better scaleup for read-only opera tions. Table compression can also speed up query execution. There is, however, a slight cost in CPU overhead.

< strong>See Also:

"Table Compression"

Partitioned Index-Organized Tables

You can partition index-orga nized tables by range, list, or hash. Partitioned index-organized tables are very useful for providing improved manageability, availa bility, and performance for index-organized tables. In addition, data cartridges that use index-organized tables can take advantage o f the ability to partition their stored data. Common examples of this are the Image and interMedia cartridges.

For pa rtitioning an index-organized table:

  • Partition columns must be a subset of primary key columns

  • Secondary indexes can be partitioned — locally and globally

  • OVERFLOW data segments are always equipartitioned with the table partitions

Overview of Partitioning Methods

Oracle p rovides the following partitioning methods:

Figure 18-2 offers a graphica l view of the methods of partitioning.

Figure 18-2 List, Range, and Hash Partitioning< /p> Description of cncpt158.gif follows
Description of the illustration cncpt158.gif

Composite partitioning i s a combination of other partitioning methods. Oracle supports range-hash and range-list composite partitioning. F igure 18-3 offers a graphical view of range-hash and range-list composite partitioning.

Figure 18-3 Com posite Partitioning

Description of cncpt168.gif follows
Description of the illustration cncpt168.gif

Range Partitioning

Range partitioning maps data to partitions based on range s of partition key values that you establish for each partition. It is the most common type of partitioning and is often used with da tes. For example, you might want to partition sales data into monthly partitions.

When using range partitioning, consider the following rules:

  • Each partition has a VALUES LESS THAN clause, wh ich specifies a noninclusive upper bound for the partitions. Any values of the partition key equal to or higher than this literal are added to the next higher partition.

  • All partitions, except the first, have an implicit lower bound spec ified by the VALUES LESS THAN clause on the previous partition.

  • A MAXVALUE literal can be defined for the highest partition. MAXVALUE represents a virtual infinite value th at sorts higher than any other possible value for the partition key, including the null value.

A typical example is given in the following section. The statement creates a table (sales_range) that is range partitioned on the sales _date field.

Range Partitioning Example

CREATE TABLE sales_range 
(salesman_id  NUMBER(5), 
s
alesman_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')),
P
ARTITION 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 Partition ing

List partitioning enables you to explicitly control how rows map to partitions. You do this by specifying a list o f 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 mappin g. The advantage of list partitioning is that you can group and organize unordered and unrelated sets of data in a natural way.

< p>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.

List Partitioning Ex ample

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('Califor
nia', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois')
,
PARTITION sales_other VALUES(DEFAULT)
);

A row is mapped to a partition by checking whether the value of the partitioni ng column for a row falls within the set of values that describes the partition. For example, the rows are inserted as follows:

< ul>

  • (10, 'Jones', 'Hawaii', 100, '05-JAN-2000') maps to partition sales_west

  • (21, 'Smith', 'Florida' , 150, '15-JAN-2000') maps to partition sales_east

  • (32, 'Lee', 'Colorado', 130, '21-JAN-2000') maps to partition sales_other

    • Unlike range and hash partitioning, multicolumn partition keys are not supported for list partitioning. If a ta ble is partitioned by list, the partitioning key can only consist of a single column of the table.

    The DEFAULT pa rtition enables you to avoid specifying all possible values for a list-partitioned table by using a default partition, so that all ro ws that do not map to any other partition do not generate an error.

    Hash Partitioning

    Hash partitioning enables easy pa rtitioning of data that does not lend itself to range or list partitioning. It does this with a simple syntax and is easy to implemen t. It is a better choice than range partitioning when:

    • You do not know beforehand how much data maps int o a given range

    • The sizes of range partitions would differ quite substantially or would be difficult to balance manually

    • Range partitioning would cause the data to be undesirably clustered

    • Performance features such as parallel DML, partition pruning, and partition-wise joins are important

    The c oncepts of splitting, dropping or merging partitions do not apply to hash partitions. Instead, hash partitions can be added and coale sced.


    See Also:

    Oracle Database Administrator's Guide for more information abou t partition tasks such as splitting partitions

    Hash Partitioning Example 

    CREATE TABLE sales_hash
(salesman_id  NUMBER(5), 
salesman_name VARCHAR2(30), 
sales_amount  N
UMBER(10), 
week_no       NUMBER(2)) 
PARTITION BY HASH(salesman_id) 
PARTITIONS 4 
STORE IN (ts1, ts2, ts3, ts4);

    The pr eceding statement creates a table sales_hash, which is hash partitioned on salesman_id field. The tablespac e names are ts1, ts2, ts3, and ts4. With this syntax, we ensure that we create th e partitions in a round-robin manner across the specified tablespaces.

    Composite Partitioning

    Composite partitioning partitions data using the range method, and within each partition, subpartitions it using the hash or list method. Composite range-hash partitioning provides the improved manag eability of range partitioning and the data placement, striping, and parallelism advantages of hash partitioning. Composite range-lis t partitioning provides the manageability of range partitioning and the explicit control of list partitioning for the subpartitions.< /p>

    Composite partitioning supports historical operations, such as adding new range partitions, but also provides higher degrees o f parallelism for DML operations and finer granularity of data placement through subpartitioning.

    Composite Partitioning Range-Hash Exampl e

    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)
SUBPARTITION TEMPLATE(
SU
BPARTITION sp1 TABLESPACE ts1,
SUBPARTITION sp2 TABLESPACE ts2,
SUBPARTITION sp3 TABLESPACE ts3,
SUBPARTITION sp4 TABLESPACE ts4)
(PA
RTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY'))
 PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2
000','DD/MM/YYYY'))
 PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY'))
 PARTITION sales_apr2000 VALUES LES
S THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
 PARTITION sales_may2000 VALUES LESS THAN(TO_DATE('06/01/2000','DD/MM/YYYY')));
    < p>This statement creates a table sales_composite that is range partitioned on the sales_date field and hash subpartitioned on salesman_id. When you use a template, Oracle names the subpartitions by concatenating the partition n ame, an underscore, and the subpartition name from the template. Oracle places this subpartition in the tablespace specified in the t emplate. In the previous statement, sales_jan2000_sp1 is created and placed in tablespace ts1 while s ales_jan2000_sp4 is created and placed in tablespace ts4. In the same manner, sales_apr2000_sp1 is c reated and placed in tablespace ts1 while sales_apr2000_sp4 is created and placed in tablespace ts4. Figure 18-4 offers a graphical view of the previous example.

    Figure 18-4 Comp osite Range-Hash Partitioning

    Description of cncpt157.gif follows
    < a id="sthref2591" name="sthref2591" href="img_text/cncpt157.htm">Description of the illustration cncpt157.gif

    Composite Partitioning Range-List Example

    CREATE T
ABLE bimonthly_regional_sales
(deptno NUMBER, 
 item_no VARCHAR2(20),
 txn_date DATE, 
 txn_amount NUMBER, 
 state VARCHAR2(2))
PARTI
TION BY RANGE (txn_date)
SUBPARTITION BY LIST (state)
SUBPARTITION TEMPLATE(

    SUBPARTITION east VALU
ES('NY', 'VA', 'FL') TABLESPACE ts1,
SUBPARTITION west VALUES('CA', 'OR', 'HI') TABLESPACE ts2,
SUBPARTITION central VALUES('IL', 'TX
', 'MO') TABLESPACE ts3)

    (
PARTITION janfeb_2000 VALUES LESS THAN (TO_DATE('1-MAR-2000','DD-MON-YYY
Y')),
PARTITION marapr_2000 VALUES LESS THAN (TO_DATE('1-MAY-2000','DD-MON-YYYY')),
PARTITION mayjun_2000 VALUES LESS THAN (TO_DATE('
1-JUL-2000','DD-MON-YYYY'))
);

    This statement creates a table bimonthly_regional_sales that is range partiti oned on the txn_date field and list subpartitioned on state. When you use a template, Oracle names the subp artitions by concatenating the partition name, an underscore, and the subpartition name from the template. Oracle places this subpart ition in the tablespace specified in the template. In the previous statement, janfeb_2000_east is created and placed in tablespace ts1 while janfeb_2000_central is created and placed in tablespace ts3. In the same manner, mayjun_2000_east is placed in tablespace ts1 while mayjun_2000_central is placed in ta blespace ts3. Figure 18-5 offers a graphical view of the table bimonthly_regional_sales and its 9 individual subpartitions.

    Figure 18-5 Composite Range-List Partitioning< /p> Description of cncpt167.gif follows
    Description of the illustration cncpt167.gif

    When to Partition a Table

    Here are some suggestions for when to partition a table:

      Tables greater than 2GB should always be considered for partitioning.

    • Tables containin g historical data, in which new data is added into the newest partition. A typical example is a historical table where only the curre nt month's data is updatable and the other 11 months are read only.

    Overview of Partitioned Indexes

    Just like par titioned tables, partitioned indexes improve manageability, availability, performance, and scalability. They can either be partitione d independently (global indexes) or automatically linked to a table's partitioning method (local indexes). In general, you should use global indexes for OLTP applications and local indexes for data warehousing or DSS applications. Also, whenever possible, you should try to use local indexes because they are easier to manage. When deciding what kind of partitioned index to use, you should consider the following guidelines in order:

    1. If the table partitioning column is a subset of the index key s, use a local index. If this is the case, you are finished. If this is not the case, continue to guideline 2.

    2. If th e index is unique, use a global index. If this is the case, you are finished. If this is not the case, continue to guideline 3.

    3. If your priority is manageability, use a local index. If this is the case, you are finished. If this is not the case, con tinue to guideline 4.

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


      < table class="notealso" summary="This is a layout table to format a tip" title="This is a layout table to format a tip" dir="ltr" bord er="1" width="80%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0">

      See Also:

      O racle Data Warehousing Guide and Oracle Database Administrator's Guide for more information about partitioned indexes and how to decide which type to use

    Local 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. < a id="sthref2598" name="sthref2598">The reason for this is equipartitioning: each partit ion of a local index is associated with exactly one partition of the table. This enables Oracle to automatically keep the index parti tions in sync with the table partitions, and makes each table-index pair independent. Any actions that make one partition's data inva lid or unavailable only affect a single partition.

    Local partitioned indexes support more availability when there are partitio n 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.

    < br />

    See Also:

    Oracle Data Warehousing Guide more information about prefixed indexes

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

    A local index can be unique. Howev er, 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 18-6 offers a graphical view of local partitioned inde xes.

    Figure 18-6 Local Partitioned Index

    Descriptio n of cncpt161.gif follows
    Description of the illustration cncpt161.gif

    Global Partitioned Indexes

    Oracle of fers two types of global partitioned index: range partitioned and hash partitioned.

    Global Range Partitioned Indexes

    Global range 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 hig hest partition of a global index must have a partition bound, all of whose values are MAXVALUE. This ensures that all ro ws in the underlying table can be represented in the index. Global prefixed indexes can be unique or nonunique.

    You cannot add a partition to a global index because the highest partition always has a partition bound of MAXVALUE. If you wish to ad d a new highest partition, use the ALTER INDEX SPLIT PARTITION statement. If a gl obal 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.

    Global Hash Partitioned Indexes< /font>

    Global hash partitioned indexes improve performance by spreading out contention when the index is monotonically growin g. In other words, most of the index insertions occur only on the right edge of an index.

    Maintenance of Global Partitioned Indexes

    By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:

    ADD (HASH) 
COALESCE (HASH) 
DROP 
EXCHANGE
 
MERGE 
MOVE 
SPLIT 
TRUNCATE

    These indexes can be maintained by appending the clause UPDATE INDEXES to th e SQL statements for the operation. The two advantages to maintaining global indexes:

    • The index remains available and online throughout the operation. Hence no other applications are affected by this operation.

    • The index doesn't have to be rebuilt after the operation.

    Example:
    ALTER TABLE DROP PAR
TITION P1 UPDATE INDEXES;

    Note:

    This feature is supported only for heap-organized tables.

    Figure 18-7 offers a graphical view of global partitioned indexes.

    Figure 18-7 Global Partitioned I ndex

    Description of cncpt160.gif follows
    Description of the illustration cncpt160.gif

    < /div>

    Global Nonpartitioned Indexes

    Global n onpartitioned indexes behave just like a nonpartitioned index. They are commonly used in OLTP environments and offer efficient access to any individual record.

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

    Figure 18-8 Global Nonpartitioned Index

    Description of cn cpt159.gif follows
    Description of the illustration cncpt1 59.gif

    Miscellaneous Information about Creating Indexes on Partit ioned Tables

    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.

    Using Partitioned Indexes in OLTP Applications

    Here are a few guidelines for OLTP applications:

    • Global indexes and unique, local indexes provide better performance than nonunique local indexes because they minimize the number of index partition probes.

    • Local indexes offer better availability when there are partition or subpartition maintenance operations on the table.

      • Hash-partitioned global indexes offer better performance by spreading out contention when the index is monotonicall y growing. In other words, most of the index insertions occur only on the right edge of an index.

    U sing Partitioned Indexes in Data Warehousing and DSS Applications

    Here are a few guidelines for data warehousing and D SS applications:

    • Local indexes are preferable because they are easier to manage during data loads and du ring partition-maintenance operations.

    • Local indexes can improve performance because many index partitio ns can be scanned in parallel by range queries on the index key.

    Partitioned Indexes on Composite P artitions

    Here are a few points to remember when using partitioned indexes on composite partitions:

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

    • Ta blespaces can be specified at either index or index subpartition levels.

    Partitioning to Improve Performance

    Part itioning can help you improve performance and manageability. Some topics to keep in mind when using partitioning for these reasons ar e:

    Partition Pru ning

    The Oracle database server explicitly recognizes partitions and subpartitions. It then optimizes SQL statements t o 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 subpart itions 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, then 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 perfo rmance.

    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 co lumn (with the exception of the TO_DATE function). Similarly, the optimizer cannot use an index if the SQL statement app lies a function to the indexed column, unless it is a function-based index.

    Pruning can eliminate index partitions even when t he underlying table's partitions cannot be eliminated, but only when the index and table are partitioned on different columns. You ca n often improve the performance of operations on large tables by creating partitioned indexes that reduce the amount of data that you r SQL statements need to access or modify.

    Equality, range, LIKE, and IN-list predicates are conside red for partition pruning with range or list partitioning, and equality and IN-list predicates are considered for partit ion pruning with hash partitioning.

    Partition Pruning Example

    We have a partitioned table called cust_orders. The partition key for cust_orders is order_date. Let us assume that cust_orders has six months of data, January to June, with a partition for each month of data. If the following query is run:

    SELE
CT SUM(value)
FROM cust_orders
WHERE order_date BETWEEN '28-MAR-98' AND '23-APR-98';

    Partition pruning is achieved by:

    • First, partition elimination of January, February, May, and June data partitions. Then either:

        An index scan of the March and April data partition due to high index selectivity

        or

      • A full scan of the March and April data partition due to low index selectivity

    Partition-wise Joins

    A partition-wise join is a join op timization for joining two tables that are both partitioned along the join column(s). With partition-wise joins, the join operation i s broken into smaller joins that are performed sequentially or in parallel. Another way of looking at partition-wise joins is that th ey minimize the amount of data exchanged among parallel slaves during the execution of parallel joins by taking into account data dis tribution.


    See Also:

    Oracle Data Warehousing Guide for more information about partitioning methods and partition-wise joins

    Parallel DML

    Parall el execution dramatically reduces response time for data-intensive operations on large databases typically associated with decision s upport systems and data warehouses. In addition to conventional tables, you can use parallel query and parallel DML with range- and h ash-partitioned tables. By doing so, you can enhance scalability and performance for batch operations.

    The semantics and restr ictions for parallel DML sessions are the same whether you are using index-organized tables or not.


    See Also:

    Oracle Data Warehousing Guide for more information about parallel DML and its use with partitioned tables

    Go to previous page
    Previous    		 Go to next page
    < font size="-2">Next
    		 Oracle
    Copyright © 1993, 2003 Oracle C orporation
    All Rights Reserved.
    Go to Documentation Home
    Home    		 Go to Book List
    Book List    		 Go to Table of Contents
    Contents    		 Go to Index
    Index    		 Go to Master Index
    Master Index    		 Go to Feedback page
    Feedback