Partitioning is a way of breaking
up tables and indexes into pieces, instead of storing
them as one monolithic object. They have been introduced
in Oracle 8 with range partitioning and in oracle
8i, two new partitioning types have been introduced
: Hash and Composite.
Range Partitioning
This provides a way to partition
an object based on the range of the partition
key. The data is stored in different partitions
based on the range of the partition key.
create table sales (
customer_id number(6),
sale_date date,
amount number(10) )
partition by range(sale_date) (
partition p1 values less than ('01-APR-1999') tablespace
ts1,
partition p2 values less than ('01-JUL-1999') tablespace
ts2,
partition p3 values less than ('01-OCT-1999') tablespace
ts3,
partition p3 values less than (maxvalue) tablespace
ts4
);
In the above example, dba wants
to manage rolling data of sales by quarter. The
partition to which the row belongs to, is decided
based on the partition key(sale_date). This method
makes data very manageable and highly available
since individual partitions can be managed without
affecting the other partitions of the table.
Hash Partitioning
Range partitioning is only good
when defined partition boundaries and data patterns
exist. In the example below the table is partitioned
on customer_id and there is no good way of evenly
distributing data with range partitioning. Hash
Partitioning provides a very simple way to break
data up into evenly sized containers to be spread
across multiple devices.
create table sales (
customer_id number(6),
sale_date date,
amount number(10) )
partition by hash ( customer_id) (
partition p1 tablespace ts1,
partition p2 tablespace ts2,
partition p3 tablespace ts3,
partition p4 tablespace ts4 );
This is a good method to use when
the number of partitions don't change and there
are no defined data boundaries that you can partition
on. The advantages are Good Data distribution and
IO balancing.
Composite Partitioning
Hash partitioning is a combination
of range and hash partitioning and provides superior
manageability and availability benefits of range
partitioning with the data distribution advantages
of hash partitioning. In the following example,
the table is broken into 4 partitions based on the
sale_date range and again broken into 4 sub partitions
based on the hashed key of customer_id. So, each
tablespace will now hold 4 partitions and there
will be a total of 16 partitions in this scheme.
create table sales (
customer_id number(6),
sale_date date,
amount number(10) )
PARTITION BY RANGE(sale_date)
SUB PARTITION BY HASH(customer_id)
SUB PARTITIONS 4
STORE IN(ts1,ts2,ts3,ts4)
( PARTITION p1 VALUES LESS THAN('01-APR-1999'),
PARTITION q2 VALUES LESS THAN('01-JUL-1999'),
PARTITION q3 VALUES LESS THAN('01-OCT-1999'),
PARTITION q4 VALUES LESS THAN(MAXVALUE)
);
NOTES
** Tables with Long columns
cannot be partitioned.
** Until Oracle 8, you
could not partition tables with large objects.
DBAsupport.com Home Page
