Skip to content

Oracle Partitions -> Complete knowledge article for the new oracle dba





you are a new oracle dba and you would like to know about oracle partition concepts. you would like to know the syntax for creation of a partition , the various types of partititons and in which oracle internal views you could find information about existing oracle partititons etc.

Then this article would give you indepth overview of oracle partition concepts.

first go to this oracle documentation link which explains you in detail about all these concepts.
oracle partitioning encyclopedia

oracle partitioning more details

oracle partitioning syntax quick reference

now read this fantastic metalink article

Top Partitioned Tables Issues [ID 165599.1]  


 

  Modified 17-MAR-2010     Type BULLETIN     Status PUBLISHED  
       
 
PURPOSE
-------
 
The purpose of this article is to discuss the top partitioned table issues 
and provide examples for partition operations.
 
I. Creating Partitioned Tables
               Range
        Interval
               Hash
               List
        Reference
        Composite
           Range-range (*11g specific)
           Range-hash
           Range-list 
           List-range(*11g specific)
           List-hash(*11g specific)
           List-list(*11g specific)
        System Partitioning
 
II. Partitioned Table Maintenance Operations
               Adding Partitions
               Coalescing Partitions
               Dropping Partitions
               Exchanging Partitions
               Merging Partitions
               Moving Partitions
               Renaming Partitions 
               Splitting Partitions
               Truncating Partitions
 
III. Common Errors/Incorrect Product Usage
 
 
IV. Partitioned Table Bugs
 
 
INTRODUCTION
------------
There are three types of partitioned tables in Oracle 8i: Range, Hash and 
Composite. Range partitioning requires that rows identified by a "partition key" 
fall into a predefined range of values. The value of a columns' "partition key"
determines the partition to which a row belongs.
 
A hash-partitioned table's rows have their physical location determined by
applying a hash value to the partition key column, providing for deterministic,
but well distributed physical data spread.
 
Composite partitioning is a combination of the basic data distribution methods; 
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 together represent a logical subset of the data.
 
Oracle9i adds a new partitioning model called list partitioning to the 
set of partition methods already being supported in the Oracle RDBMS.
The new List method allows explicit control over how rows map to partitions,
by allowing to specify a list of discrete values for the partitioning column
in the description of each partition. One should use list partitioning 
when you want to specifically map rows to partitions based on discrete values. 
 
Oracle 11g has added even more partitioning methods. Interval partitioning is 
an extension of range partitioning that instructs the database to automatically 
create partitions of a specified interval when data inserted into the table exceeds 
all of the range partitions. Reference partitioning enables you to partition a 
table based on the partitioning scheme of the table referenced in its referential 
constraint. System partitioning enables application-controlled partitioning for 
arbitrary tables. 
 
 
--------------------------------------------------------------------------------
 
I. Creating Partitioned Tables
 
Using the Range Partitioning Method
----------------------------------- 
 
Range partitioning maps rows to partitions based on a range of column values. 
This type of partitioning is best suited when dealing with data that has logical 
ranges into it can be distributed by. For example, months of the year, or 
a numeric value. Performance is best when the data is evenly distributed across
the range. If partitioning by range causes partitions to vary dramatically in 
size because of unequal distribution, you may want to consider one of the other
methods of partitioning. 
 
When creating range partitions, you must specify the method as "RANGE", the 
partitioning column(s) in the RANGE, and the partition boundaries.
 
For an example of how to create a range partitioned table, see:
Note:164874.1 Example of Script to Create a Range Partition Table.
 
For an example of inserting in to a range partitioned table, see:
Note: 165303.1. Examples of Inserting into Range Partitioned Tables.
 
 
Using the Hash Partitioning Method
---------------------------------- 
 
Hash partitioning is best used when the data does not easily lend itself to range 
partitioning, but you would like to partition for performance reasons. Hash 
partitioning evenly distributes data across a specified number of partitions. 
Rows are mapped into partitions based on a hash value of the partitioning key. 
 
When creating hash partitions, you must specify the method as "HASH", the 
partitioning column(s) in the HASH, and the number of partitions or individual
partition descriptions.
 
In order for rows to be distributed relatively evenly in a hash partitioned table these two requirements must be met:
 
The number of partitions are to the power of 2.
The partition key is sequential or near sequential.
 
