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
