Database Infrastructure Engineer (NYC) Next Step Systems US-NY-New York Post A Job | Post A Resume

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