For an example of how to create a hash partitioned table, see:
Note:164873.1 Example of Script to Create a Hash Partition Table.
 
 
Using the Composite Partitioning Method
---------------------------------------
 
Previously the only composite partitioning methods supported were Range-List 
and Range-Hash. With Oracle Database 11g list partitioning can be a top level 
partitioning method for composite partitioned tables giving us List-List, List-Hash, 
List-Range and Range-Range composite methods. With the Interval partitioning, the 
following composite partitioning methods are also supported: Interval-Range, 
Interval-List and Interval-Hash. 
 
* Range-Range Partitioning: Composite range-range partitioning enables logical range 
partitioning along two dimensions; for example, partition by order_date and range 
subpartition by shipping_date. 
 
* List-Range Partitioning: Composite list-range partitioning enables logical range 
subpartitioning within a given list partitioning strategy; for example, list partition 
by country_id and range subpartition by order_date. 
 
* List-Hash Partitioning: Composite list-hash partitioning enables hash sub-partitioning 
of a list-partitioned object; for example, to enable partition-wise joins. 
 
* List-List Partitioning: Composite list-list partitioning enables logical list 
partitioning along two dimensions; for example, list partition by country_id and 
list subpartition by sales_channel. 
    
For an example of how to create a composite partitioned table, see:
Note:165924.1 Example of Script to Create a Composite Partition Table
Note:452447.1 11g Partitioning Enhancements
 
 
Using the List Partitioning Method
-----------------------------------
 
List partitioning is a new partition method introduced in Oracle9i,
where one can control how rows map to partitions. It allows for the 
distribution of data based on discrete column values. Unordered and 
unrelated sets of data can be grouped together. As there is no relationship 
between partitions this method is ideal for columns that consist of discrete
values; it also provides powerful data-management capabilities.
 
Feature Usage
Currently supported only for heap tables.
Multicolumn partitioning not supported.
The specified literal values must be unique across all literal values of all 
partition value lists of the object.
NULL can be specified as a partition literal value.
MAXVALUE cannot be specified.
All lists must have at least one literal.
Partition pruning, partition wise joins, and parallelism are supported.
Local indexes and global range partitioned indexes are supported.
 
When creating List partitions, you must specify the method as "LIST", the 
partitioning column in the LIST, and the partition descriptions with the set of 
values.
 
For introduction to the new partitioning method LIST Partitioning, see: 
Note:149116.1 Oracle9i Partitioning Enhancements,LIST Partitioning
 
 
Using the Interval Partitioning Method
---------------------------------------
 
Introduced in 11g, interval partitions are extensions to range partitioning. Interval 
partitioning resolves the limitations built into range partitioning when a specific range 
is unknown by the developer or DBA creating the partitions for the table. 
 
This is a helpful addition to Range Partitioning where Oracle automatically creates a 
partition when the inserted value exceeds all other partition ranges. 
 
As such the requirement of interval partitioning dictates that at least one range partition 
is specified. Once the range partitioning key is given for the high value of the range 
partitions, this transition point is used as the baseline to create interval partitions 
beyond this point. 
 
For more information on the Interval Partitioning Method, see:
Note:757754.1 Interval Partitioning By Week
 
 
Using the Reference Partitioning Method
---------------------------------------
 
Introduced in 11g, reference partitions provide the ability to partition a table based on 
the partitioning scheme of the table referenced in its referential constraint. 
 
* A table can now be partitioned based on the partitioning method of a table referenced in 
its referential constraint. 
* Tables with a parent/child relationship can be equi-partitioned by inheriting the 
partitioning key from the parent table without duplication the key columns 
* If the parent table is a composite-partitioned table, then the table will have one 
partition for each subpartition of its parent. 
 
For more information on the Reference Partitioning Method, please see:
Note:467019.1 11g Feature: Reference Partitioning
 
Using the System Partitioning Method
------------------------------------
 
The most fundamental difference between system partitioning and other methods is that 
system partitioning does not have any partitioning keys so the mapping of the rows to 
a particular partition is not implicit. Instead, you specify the partition to which a 
row maps by using partition extended syntax when inserting a row. 
 
Without a partitioning key the usual performance benefits of partitioned tables are not 
available for system partitioned tables. There is no support for traditional partition 
pruning or partition wise joins. Partition pruning is only achieved by accessing the same 
partitions in the system partitioned tables as those that were accessed in the base table. 
 
