Partitions in Oracle

Oracle partitions addresses the key problem of supporting very large tables and indexes by allowing you to decompose
them into smaller and more manageable pieces called partitions.

Once partitions are defined, SQL statements can access and manipulate the partitions
rather than entire tables or indexes.

Partitions are especially useful in data warehouse applications,
which commonly store and analyze large amounts of historical data.

Advantages
----------

1. Smaller and more manageable pieces of data ( Partitions )
2. import / export can be done at the " Partition Level".
3. Faster access of data
4. Partitions work independent of the other partitions.
5. Very easy to use

Types of Partitioning Methods
----------------------------
Range partitioning (introduced in Oracle 8)

Hash partitioning (introduced in Oracle 8i)

Composite partitioning (introduced in Oracle 8i)

List partitioning (introduced in Oracle 9i)

Interval partitioning (introduced in Oracle 11g)

System partitioning (introduced in Oracle 11g)

Reference partitioning (introduced in Oracle 11g)

RANGE Partitioning
------------------
This type of partitioning creates partitions based on the " Range of Column" values.
Most commonly used values for " Range Partition" is the Date field in a table.

CREATE TABLE invoices
(invoice_no    NUMBER NOT NULL,
 invoice_date  DATE   NOT NULL,
 comments      VARCHAR2(500))
PARTITION BY RANGE (invoice_date)
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION invoices_q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY')) TABLESPACE users);


CREATE TABLE ref_parent (
table_name   VARCHAR2(30),
order_date   DATE,
num_rows     NUMBER)
PARTITION BY RANGE(num_rows) (
PARTITION num_rows1 VALUES LESS THAN (100) TABLESPACE part1,
PARTITION num_rows2 VALUES LESS THAN (1000) TABLESPACE part2,
PARTITION num_rows3 VALUES LESS THAN (10000) TABLESPACE part3,
PARTITION num_rows4 VALUES LESS THAN (MAXVALUE) TABLESPACE part4);


Hash Partitioning Tables:
-------------------------

Hash partitioning is useful when there is no obvious range key, or
range partitioning will cause uneven distribution of data.
The number of partitions must be a power of 2 (2, 4, 8, 16...) .

CREATE TABLE invoices
(invoice_no    NUMBER NOT NULL,
 invoice_date  DATE   NOT NULL,
 comments      VARCHAR2(500))
PARTITION BY HASH (invoice_no)
PARTITIONS 4
STORE IN (users, users, users, users);or specified individually:

CREATE TABLE invoices
(invoice_no    NUMBER NOT NULL,
 invoice_date  DATE   NOT NULL,
 comments      VARCHAR2(500))
PARTITION BY HASH (invoice_no)
(PARTITION invoices_q1 TABLESPACE users,
 PARTITION invoices_q2 TABLESPACE users,
 PARTITION invoices_q3 TABLESPACE users,
 PARTITION invoices_q4 TABLESPACE users);

Composite Partitioning Tables:
-----------------------------
Composite partitioning allows range partitions to be hash subpartitioned on a different key.
 The greater number of partitions increases the possiblities for parallelism and reduces the chances of contention.
 The following example will range partition the table on invoice_date and subpartitioned these on the
 invoice_no giving a totol of 32 subpartitions:

CREATE TABLE invoices
(invoice_no    NUMBER NOT NULL,
 invoice_date  DATE   NOT NULL,
 comments      VARCHAR2(500))
PARTITION BY RANGE (invoice_date)
SUBPARTITION BY HASH (invoice_no)
SUBPARTITIONS 8
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')),
 PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')),
 PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')),
 PARTITION invoices_q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY'));


List Partitioning ( Only with 9i)
---------------------------------
Under this type of partitioning the records in a table are partitioned based on the List of values for a table .

CREATE TABLE Emp_list_partition
(empno NUMBER,
ename varchar2(20),
deptno NUMBER,
sal NUMBER,
job varchar2(20))
PARTITION BY LIST (deptno)
(PARTITION deptno1 VALUES (10,20) TABLESPACE users,
PARTITION deptno2 VALUES (30,40) TABLESPACE users,
PARTITION deptno3 VALUES (50) TABLESPACE users
);


SELECT table_name, tablespace_name, partitioned
FROM user_tables;

SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions;

INSERT INTO Emp_list VALUES (100, 'A', 10, 12300,'fds');
INSERT INTO Emp_list VALUES (100, 'A', 20, 12300,'fds');
INSERT INTO Emp_list VALUES (100, 'A', 30, 12300,'fds');
INSERT INTO Emp_list VALUES (100, 'A', 40, 12300,'fds');
INSERT INTO Emp_list VALUES (100, 'A', 20, 12300,'fds');
COMMIT;
SELECT * FROM Emp_list;
SELECT * FROM Emp_list PARTITION(deptno1);

PARTITION BY SYSTEM
-------------------

CREATE TABLE syst_part (
tx_id   NUMBER(5),
begdate DATE)
PARTITION BY SYSTEM (
PARTITION p1 TABLESPACE part1,
PARTITION p2 TABLESPACE part2,
PARTITION p3 TABLESPACE part3);

INSERT INTO syst_part VALUES (1, SYSDATE-10);

INSERT INTO syst_part PARTITION (p1) VALUES (1, SYSDATE-10);
INSERT INTO syst_part PARTITION (p2) VALUES (2, SYSDATE);
INSERT INTO syst_part PARTITION (p3) VALUES (3, SYSDATE+10);

SELECT * FROM syst_part PARTITION(p2);



Ur's
AmarAlam