PARTITIONS

What is partitions?
PARTITION in Oracle database can be defined as a concept in which data objects like tables, indexes, and index-organized tables can be reduced or subdivided into smaller areas or pieces and these portioned data objects can be accessed at the much finer level which helps in increasing the performance as it works on only on the relevant data, it is also easy to maintain and decreases the cost by appropriately storing the data and also increases the availability.

Partition independance means backup and recovery operations can be performed on individual partitions, whilst leaving the other partitons available.
  • Query performance can be improved as access can be limited to relevant partitons only.
  • There is a greater ability for parallelism with more partitions.
Types of Partitions:
  • Range Partition
  • List Partition
  • Hash Partition
  • Composite Partition
  • Virtual Column Based Partition
  • Interval Partition
  • Reference Partition
  • System Partition
For demo purpose we can create one user and the username is TESBD.
	SQL> create user tesdb identified by tesdb;
	User created.
	SQL> grant connect,resource,unlimited tablespace to tesdb;
	Grant succeeded.
	SQL> conn tesdb/tesdb
	Connected.

Range Partition : (its based on number and date)

A table that is partitioned by range is partitioned in such a way that each partition contains rows for which the partitioning expression value lies within a given range. Ranges should be contiguous but not overlapping, and are defined using the VALUES LESS THAN operator.

	SQL> create table tesdb_range (id int, name varchar2(20))
		partition by range (id)
		(partition p1 values less than (10),
		partition p2 values less than (20),
		partition p3 values less than (30));

	Table created.

	SQL> begin
		for i in 0..10 loop
		insert into tesdb_range values (i,'aa');
		end loop;
		end;
		/
	  PL/SQL procedure successfully completed.

To View partitions : 
	SQL> desc user_tab_partitions;
	SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from user_tab_partitions;
	TABLE_NAME		       PARTITION_NAME	    HIGH_VALUE
	------------------------------ -------------------- ----------------------
	TESDB_RANGE		       P1		    10
	TESDB_RANGE		       P2		    20
	TESDB_RANGE		       P3		    30

	To Check values in each  partitions:
	SQL> select * from tesdb_range partition(p1);

		ID NAME
	---------- -----------
		 0 aa
		 1 aa
		 2 aa
		 3 aa
		 4 aa
		 5 aa
		 6 aa
		 7 aa
		 8 aa
		 9 aa

	10 rows selected.
	SQL> select * from tesdb_range partition(p2);

		ID NAME
	---------- --------------------
		10 aa

	SQL> select * from tesdb_range partition(p3);

	no rows selected
	Now we add some records out of the range partition . it can be error of (ORA-14400: inserted partition key does 
	not map to any partition)
	SQL> insert into tesdb_range values (30,'aa');
	insert into tesdb_range values (30,'aa')
				*
	ERROR at line 1:
	ORA-14400: inserted partition key does not map to any partition

	We can?t add value of above 29, if we add the value above error will be occurred, on that case we set the  
	(max value) conditions.

	In order to add values above mentioned range we can add a new partition with max value
	SQL> alter table tesdb_range add partition p4 values less than(maxvalue);
	Table altered.

Drop Partition :
	SQL> alter table tesdb_range drop partition(p2);
	Table altered.

	SQL> select * from tesdb_range partition(p2);
	select * from tesdb_range partition(p2)
								*
	ERROR at line 1:
	ORA-02149: Specified partition does not exist

	Truncate the partiton:
	alter table tesdb_range truncate partition(p1);
	Table truncated.
	SQL> select * from tesdb_range partition(p1);
	no rows selected
Move Partition across tablespace : 
	SQL> alter table tesdb_range move partition p1 tablespace users;
	Table altered.
Rename Partition : 
	SQL> select * from tesdb_range partition(p4);
		ID NAME
	---------- --------------------
		40 ccc
	Now, rename the partition p4 to p6,
	SQL>         alter table tesdb_range rename partition p4 to p6;
	SQL>
	Table altered.
