Partitioning
Partitioning:-Partitioning is a method by which we can do partition of a Table.Oracle provide the feature to divide big tables or indexes into small pieces .Table should be Partitioned when :-
1) Size of Tables is greater than 2 GB .
2) Table contains more than 1 millions records.
3) Contents of table need to distributed over different tablespaces.
Note:-Till Oracle 11g we can not convert directly Non-Partition Table into Partition Table.So we create Partition Table at time of creation of Table. Oracle 12c has the feature to convert Non-Partition Table into Partition Table.
Types Of Partitions :- There are 4 types are partitions available in Oracle 11g
1) Range partitions
2) List partitions
3) Hash partitions
4) Composite partitions
Benefits of Partitions :-
1) Manageability
2) Accessibility
3) Availability
RANGE PARTITIONS:-Range partitioning is a type of partitioning where we can store data in partitions on the basis of range value.
Note:-
1) Useful when Data has logical ranges into which it can be distributed,For example:- a range of dates
2) Data is mapped to partitions based on ranges of partition key values established for each partition
3) Each partition has a VALUES LESS THAN clause, which specifies a non inclusive upper bound for the partitions.
4) All partitions, except the first, have an implicit lower bound specified by the VALUES LESS THAN clause on the previous partition
5) A MAXVALUE literal can be defined for the highest partition. MAXVALUE represents a virtual infinite value
Example:-Creating range partitioned table
1) Partition Table without default Partition
SQL>Create table orders (order_id number(6),order_date date)
partition by range(order_date)
(partition p_1997 values less than ( to_date('01-JAN-1998','DD-MON-YYYY') ),
partition p_1998 values less than ( to_date('01-JAN-1999','DD-MON-YYYY') ),
partition p_1999 values less than ( to_date('01-JAN-2000','DD-MON-YYYY') )
);
We can add default partition by "Alter Table" Statement or by "Create Table" Statement
SQL>alter table orders add partition p_other values less than (maxvalue );
2) Partition Table with default Partition
SQL>Create table orders (order_id number(6),order_date date)
partition by range(order_date)
(partition p_1997 values less than ( to_date('01-JAN-1998','DD-MON-YYYY') ),
partition p_1998 values less than ( to_date('01-JAN-1999','DD-MON-YYYY') ),
partition p_1999 values less than ( to_date('01-JAN-2000','DD-MON-YYYY') ),
partition p_other values less than(maxvalue)
);
SQL>CREATE TABLE order_trans (trans_id NUMBER,trans_dt DATE,
product_code NUMBER,store_id NUMBER,trans_amount NUMBER(12,2))
partition BY range (trans_dt)
(
partition p_q1 VALUES less than ( to_date('04/01/2005','mm/dd/yyyy') ) ,
partition p_q2 VALUES less than ( to_date('07/01/2005','mm/dd/yyyy') ) ,
partition p_q3 VALUES less than ( to_date('10/01/2005','mm/dd/yyyy') ) ,
partition p_q4 VALUES less than ( to_date('01/01/2006','mm/dd/yyyy') ) ,
partition p_other VALUES less than (maxvalue)
);
Partition Table with every Partition in different Tablespace
SQL>CREATE TABLE order_trans (trans_id NUMBER,trans_dt DATE,
product_code NUMBER,store_id NUMBER,trans_amount NUMBER(12,2))
partition BY range (trans_dt)
(
partition p_q1 VALUES less than ( to_date('04/01/2005','mm/dd/yyyy') ) tablespace t1,
partition p_q2 VALUES less than ( to_date('07/01/2005','mm/dd/yyyy') ) tablespace t2,
partition p_q3 VALUES less than ( to_date('10/01/2005','mm/dd/yyyy') ) tablespace t3,
partition p_q4 VALUES less than ( to_date('01/01/2006','mm/dd/yyyy') ) tablespace t4,
partition p_other VALUES less than (maxvalue) tablespace t5
);
Note:- Tablespace t1,t2,t3,t4 and t5 should be created first.
Inserting records into range partitioned table :-
SQL> Insert into orders values(101,to_date('12-JAN-1997','DD-MON-YYYY'));
SQL> Insert into orders values(102,to_date('18-FEB-1998','DD-MON-YYYY'));
SQL> Insert into orders values(103,to_date('22-JUN-1999','DD-MON-YYYY'));
SQL> Insert into orders values(104,to_date('08-JAN-2001','DD-MON-YYYY'));
SQL> Insert into orders values(105,to_date('22-JAN-1994','DD-MON-YYYY'));
SQL> Insert into orders values(106,to_date('27-FEB-2014','DD-MON-YYYY'));
SQL> Insert into orders values(107,to_date('21-JUN-1985','DD-MON-YYYY'));
SQL> Insert into orders values(108,to_date('13-MAR-2007','DD-MON-YYYY'));
Note:- If default Partition is not created then you can not insert record that is not mapped with any Partition.
Example:-
SQL>insert into orders values(101,'12-JUN-2012');
ORA-14400: inserted partition key does not map to any partition.
Partition Key :-We create partition on the basis of data of a column, that column is called Partition Key.
Example:- In above example "order_date" column is a partition key.
LIST PARTITIONS:-List partitioning is a type of partitioning where we can store data in partitions on the basis of list of values of column.
Note:-
1) List Partitioning is useful for data that has discrete or distinct values.
2) Enables to group and organize unordered and unrelated sets of data.
3) Gives data warehouse administrators precise control over which data belongs in each partition.
4) Enables the partitioning strategy to closely model underlying business processes.
5) Unlike range and hash partitioning, multicolumn partition keys are not supported for list partitioning.
Example :-Creating list partitioned table
SQL> CREATE TABLE sales (product_id NUMBER,trans_amt NUMBER,
sales_dt DATE,state_code varchar2(2))
partition by list(state_code)
(
partition p_ct VALUES ('UP','MP','HR'),
partition p_ca VALUES ('DL','JK','HP'),
partition p_def VALUES (DEFAULT)
);
Inserting records into list partitioned table
SQL> Insert into sales values(101,1350,to_date('12-JAN-1997','DD-MON-YYYY'),'UP');
SQL> Insert into sales values(102,1850,to_date('10-JUN-1997','DD-MON-YYYY'),'JK');
SQL> Insert into sales values(103,2150,to_date('18-FEB-1997','DD-MON-YYYY'),'HP');
SQL> Insert into sales values(104,4750,to_date('21-MAY-1997','DD-MON-YYYY'),'MP');
HASH PARTITIONS:-Hash partitioning maps data to partitions based on a hashing algorithm that Oracle applies to the partitioning key that you identify.
Note:-1) Hash function applied to the partitioning key column to place row in required partition.
2) Balances the data distribution between all partitions.
3) Is an effective means of distributing data, because Oracle hashes the data into a number of partitions, each of which can reside on a separate device.
4) Hash Partitioning enables the use of performance features like Partition-wise joins when two tables are hash partitioned on the join key.
Disadvantage of Hash Partitioning :-
1) Not suitable for purging and archiving data models.
2) Partition pruning is limited to using equality or IN-list predicates.
3) User has no control of the row to partition mapping.
4) Partition maintenance tasks like splitting, dropping and merging cannot be carried out.
5) Partitions can only be added and coalesced.
Example1:-Creating hash partitioned table
SQL> CREATE TABLE cust (cust_id NUMBER,cust_name varchar2(20))
partition by hash (cust_id)
partitions 4;
Example2:-
SQL>CREATE TABLE trans (trans_id NUMBER,trans_dt DATE,product_code NUMBER,
store_id NUMBER,trans_amount NUMBER(12,2))
partition BY hash (trans_id)
(
partition trans1 tablespace trans1,
partition trans2 tablespace trans2,
partition trans3 tablespace trans3,
partition trans4 tablespace trans4
);
Inserting records into hash partitioned table
SQL> Insert into cust values(101,'RAMESH');
SQL> Insert into cust values(102,'SURESH');
SQL> Insert into cust values(103,'GANESH');
SQL> Insert into cust values(104,'MAHESH');
Interval Partitioning:-(This is the New feature for Oracle 11g)
Before 11g new partitions must be created in advance for new data that was the additional partitioning management overhead. 11g interval partitioning automates partition management for range partitioning.Automatic creation of range partitions based on interval.Segments are allocated as soon as new data arrives.Local indexes are created and maintained as well.
Data Dictionary related to Partition Information:-
dba_part_tables
dba_tab_partitions
dba_tab_subpartitions
dba_tab_cols
dba_part_indexes
dba_ind_partitions
dba_ind_subpartitions
dba_part_lobs
dba_lob_partitions
dba_lob_subpartitions
dba_subpartition_templates
dba_subpart_key_columns
No comments:
Post a Comment