System partitioned tables do provide the manageability advantages of equi-partitioning. 
For example, a nested table can be created as a system partitioned table that has the 
same number of partitions as the base table. A domain index can be backed up by a system 
partitioned table that has the same number of partitions as the base table. 
 
For more information on the Reference Partitioning Method, please see:
Note:452447.1 11g Partitioning Enhancements
 
 
--------------------------------------------------------------------------------
 
II. Partitioned Table Maintenance Operations
 
Adding a Range, Hash, Composite Partition or Composite Subpartition
 
The ALTER TABLE...ADD PARTITION command allows you to add an extra partition 
beyond the last partition as long as the upper limit is not equal to MAXVALUE.
If it's equal, then adding a partition would be impossible. If you wish to add 
a partition at the beginning or in the middle of a table, or if the partition 
bound on the highest partition is MAXVALUE, you should instead use the SPLIT 
PARTITION statement. When the partition bound of the highest partition is 
anything other than MAXVALUE, you can add a partition using the 
ALTER TABLE...ADD PARTITION statement. 
 
You can add partitions to range, hash, or composite partitioned tables. You can 
only add subpartitions to a composite-partitioned table.
 
Adding a Partition to a Range-Partitioned Table
-----------------------------------------------
 
You can use the ALTER TABLE...ADD PARTITION statement to add a new partition to
the "high" end (the point after the last existing partition). If you want to add
a partition at the beginning or in the middle of a table, use the 
SPLIT PARTITION clause. 
 
Example:
Note:166652.1 Example of Script to Maintain Range Partitioned Table
 
Adding Index Partitions
-----------------------
 
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.
 
When there are local indexes defined on the table and you issue the 
ALTER TABLE...ADD PARTITION statement, a matching partition is also added to 
each local index. Since Oracle assigns names and default physical storage 
attributes to the new index partitions, you may wish to rename or alter them 
after the ADD operation is complete. 
 
Adding a partition does not affect the local or global indexes. If the table 
has a local index, all local index partitions stay in USABLE status. Any global 
index, or all partitions of a partitioned global index will also remain USABLE 
status. 
 
--------------------------------------------------------------------------------
 
Coalescing Partitions
 
COALESCE can be considered a replacement for the MERGE partition operation, 
which is not supported for hash partitioning. Coalescing partitions is a way of
reducing the number of partitions in a hash-partitioned table, or the number of
subpartitions in a composite-partitioned table. When a hash partition is 
coalesced, its contents are redistributed into one or more remaining partitions
determined by the hash function. The specific partition that is coalesced is 
selected by the database, and is dropped after its contents have been 
redistributed. 
 
Any local index partition corresponding to the selected partition is also 
dropped. Local index partitions corresponding to the one or more absorbing 
partitions are marked UNUSABLE, and must be rebuilt. Any global index is 
marked unusable. 
 
Coalescing a Partition in a Hash-Partitioned Table
--------------------------------------------------
 
The ALTER TABLE...COALESCE PARTITION statement is used to coalesce a partition
in a hash-partitioned table. 
 
Coalescing a Subpartition in a Composite-Partitioned Table
 
Example:
Note:93767.1 New 8.1.X coalesce partition/subpartition option of the 
ALTER TABLE command
 
--------------------------------------------------------------------------------
 
 
Dropping Partitions
 
You can drop partitions from range or composite-partitioned tables. For 
hash-partitioned tables, or hash subpartitions of composite-partitioned tables,
you must perform a coalesce instead. 
 
Use the ALTER TABLE...DROP PARTITION statement to drop a table partition from 
either a range or composite partitioned table. If you want to preserve the data
in the partition, you should merge the data into an adjacent partition instead.
 
If there are local indexes defined for the table, this statement also drops the
matching partition or subpartitions from the local index. Any global 
nonpartitioned indexes on the table will be marked UNUSABLE, and all partitions
of any global partitioned indexes will be marked UNUSABLE, unless the partition
being dropped or its subpartitions are empty. 
 
Dropping a Partition from a Table Example:
Note:166652.1 Example of Script to Maintain Range Partitioned Table  
 
Dropping Index Partitions
-------------------------
 
You cannot explicitly drop a partition of a local index. Instead, local index 
partitions are dropped only when you drop a partition from the underlying table.
 
