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.
#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.
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.
Consists of an ordered list of 1 to 16 columns
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
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
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
