Managing Schema Objects

Partitioned Table

Types of Tables:

a) Ordinary(heap oraganized) table: Data is stored as an unordered collection(heap).

b) Partitioned table: Data is divided into smaller, more manageable pieces.

c) Index-organized table: Data is sorted and stored in a B-tree index structure.

d)Clustered table: Related data from one or more tables are stored together.

Partitioning addresses key issues in supporting very large tables and indexes by letting you decompose them into smaller and more manageable pieces called partitions.

Advantages of Partitioning:

#Partitioning enables data management operations such data loads, index creation and rebuilding, and backup/recovery at the partition level, rather than on the entire table. This results in significantly reduced times for these operations.

#Partitioning improves query performance. In many cases, the results of a query can be achieved by accessing a subset of partitions, rather than the entire table. For some queries, this technique (called partition pruning) can provide order-of-magnitude gains in performance.

#Partitioning can significantly reduce the impact of scheduled downtime for maintenance operations.

#Partition independence for partition maintenance operations lets you perform concurrent maintenance operations on different partitions of the same table or index. You can also run concurrent SELECT and DML operations against partitions that are unaffected by maintenance operations.

#Partitioning increases the availability of mission-critical databases if critical tables and indexes are divided into partitions to reduce the maintenance windows, recovery times, and impact of failures.

#Partitioning can be implemented without requiring any modifications to your applications.

Partition Key

Each row in a partitioned table is unambiguously assigned to a single partition. The partition key is a set of one or more columns that determines the partition for each row.

A partition key:

Consists of an ordered list of 1 to 16 columns

Cannot contain a LEVEL, ROWID, or MLSLABEL pseudocolumn or a column of type ROWID

Can contain columns that are NULLable

Partitioning Methods:

Range Partitioning

Range partitioning maps data to partitions based on ranges of partition key values that you establish for each partition. It is the most common type of partitioning and is often used with dates. For example, you might want to partition sales data into monthly partitions.

Ex-

CREATE TABLE sales_range

(salesman_id  NUMBER(5),

salesman_name VARCHAR2(30),

sales_amount  NUMBER(10),

sales_date    DATE)

PARTITION BY RANGE(sales_date)

(

PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),

PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),

PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),

PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))

);

List Partitioning:

List partitioning enables you to explicitly control how rows map to partitions. You do this by specifying a list of discrete values for the partitioning key in the description for each partition. This is different from range partitioning, where a range of values is associated with a partition and from hash partitioning, where a hash function controls the row-to-partition mapping. The advantage of list partitioning is that you can group and organize unordered and unrelated sets of data in a natural way.

Ex-

CREATE TABLE sales_list

(salesman_id  NUMBER(5),

salesman_name VARCHAR2(30),

sales_state   VARCHAR2(20),

sales_amount  NUMBER(10),

sales_date    DATE)

PARTITION BY LIST(sales_state)

(

PARTITION sales_west VALUES('California', 'Hawaii'),

PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),

PARTITION sales_central VALUES('Texas', 'Illinois')

PARTITION sales_other VALUES(DEFAULT)

);

Hash Partitioning

Hash partitioning enables easy partitioning of data that does not lend itself to range or list partitioning. It does this with a simple syntax and is easy to implement.

Ex-

CREATE TABLE sales_hash

(salesman_id  NUMBER(5), 

salesman_name VARCHAR2(30), 

sales_amount  NUMBER(10), 

week_no       NUMBER(2)) 

PARTITION BY HASH(salesman_id) 

PARTITIONS 4 

STORE IN (data1, data2, data3, data4);

                                        

Composite Partitioning

Composite partitioning partitions data using the range method, and within each partition, subpartitions it using the hash or list method. Composite range-hash partitioning provides the improved manageability of range partitioning and the data placement, striping, and parallelism advantages of hash partitioning. Composite range-list partitioning provides the manageability of range partitioning and the explicit control of list partitioning for the subpartitions.

Ex-

CREATE TABLE sales_composite 

(salesman_id  NUMBER(5), 

 salesman_name VARCHAR2(30), 

 sales_amount  NUMBER(10), 

 sales_date    DATE)

PARTITION BY RANGE(sales_date) 

SUBPARTITION BY HASH(salesman_id)

SUBPARTITION TEMPLATE(

SUBPARTITION sp1 TABLESPACE data1,

SUBPARTITION sp2 TABLESPACE data2,

SUBPARTITION sp3 TABLESPACE data3,

SUBPARTITION sp4 TABLESPACE data4)

(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY'))

 PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY'))

 PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY'))

 PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))

 PARTITION sales_may2000 VALUES LESS THAN(TO_DATE('06/01/2000','DD/MM/YYYY')));

When to Partition a Table

Here are some suggestions for when to partition a table:

#Tables greater than 2GB should always be considered for partitioning.

#Tables containing historical data, in which new data is added into the newest partition.