Table & Index Partition

Partition:
Decompose a table or index into smaller, more manageable pieces, called partitions.

Each partition of a table or index must have the same logical attributes, such as column names, datatypes, and constraints, but each partition can have separate physical attributes such as pctfree, pctused, and tablespaces.

Partitioning is useful for many different types of applications, particularly applications that manage large volumes of data. OLTP systems often benefit from improvements in manageability and availability, while data warehousing systems benefit from performance and manageability.
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. Oracle9i automatically directs insert, update, and delete operations to the appropriate partition through the use of the partition key.
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
There are mainly four types of partitioning :

1.Range Partitioning
2.List Partitioning
3.Hash Partitioning
4.Composite Partitioning

You can get the partition related information form the views:
dba_tables
dba_tab_partitions

Task: Partition the Scott.emp table using SAL (range partitioning method ):

SQL> SELECT partitioned FROM user_tables WHERE table_name = 'EMP';
PAR
----
NO

SQL> CREATE TABLE emp_range
(EMPNO NUMBER(4) NOT NULL,
ENAME vARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2))
PARTITION BY RANGE (SAL)
(PARTITION emp_1000 VALUES LESS THAN('1000'),
PARTITION emp_2000 VALUES LESS THAN('2000'),
PARTITION emp_6000 VALUES LESS THAN('6000'));

SQL> insert into emp_range (select * from emp) ;
14 rows created;

SQL> drop table emp;
Table dropped.

SQL> alter table emp_range rename to emp ;
Table altered.

SQL> SELECT partitioned FROM user_tables WHERE table_name = 'EMP';
PAR
----
YES

SQL> SELECT * FROM emp;

SQl> SELECT * FROM emp PARTITION(emp_1000);

SQL> SELECT partition_name FROM dba_tab_partitions WHERE table_name = 'EMP';
PARTITION_NAME
------------------------------
EMP_1000
EMP_2000
EMP_6000

SQL> ALTER TABLE "SCOTT"."EMP" DROP PARTITION "EMP_6000";

SQL> ALTER TABLE "SCOTT"."EMP" ADD PARTITION "EMP_MAX" VALUES LESS THAN (MAXVALUE) TABLESPACE "SYSTEM" ;

SQL> SELECT partition_name FROM dba_tab_partitions WHERE table_name = 'EMP';
PARTITION_NAME
------------------------------
EMP_1000
EMP_2000
EMP_MAX

SQL> ALTER TABLE "DIP"."FOOD_RANGE" RENAME PARTITION "FOOD_4" TO "FOOD_3"



Range partition Example:
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 partition:
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 partition Example:
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 Range-Hash Example
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')));

Composite Partitioning Range-List Example
CREATE TABLE bimonthly_regional_sales(deptno NUMBER, item_no VARCHAR2(20), txn_date DATE, txn_amount NUMBER, state VARCHAR2(2))PARTITION BY RANGE (txn_date)SUBPARTITION BY LIST (state)SUBPARTITION TEMPLATE(
SUBPARTITION east VALUES('NY', 'VA', 'FL') TABLESPACE ts1,SUBPARTITION west VALUES('CA', 'OR', 'HI') TABLESPACE ts2,SUBPARTITION central VALUES('IL', 'TX', 'MO') TABLESPACE ts3)
( PARTITION janfeb_2000 VALUES LESS THAN (TO_DATE('1-MAR-2000','DD-MON-YYYY')), PARTITION marapr_2000 VALUES LESS THAN (TO_DATE('1-MAY-2000','DD-MON-YYYY')), PARTITION mayjun_2000 VALUES LESS THAN (TO_DATE('1-JUL-2000','DD-MON-YYYY')) );

Partitioned Indexes

Just like partitioned tables, partitioned indexes improve manageability, availability, performance, and scalability.
They can either be partitioned independently (global indexes)
or
automatically linked to a table's partitioning method (local indexes).

Example of a Local Index Creation
CREATE INDEX employees_local_idx ON employees (employee_id) LOCAL;

Example of a Global Index Creation
CREATE INDEX employees_global_idx ON employees(employee_id);

Example of a Global Partitioned Index Creation
CREATE INDEX employees_global_part_idx ON employees(employee_id)
GLOBAL PARTITION BY RANGE(employee_id)
(PARTITION p1 VALUES LESS THAN(5000),
PARTITION p2 VALUES LESS THAN(MAXVALUE));