Basics of Partitioning
Partitioning allows a table, index or index-organized table to be subdivided into
smaller pieces. Each piece of the database object is called a partition. Each
partition has its own name, and may optionally have its own storage characteristics. From the perspective of a database administrator, a partitioned
object has multiple pieces that can be managed either collectively or individually.
This gives the administrator considerable flexibility in managing partitioned
object. However, from the perspective of the application, a partitioned table is
identical to a non-partitioned table; no modifications are necessary when
accessing a partitioned table using SQL DML commands.
Figure 1: Application and DBA perspective of a partitioned table
Database objects - tables, indexes, and index-organized tables - are partitioned
using a 'partitioning key', a set of columns which determine in which partition a
given row will reside. For example the sales table shown in figure 1 is rangepartitioned
on sales date, using a monthly partitioning strategy; the table appears
to any application as a single, 'normal' table. However, the DBA can manage and
store each monthly partition individually, potentially using different storage tiers,
applying table compression to the older data, or store complete ranges of older
data in read only tablespaces.
Basic Partitioning Strategies
Oracle Partitioning offers three fundamental data distribution methods that control
how the data is actually going to placed into the various individual partitions,
namely:
Range: The data is distributed based on a range of values of the
partitioning key (for a date column as the partitioning key, the 'January-
2007' partition contains rows with the partitioning-key values between
'01-JAN-2007' and '31-JAN-2007'). The data distribution is a continuum
without any holes and the lower boundary of a range is automatically
defined by the upper boundary of the preceding range.
List: The data distribution is defined by a list of values of the partitioning
key (for a region column as the partitioning key, the 'North America'
partition may contain values 'Canada', 'USA', and 'Mexico'). A special
'DEFAULT' partition can be defined to catch all values for a partition key
that are not explicitly defined by any of the lists.
Hash: A hash algorithm is applied to the partitioning key to determine
the partition for a given row. Unlike the other two data distribution
methods, hash does not provide any logical mapping between the data
and any partition.
Using the above-mentioned data distribution methods, a table can be partitioned
either as single or composite partitioned table:
Single (one-level) Partitioning: A table is defined by specifying one of
the data distribution methodologies, using one or more columns as the
partitioning key. For example consider a table with a number column as
the partitioning key and two partitions 'less_than_five_hundred' and
'less_than_thousand', the 'less_than_thousand' partition contains rows
where the following condition is true: 500 <= Partitioning key <1000 You can specify Range, List, and Hash partitioned tables.
Composite Partitioning: A combination of two data distribution
methods are used to define a composite partitioned table. First, the table
is partitioned by data distribution method one and then each partition is
further subdivided into subpartitions using a second data distribution
method. All sub-partitions for a given partition together represent a
logical subset of the data. For example, a range-hash composite
partitioned table is first range-partitioned, and then each individual rangepartition
is further sub-partitioned using the hash partitioning technique.
Available composite partitioning techniques are range-hash, range-list,
range-range, list-range, list-list, and list-hash.
Index-organized tables (IOTs) can be partitioned using range, hash, and
list partitioning. Composite partitioning is not supported for IOTs.
0 comments:
Post a Comment