Partition in oracle
Partition in oracle:-
- Partition enhave high performance,helps reduce the total cost of ownership for storing large amounts of data.
- Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces.
- Range-hash partitioning was introduced in Oracle 8i
- Range-list partitioning was introduced in Oracle 9i
- Range-range partitioning was introduced in Oracle 11g
- List-range partitioning was introduced in Oracle 11g
- List-hash partitioning was introduced in Oracle 11g
- List-list partitioning was introduced in Oracle 11g
- Interval-range partitioning was introduced in Oracle 11g
- Interval-list partitioning was introduced in Oracle 11g
- Interval-hash partitioning was introduced in Oracle 11g
- Hash-hash partitioning was introduced in Oracle 11gR2
Partitioning Key:-
- Each row in a partitioned table is unambiguously assigned to a single partition.
- Oracle automatically directs DML operations to the appropriate partition through the use of the partitioning key.
Partitioned Tables:-
- Any table can be partitioned into a million separate partitions
- Partitions does not allows tables containing columns with LONG or LONG RAW datatypes.
When to Partition a Table:-
- Tables greater than 2 GB.
- Tables containing historical data,data is added newest partition. -historical table where only the current month's data is updatable others in read-only.
- content of a Table data across different types of storage devices.
- Avoid rebuilding the entire index when data is removed.
- Reduce the impact of index skew caused by index.
Partitioning for Performance:-
Partition Pruning.
Partition-Wise Joins.
1.Partition Pruning:-
- If you have selected one partition,and eliminate all except select this is call partition -pruning.
- It happens historical Data.
2.Partition-Wise Joins:-
- Partitioning can also improve the performance of multi-table joins by using a technique known as partition-wise joins.
- when two tables are being joined together and both tables are partitioned on the join key.
Partitioning Strategies:-
1.Range Partition.
2.List Partition.
3.Hash Partition.
4.Composite Partition.
Composite Range-Range Partition
Composite Range-Hash Partition
Composite Range-List Partition
Composite List-Range Partition
Composite List-Hash Partition
Composite List-List Partition
5.system partition
5.Interval Partition
1.Range Partitioning:-
- Range partitioni maps data to partitions based on ranges of values of the partitioning key that you establish for each partition.
- Each partition has a VALUES LESS THAN clause month to month end like 1-jan-2018 to 31-jan-2018 store partiton 1.
- A MAXVALUE literal can be defined for the highest partition.
Create table Emp(emp_no number(2),emp_name varchar(2))
partition by range(emp_no)
(partition p1 values less than(100),
partition p2 values less than(200),
partition p3 values less than(300),
partition p4 values less than(maxvalue));
To view Partition based records:-
select * from emp
Select * from emp partition(p1);
To Alter partition:-
Alter table emp add partition p5 values less than(400);
Dropping a partition
alter table emp drop partition p1;
Renaming a partition
alter table emp rename partition p3 to p6;
Truncate a partition
alter table emp truncate partition p5;
Splitting a partition
alter table emp split partition p2 at(120) into (partition p21,partition p22);
Exchanging a partition
alter table emp exchange partition p2 with table Emp2;
Moving a partition
alter table emp move partition p2 tablespace tbs8;
List Partitioning
- List partitioning enables you to explicitly control how rows map to partitions by specifying a list of discrete values to each.
- The North America partition might contain values Canada, USA, and Mexico.
- The advantage of list partitioning is that you can group and organize unordered .
Examples:-
Create table Employee (Emp_no number(2),Emp_name varchar(2))
partition by list(Emp_no)
(partition p1 values(1,2,3,4,5),
partition p2 values(6,7,8,9,10),
partition p3 values(11,12,13,14,15),
partition p4 values(16,17,18,19,20));
Hash Partitioning
Hash partitioning maps data to partitions based on a hash algorithm that Oracle applies to the partition key that you identify.
Examples:-
Create table Employee(emp_no number(2),emp_name varchar(2))
partition by hash(emp_no)
partitions 5;
Here oracle automatically gives partition names like
SYS_P1
SYS_P2
SYS_P3
SYS_P4
SYS_P5
Interval Partitioning
- Interval partitioning is an extension of range partitioning which instructs the database to automatically create partitions.
- You must specify at least one range partition.
- Interval partitioning is an enhancement to range partitioning in Oracle 11g and
- interval partitioning automatically creates time-based partitions as new data is added.
When using interval partition Note the below:-
You can only specify one partitioning key column, and it must be of NUMBER or DATE type.Interval partitioning is not supported for index-organized tables.
You cannot create a domain index on an interval-partitioned table.
Example:-
create table p_data (start_date DATE,store_id NUMBER)
PARTITION BY RANGE (start_date)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),
PARTITION pos_data_p3 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY'))
);
- 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.
CREATE TABLE sales
(invoice_no NUMBER NOT NULL,
invoice_date DATE NOT NULL,
comments VARCHAR2(500))
PARTITION BY RANGE (date)
SUBPARTITION BY HASH (emp_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'));
System Partitioning
System partitioning enables application-controlled partitioning without having the database controlling the data placement.
Example:-
create table test5 (a number,b varchar2(10),c date)
partition by system
(partition p1 tablespace RR12 ,
partition p2 tablespace TBS_1);
insert into test5 partition(p1) values(10,'aaa',sysdate);
Partitioning Indexes:-
Local - All index entries in a single partition will correspond to a single table partition (equipartitioned).with local key.
eg:- create index emp_p1 on emp(id) local;
Global - Index in a single partition may correspond to multiple table partitions.
Eg:-
CREATE INDEX emp_global ON emp (date)
GLOBAL PARTITION BY RANGE (date)
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE tbs1,
PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')) TABLESPACE tbs1,
PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')) TABLESPACE tbs1,
PARTITION invoices_q4 VALUES LESS THAN (MAXVALUE) TABLESPACE tbs1);
Partitioning Existing Tables:-
This table same structure as t1...
CREATE TABLE t2 (id NUMBER,description VARCHAR2(50))
PARTITION BY RANGE (id)
(PARTITION t2_p1 VALUES LESS THAN (MAXVALUE));
ALTER TABLE t2 EXCHANGE PARTITION t2_p1 WITH TABLE t1 WITHOUT VALIDATION;
To view the information about Partition:-
COLUMN high_value FORMAT A20
SELECT table_name,partition_name,high_value,num_rows FROM user_tab_partitions ORDER BY table_name, partition_name;
desc DBA_PART_TABLES
desc DBA_TAB_PARTITIONS
DESC DBA_TAB_SUBPARTITIONS
DESC DBA_PART_COL_STATISTICS
DESC DBA_PART_INDEXES
DESC DBA_IND_PARTITIONS
DESC DBA_IND_SUBPARTITIONS
Comments
Post a Comment