--------------------------------------------------------------------------------
 
 
Exchanging Hash or Range Partitions
 
A quick note on indexes: A global index will require rebuilding after an 
exchange partition. If the INCLUDING INDEXES clause is used in the exchange,
local indexes will be exchanged with the corresponding regular indexes. If the 
EXCLUDING INDEXES clause is used, all the local index partitions corresponding 
to the partition and all the regular indexes on the exchanged table are marked 
as unusable and will have to be rebuilt.
 
Exchanging a Hash or Range Partition
------------------------------------
 
To exchange a partition of a range or hash-partitioned table with a 
nonpartitioned table, or the reverse, you use the 
ALTER TABLE...EXCHANGE PARTITION statement. 
 
Examples of how to Exchange a Non-Partitioned Table with a Range Partitioned 
Table:
Note:166652.1 Example of Script to Maintain Range Partitioned Table
Note:1070693.6 How to partition a non-partitioned table
 
--------------------------------------------------------------------------------
 
 
Merging Partitions
 
The ALTER TABLE...MERGE PARTITIONS statement merges the contents of two 
adjacent range partitions into one partition. The results is a partition that 
inherits the higher upper bound of the two merged partitions. The two original 
partitions are dropped, as well as any corresponding local indexes. Any global 
nonpartitioned indexes on the table will be marked UNUSABLE, and all partitions
of any global partitioned indexes will be marked UNUSABLE, if the partitions
being merged are not empty. 
 
Unless the involved partitions or subpartitions are empty, Oracle marks 
corresponding local index partitions or subpartitions UNUSABLE.
 
You cannot use this statement for a hash-partitioned table or for hash 
subpartitions of a composite-partitioned table. However, you can use COALESCE 
discussed above.
 
Merging Range Partitions
------------------------
 
Example:
Note:166652.1 Example of Script to Maintain Range Partitioned Table
 
--------------------------------------------------------------------------------
 
Moving Partitions
 
You can use the MOVE PARTITION clause of the ALTER TABLE statement to re-cluster
data and reduce fragmentation, move a partition to another tablespace, or 
modify create-time attributes.
 
When the partition you are moving contains data, MOVE PARTITION marks the 
matching partition in each local index, and all global index partitions as 
unusable. You must rebuild these index partitions after issuing MOVE PARTITION.
Global indexes must also be rebuilt.  
 
You can rebuild the entire index by rebuilding each partition individually using
the ALTER INDEX...REBUILD PARTITION statement. You can perform these rebuilds 
concurrently. You can also simply drop the index and re-create it. 
 
If the partition is not empty, MOVE PARTITION marks all corresponding local 
index partitions, all global nonpartitioned indexes, and all the partitions of 
global partitioned indexes, UNUSABLE. 
 
 
Restrictions: 
You cannot MOVE an entire partitioned table (either heap or index organized). 
You must move individual partitions or subpartitions. 
 
If a partition is a hash partition, the only attribute you can specify in this 
clause is TABLESPACE. 
 
You cannot move a partition of a composite-partitioned table. You must move 
each subpartition separately with the move subpartition clause. 
 
You cannot specify this clause (MOVE PARTITION) for a partition containing 
subpartitions. However, you can move subpartitions using the 
move subpartition clause. 
 
Moving a Partition
------------------
 
Example
Note:166652.1 Example of Script to Maintain Range Partitioned Table
 
Moving a Subpartition
---------------------
 
You can use the move subpartition clause to move the table subpartition 
to another segment. If you do not specify TABLESPACE, the subpartition will 
remain in the same tablespace. 
 
Unless the subpartition is empty, Oracle marks all corresponding local index 
partitions, all global nonpartitioned indexes, and all the partitions of global
partitioned indexes, UNUSABLE. 
 
 
--------------------------------------------------------------------------------
 
Renaming Partitions
 
You can use the rename partition option to rename a table partition or 
subpartition. For both partitions and subpartitions, the new name must be 
different from all existing partitions and subpartitions of the same table. 
 
Example: 
Note:166652.1 Example of Script to Maintain Range Partitioned Table
 
--------------------------------------------------------------------------------
 
Splitting a Range or Index Partition
 