Now, check the p4 partition, 
	SQL> select * from tesdb_range partition(p4);
	select * from tesdb_range partition(p4)
										*
	ERROR at line 1:
	ORA-02149: Specified partition does not exist

	SQL> select * from tesdb_range partition(p6);
		ID NAME
	---------- --------------------
		40 ccc

Split Partition :
In p6 partition we have 6 values, 
	SQL> select * from tesdb_range partition(p6);

		ID NAME
	---------- --------------------
		40 ccc
		41 ddd
		45 rrr
		50 yyy
		   100 hhh
		80 uuu

	SQL> alter table tesdb_range split partition p6 at(45) into (partition p6a,partition p6b);
	Table altered.

	SQL> select * from tesdb_range partition(p6a);

		ID NAME
	---------- --------------------
		40 ccc
		41 ddd

	SQL> select * from tesdb_range partition(p6b);

		ID NAME
	---------- --------------------
		45 rrr
		50 yyy
		   100 hhh
		80 uuu

Views for Partition : 
		SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from user_tab_partitions;

		TABLE_NAME	 PARTITION_NAME	    	HIGH_VALUE
		------------- 	---------------     ------------------
		TESDB_RANGE		P1 		 10
		TESDB_RANGE		P3 		 30
		TESDB_RANGE		P6A		 45
		TESDB_RANGE		P6B		 MAXVALUE

Merge Partition : 
		SQL> alter table tesdb_range merge partitions p6a,p6b into partition p6;
		Table altered.

		SQL> select * from tesdb_range partition (p6a);
		select * from tesdb_range partition (p6a)
											 *
		ERROR at line 1:
		ORA-02149: Specified partition does not exist
		SQL> select * from tesdb_range partition (p6);

			ID NAME
		---------- --------------------
			40 ccc
			41 ddd
			45 rrr
			50 yyy
			   100 hhh
			80 uuu

		6 rows selected.

Interval Partition :
It is a advanced concept of range partition(11g).
Interval partitioning is an extension to range partitioning in which, beyond a point in time, partitions are defined by an interval. Interval partitions are automatically created by the database when data is inserted into the partition. Range or interval partitioning is often used to organize data by time intervals on a column of type DATE.

Interval Partition Oracle create any partition whenever needed for data at the time Insesrted.

It Improves the manageability.
Interval
Interval-list
Interval-hast
Interval-range
interval-reference
Key column must be number/date type.

		SQL> create table tesdb_interval (id int, name varchar2(20))
		partition by range (id) interval (10)
		(partition i1 values less than (10),
		partition i2 values less than (20));

		Table created.

		SQL> begin
			for i in 0..50 loop
			insert into tesdb_interval values (i,'aa');
			end loop;
			end;
			/

		PL/SQL procedure successfully completed.

		select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from user_tab_partitions;
		TABLE_NAME	     PARTITION_NAME		    HIGH_VALUE
		-------------------- ------------------------------ ------------------------------
		TESDB_INTERVAL	     I1 			    10
		TESDB_INTERVAL	     I2 			    20
		TESDB_INTERVAL	     SYS_P261			    30
		TESDB_INTERVAL	     SYS_P262			    40
		TESDB_INTERVAL	     SYS_P263			    50
		TESDB_INTERVAL	     SYS_P264			    60
		TESDB_RANGE	     P3 			    30
		TESDB_RANGE	     P5 			    10
		TESDB_RANGE	     P6 			    MAXVALUE

		9 rows selected.

