Database Reference
In-Depth Information
Partitioning Overview
Partitioning facilitates the management of very large tables and indexes using
divide and conquer
logic. Partitioning
introduces the concept of a
partition key
that is used to segregate data based on a certain range value, a list of
specific values, or the value of a hash function. If I were to put the benefits of partitioning in some sort of order, it
would be as follows:
1.
Increases availability of data
. This attribute is applicable to all system types, be they OLTP
or warehouse systems by nature.
2.
Eases administration of large segments by removing them from the database
. Performing
administrative operations on a 100GB table, such as a reorganization to remove migrated
rows or to reclaim “whitespace” left in the table after a purge of old information, would be
much more onerous than performing the same operation ten times on individual 10GB
table partitions. Additionally, using partitions, we might be able to conduct a purge routine
without leaving whitespace behind at all, removing the need for a reorganization entirely!
3.
Improves the performance of certain queries
. This is mainly beneficial in a large warehouse
environment where we can use partitioning to eliminate large ranges of data from
consideration, avoiding accessing this data at all. This will not be as applicable in a
transactional system, since we are accessing small volumes of data in that system already.
4.
May reduce contention on high-volume OLTP systems by spreading out modifications across
many separate partitions
. If you have a segment experiencing high contention, turning it
into many segments could have the side effect of reducing that contention proportionally.
Let's take a look at each of these potential benefits of using partitioning.
Increased Availability
Increased availability derives from the independence of each partition. The availability (or lack thereof ) of a single
partition in an object does not mean the object itself is unavailable. The optimizer is aware of the partitioning
scheme that is in place and will remove unreferenced partitions from the query plan accordingly. If a single partition
is unavailable in a large object, and your query can eliminate this partition from consideration, then Oracle will
successfully process the query.
To demonstrate this increased availability, we'll set up a hash partitioned table with two partitions, each in a
separate tablespace. We'll create an
EMP
table that specifies a partition key on the
EMPNO
column;
EMPNO
will be our
partition key. In this case, this structure means that for each row inserted into this table, the value of the
EMPNO
column
is hashed to determine the partition (and hence the tablespace) into which the row will be placed. First, we create two
tablespaces (
P1
and
P2
) and then a partitioned table with two partitions (
PART_1
and
PART_2
), with one partition in
each tablespace:
EODA@ORA12CR1> create tablespace p1 datafile size 1m autoextend on next 1m;
Tablespace created.
EODA@ORA12CR1> create tablespace p2 datafile size 1m autoextend on next 1m;
Tablespace created.
EODA@ORA12CR1> CREATE TABLE emp
2 ( empno int,
3 ename varchar2(20)
4 )
5 PARTITION BY HASH (empno)