The SPLIT PARTITION clause of the ALTER TABLE or ALTER INDEX statement is used 
to redistribute the contents of a partition into two new partitions. You may 
want to do this when a partition becomes too large and causes backup, recovery,
or maintenance operations to take a long time to complete. You can also use the
SPLIT PARTITION clause to redistribute the I/O loads. 
 
The split_partition_clause lets you create, from an original partition 
partition_name_old, two new partitions, each with a new segment and new physical
attributes, and new initial extents. The segment associated with 
partition_name_old is discarded.
 
If the partition you are splitting contains data, the 
ALTER TABLE...SPLIT PARTITION statement marks UNUSABLE the new partitions
(there are two) in each local index, all global index partitions, and any 
global nonpartitioned index. You must rebuild such affected indexes or index 
partitions. 
 
This clause cannot be used for hash partitions or subpartitions. 
 
Splitting a Range Partition
-----------------------
 
Example:
Note:166652.1 Example of Script to Maintain Range Partitioned Table
 
---------------
 
Splitting Index Partitions
--------------------------
 
You cannot explicitly split a partition in a local index. A local index 
partition is split only when you split a partition in the underlying table. 
 
--------------------------------------------------------------------------------
 
Truncating Partitions
 
TRUNCATE PARTITION removes all rows from a partition or, if the table is 
composite-partitioned, all rows from partition's subpartitions. TRUNCATE
SUBPARTITION removes all rows from subpartition. 
 
If the table contains any LOB columns, the LOB data and LOB index segments for
this partition are also truncated. If the table is composite-partitioned, the 
LOB data and LOB index segments for this partition's subpartitions are 
truncated. 
 
If the partition or subpartition to be truncated contains data, you must first 
disable any referential integrity constraints on the table. Alternatively, you 
can delete the rows and then truncate the partition. 
 
For each partition or subpartition truncated, Oracle also truncates 
corresponding local index partitions and subpartitions. If those index 
partitions or subpartitions are marked UNUSABLE, Oracle truncates them and 
resets the UNUSABLE marker to VALID. In addition, if the truncated partition or
subpartition, or any of the subpartitions of the truncated partition are not 
empty, Oracle marks as UNUSABLE all global nonpartitioned indexes and partitions
of global indexes defined on the table. 
 
DROP STORAGE-Specify DROP STORAGE to deallocate space from the deleted rows and
make it available for use by other schema objects in the tablespace. 
 
REUSE STORAGE-Specify REUSE STORAGE to keep space from the deleted rows 
allocated to the partition or subpartition. The space is subsequently available
only for inserts and updates to the same partition or subpartition. 
 
Example:
Note:166652.1 Example of Script to Maintain Range Partitioned Table
 
 
 
 
--------------------------------------------------------------------------------
 
III. Common Errors/Incorrect Product Usage
 
ORA-14099 Exchanging a Partition
Note:135762.1
 
ORA-14080 Splitting a Partition
Note:100299.1
 
ORA-14074 Adding a New Table Partition
Note:100295.1
 
--------------------------------------------------------------------------------
 
IV. Partitioned Table Bugs
 
Note:165510.1 discusses common bugs customers encounter when using the 
partitioning feature. If no fix is available, we have tried to provide a
workaround if one could be determined.
 
--------------------------------------------------------------------------------
 
 
RELATED DOCUMENTS
-----------------
 
Note:93767.1 New 8.1.X coalesce partition|subpartition option of the ALTER TABLE command
Note:105317.1 How to Implement Partitioning in Oracle Versions 8 and 8i
Note:1070693.6 How to partition a non-partitioned table
Note:90987.1 Oracle 8i New Feature: Composite-Partitioned Table Exchange Enhancement
Note:47966.1 8.0 FAQ: Oracle8 Partitions
Note:165309.1 Top Partitioned Indexes Issues
Note:166215.1 Top Partition Performance Issues
Note:165510.1 Partition Table Bugs
Note:135762.1 ORA-14099 Using Exchange Partition Option: Oracle 8.x
Note:100299.1 ORA-14080 when trying to Split a Partition.
Note:100295.1 8.X ORA-14074 Received when Adding a New Table Partition
Note:757754.1 Interval Partitioning By Week
Note:452447.1 11g Partitioning Enhancements
Note:467019.1 11g Feature: Reference Partitioning
Oracle8 and Oracle8i Concepts Guide
Oracle8 and Oracle 8i SQL Reference
 
VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.