List Partition :
List partitioning was added as a partitioning method in Oracle 9i, Release 1. List partitioning allows for partitions to reflect real-world groupings (e.g.. business units and territory regions).
List partitioning differs from range partition in that the groupings in list partitioning are not side-by-side or in a logical range. 
		SQL> create table tesdb_list (id int,city varchar2(20))
		partition by list (city)
		(partition p_1 values ('chennai'),
		partition p_2 values ('madurai'),
		partition p_3 values (default));

		Table created.
		select * from tesdb_list;
		no rows selected

		insert into tesdb_list values(1,'chennai');
		insert into tesdb_list values(2,'madurai');
		insert into tesdb_list values(1,'');
		insert into tesdb_list values(4,'chennai');
		insert into tesdb_list values(5,'madurai');
		insert into tesdb_list values(6,'');
		SQL>
		1 row created.
		SQL>
		1 row created.
		SQL>
		1 row created.
		SQL>
		1 row created.

		SQL> select * from tesdb_list partition (p_1);
			ID CITY
		---------- --------------------
			 4 chennai
		SQL> select * from tesdb_list partition (p_2);
			ID CITY
		---------- --------------------
			 2 madurai
			 5 madurai

		SQL> select * from tesdb_list partition (p_3);
			ID CITY
		---------- --------------------
			 1
		Now insert the city coloumn, any other city(salem), it will be consider default partitions.

		SQL> insert into tesdb_list values(10,'salem');
		1 row created.
		SQL> select * from tesdb_list Partition (p_3);
			ID 		CITY
		---------- 	--------------------
			 1
			10 		salem

HASH PARTITION : (it will taken random partitions)
hash partitioning, a row is placed into a partition based on the result of passing the partitioning key into a hashing algorithm. Using this approach, data is randomly distributed across the partitions rather than grouped. This is a good approach for some data, but may not be an effective way to manage historical data. 
		SQL> create table tesdb_hash (id int,name varchar2(20))
			partition by hash(id)
			(partition h1, partition h2, partition h3);

			Table created.

		SQL>
		 begin
			for i in 1..15 loop
			insert into tesdb_hash values (1,'a');
			end loop;
			end;
			/
		PL/SQL procedure successfully completed.
		SQL> select * from tesdb_hash partition (h1);
		no rows selected
		SQL> select * from tesdb_hash partition (h2);

			ID NAME
		---------- --------------------
			 1 a
			 1 a
			 1 a
			 1 a
			 1 a
			 1 a
			 1 a
			 1 a
			 1 a
			 1 a
			 1 a

			ID NAME
		---------- --------------------
			 1 a
			 1 a
			 1 a
			 1 a

		15 rows selected.

		SQL> select * from tesdb_hash partition (h3);
		no rows selected

System Partition :
System partitioning is a partitioning method introduced in Oracle 11g that allows an application to control partition selection. Unlike other partitioning schemes, system partitions do not have partition keys. As such, partition extended syntax must be used when inserting rows into system partitioned tables 
	SQL> create table tesdb_system (id int, name varchar2(20))
		partition by system
		(partition p1, partition  p2);

		Table created.
		SQL> insert into tesdb_system partition (p1) values (1,'a');
		1 row created.
		SQL> insert into tesdb_system partition (p2) values (2,'b');
		1 row created.
		SQL> declare
		i number(5);
		begin
			for i in 1..20 loop
			insert into tesdb_system partition (p1) values (i,'a');
			end loop;
			end;
			/

		PL/SQL procedure successfully completed.

		SQL> select * from tesdb_system partition (p1);

			ID NAME
		---------- --------------------
			 1 a
			 1 a
			 2 a
			 3 a
			 4 a
			 5 a
			 6 a
			 7 a
			 8 a
			 9 a
			10 a

			ID NAME
		---------- --------------------
			11 a
			12 a
			13 a
			14 a
			15 a
			16 a
			17 a
			18 a
			19 a
			20 a

		21 rows selected.

		SQL> select * from tesdb_system partition (p2);

			ID NAME
		---------- --------------------
			 2 b

