Partitioned Tables:Partitioned tables allow your data to be broken down into smaller, more manageable pieces called partitions.

Partitioning can also bring better performance, because many queries can ignore partitions that, according to the WHERE clause, won't have the requested rows, thereby reducing the amount of data to be scanned to produce a result set Although you are not required to keep each table or index partition (or subpartition) in a separate tablespace, it is to your advantage to do so. Storing partitions in separate tablespaces enables you to: Reduce the possibility of data corruption in multiple partitions Back up and recover each partition independently Improve availability(taking online backup of one part while other is offline in deiff tablespace), and performance(less I/O); Partitioning Methods There are several partitioning methods offered by Oracle Database: Range partitioning Hash partitioning List partitioning Range Partitioning Use range partitioning to map rows to partitions based on ranges of column values. This type of partitioning is useful when dealing with data that has logical ranges into which it can be distributed; for example, months of the year. Performance is best when the data evenly distributes across the range When creating range partitions, you must specify: Partitioning method: range Partitioning column(s) Partition descriptions identifying partition bounds Example:CREATE TABLE emp ( empno NUMBER,

ename varchar(10), sal number, comm. Number, job varchar2(20)) PARTITION BY RANGE (sal) ( PARTITION emp_p1 VALUES LESS THAN (1000) TABLESPACE tbs1, PARTITION emp_p2 VALUES LESS THAN (2000) TABLESPACE tbs2, PARTITION emp_p3 VALUES LESS THAN (3000) TABLESPACE tbs3, PARTITION emp_p4 VALUES LESS THAN (4000) TABLESPACE tbs4 ); Select data from partition table:Select * from emp; Select data from a specifics partition SELECT * FROM emp PARTITION(emp_p3);

Hash Partitioning

Use hash partitioning if your data does not easily lend itself to range partitioning, but you would like to partition for performance and manageability reasons. Hash partitioning provides a method of evenly distributing data across a specified number of partitions. Rows are mapped into partitions based on a hash value of the partitioning key.

Partitioning method: hash Partitioning column(s) Number of partitions or individual partition descriptions

Example:CREATE TABLE emp (id NUMBER, name VARCHAR2 (60), job varchar2(20)) PARTITION BY HASH (id) PARTITIONS 4 STORE IN (tbs1, tbs2, tbs3, tbs4); Or CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32)) PARTITION BY HASH(deptno) PARTITIONS 16;

Or CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32)) STORAGE (INITIAL 10K NEXT 20K MINEXTENTS 2 MAXEXTENTS 100) PARTITION BY HASH(deptno) (PARTITION p1 TABLESPACE TBS1, PARTITION p2 TABLESPACE TBS2, PARTITION p3 TABLESPACE TBS3, PARTITION p4 TABLESPACE TBS4);

List Partitioning
Use list partitioning when you require explicit control over how rows map to partitions. You can specify a list of discrete values for the partitioning column 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 the user has no control of the row to partition mapping. When creating list partitions, Partitioning method: list Partitioning column Partition descriptions, each specifying a list of literal values (a value list), which are the discrete values of the partitioning column that qualify a row to be included in the partition CREATE TABLE dept (deptno number, deptname varchar2(20), state varchar2(2)) PARTITION BY LIST (state) (PARTITION dept_p1 VALUES PARTITION dept_p2 VALUES PARTITION dept_p3 VALUES PARTITION dept_p4 VALUES PARTITION dept_p5 VALUES PARTITION dept_p6 VALUES OR CREATE TABLE DEPT ( DEPTNO NUMBER, DNAME VARCHAR2(10), STATE VARCHAR(30),

('RAJASTHAN', 'DELHI'), ('UP', 'AP', 'MP'), ('BIHAR', 'GUJRAT', 'HP'), ('HAR', 'MUM'), ('KER', 'WB'), ('KAR', 'TX'));

STATE_CODE VARCHAR(2)) STORAGE(INITIAL 10K NEXT 20K) TABLESPACE tbs5 PARTITION BY LIST (state_code) ( PARTITION DEPT_EAST VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ') STORAGE (INITIAL 20K NEXT 40K PCTINCREASE 50) TABLESPACE tbs8, PARTITION DEPT_WEST VALUES ('CA','AZ','NM','OR','WA','UT','NV','CO') NOLOGGING, PARTITION DEPT_SOUTH VALUES ('TX','KY','TN','LA','MS','AR','AL','GA'), PARTITION region_cen`tral VALUES ('OH','ND','SD','MO','IL','MI','IA'), PARTITION DEPT_NULL VALUES (NULL), PARTITION DEPT_UNKNOWN VALUES (DEFAULT) ); ADD A PARTITION:alter table emp_par split partition emp_p2 at (1000) into (partition emp_p1,partition emp_p2 tablespace tbs); create local index:CREATE INDEX loc_ind ON part_tab(deptno) LOCAL; or CREATE INDEX emp_ix ON emp(deptno) LOCAL STORE IN (tbs1, tbs2, tbs3); or CREATE INDEX i_cost1 ON costs_demo (prod_id) LOCAL (PARTITION part_ind_1, PARTITION part_ind_2, PARTITION part_ind_3, PARTITION part_ind_4);

create global index:-

CREATE INDEX month_ix ON sales(sales_month) GLOBAL PARTITION BY RANGE(sales_month) (PARTITION part_ind_1 VALUES LESS THAN (2) PARTITION part_ind_2 VALUES LESS THAN (3) PARTITION part_ind_3 VALUES LESS THAN (4) PARTITION part_ind_4 VALUES LESS THAN (5) PARTITION part_ind_5 VALUES LESS THAN (6) PARTITION part_ind_6 VALUES LESS THAN (7) PARTITION part_ind_7 VALUES LESS THAN (8)

PARTITION PARTITION PARTITION PARTITION PARTITION

part_ind_8 VALUES LESS THAN (9) part_ind_9 VALUES LESS THAN (10) part_ind_10 VALUES LESS THAN (11) part_ind_11 VALUES LESS THAN (12) part_ind_12 VALUES LESS THAN (MAXVALUE));

Or CREATE INDEX hgidx ON tab (c1,c2,c3) GLOBAL PARTITION BY HASH (c1,c2) (PARTITION p1 TABLESPACE tbs_1, PARTITION p2 TABLESPACE tbs_2, PARTITION p3 TABLESPACE tbs_3, PARTITION p4 TABLESPACE tbs_4); DBA_PART_TABLES DBA_TAB_PARTITIONS DBA_PART_KEY_COLUMNS DBA_PART_INDEXES DBA_IND_PARTITIONS