Composite Partition :
(SUB partition inside another partitions) Composite partitioning partitions the data rows
using the range method, and within each partition, Oracle subpartitions the data using many methods.
Oracle experts note that composite partitioning supports historical operations, such as adding new
range partitions, but also provides higher degrees of parallelism for DML operations and finer granularity..
of data placement through sub-partitioning.

	SQL> create table tesdb_composite (id int,name varchar2(20),deptno int)
	partition by range(id)
	subpartition by list (deptno)
		subpartition template(
		subpartition sub1 values (10,20,30) ,
		subpartition sub2 values (40,50,60) ,
		subpartition sub3 values (70,80,90))
	(partition p1 values less than(10),
	partition p2 values less than(20),
	partition p3 values less than(maxvalue));

	Table created.

	SQL> begin
	insert into tesdb_composite values (1,'aa',10);
	insert into tesdb_composite values (2,'ba',20);
	insert into tesdb_composite values (3,'ca',30);
	insert into tesdb_composite values (4,'da',40);
	insert into tesdb_composite values (5,'ea',50);
	insert into tesdb_composite values (6,'fa',60);
	insert into tesdb_composite values (7,'ga',70);
	insert into tesdb_composite values (8,'ha',80);
	insert into tesdb_composite values (9,'ia',90);
	end;
	/
	PL/SQL procedure successfully completed.
	SQL> insert into tesdb_composite values (19,'aa',10);
	1 row created.
	SQL> insert into tesdb_composite values (18,'aa',10);
	1 row created.
	SQL> insert into tesdb_composite values (5,'aa',10);
	1 row created.


	SQL> SELECT partition_name, subpartition_name, tablespace_name, high_value  FROM
	user_tab_subpartitions  WHERE table_name = 'TESDB_COMPOSITE';
	

	
PARTITION_NAMESUBPARTITION_NAMETABLESPACE_NAMEHIGH_VALUE

	
----------------------------------------------------------------

	
P1P1_SUB1USERS10, 20, 30

	
P1P1_SUB2USERS40, 50, 60

	
P1P1_SUB3USERS70, 80, 90

	
P2P2_SUB1USERS10, 20, 30

	
P2P2_SUB2USERS40, 50, 60

	
P2P2_SUB3USERS70, 80, 90

	
P3P3_SUB1USERS10, 20, 30

	
P3P3_SUB2USERS40, 50, 60

	
P3P3_SUB3USERS70, 80, 90

	


	9 rows selected.

Reference Partition :
Reference partitioning provides the ability to partition a table based on the partitioning scheme of the table referenced
in its referential constraint. A table can now be partitioned based on the partitioning method of a table referenced in
its referential constraint.

Tables with a parent/child relationship can be equipartitioned by inheriting the partitioning key from the parent table
without duplication of the key columns. If the parent table is a composite-partitioned table, then the table will have one
partition for each subpartition of its parent. Reference Partitioning allows you to use a Referential Integrity Constraint
to equi-partition a "Child" Table with a "Parent" Table.
	SQL> create table ref_tesdb(id int constraint pk2 primary key,place varchar(30))
	partition by list(place)
	(partition p1 values('chennai'),
	partition p2 values('mumbai'));
	Table created.

	SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from user_tab_partitions where TABLE_NAME='REF_TESDB';

	TABLE_NAME		       PARTITION_NAME	    HIGH_VALUE
	------------------------------ -------------------- ----------------------------------------
	REF_TESDB		       P1		    'chennai'
	REF_TESDB		       P2		    'mumbai'

	SQL > create table fore_tesdb(name varchar2(20) not null,sid int not null,constraint fk2 foreign key(sid) 

	references ref_tesdb(id)) partition by reference(fk2);
	Table created.

	SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from user_tab_partitions where 

	TABLE_NAME='FORE_TESDB';

	TABLE_NAME		       PARTITION_NAME	    HIGH_VALUE
	------------------------------ -------------------- ----------------------------------------
	FORE_TESDB		       P1
	FORE_TESDB		       P2


(Oracle Database - Flash back)