Infolinks

Sunday, 15 July 2012

Partitions

Partitions

Partition is a data base object, it allows VLDB objects (Very Large Databases such as tables, indexes etc). By splitting large database objects into smaller, more manageable units. You can divide the rows of a single table into different parts called as partitioning a table
Advantages of Using Partitions
1.Reducing data base Retrieve time
By splitting the large data base objects into smaller, more manageable partitions, allows you to perform any type of action on individual units of the database
2.Backup and Recovery
Partitioning improves performance of Backup and Recovery operations. Smaller size of partitions offers more options for backing up and recovering from the partitions
3.Query Performance
Partitions can be created on ranges of certain columns. This focus reduce the search time, as a queries require a full table scan to search for the given criteria. This can be resolved by using partitions on a table
4.Partition Transparency
Partition implementation is transparent to the end users and developers. They do not need to be aware of the physical implementation of the database objects
Syntax :
Create table <table name> (columns specifications,-------)
Partition by Range (column name)
(partition <partition name> values less than (some value),
partition <partition name> values less than (some value),
----------------------------
partition <partition name> values less than (max value));
Example :
create table partitiondemo(sno number(3),sname varchar2(10))
partition by range (sno)
(partition p1 values less than(200),
partition p2 values less than(300),
partition p3 values less than(600),
partition p4 values less than(999))
1. select * from partitiondemo;
SNO SNAME
--------- ----------
100 Nithya
105 Saloni
109 Namitha
199 Aruna
204 Priya
250 Mahesh
306 Nandhini
450 Pooja
560 Uma
328 Janshi
900 Rani
904 Kavya
2. select * from partitiondemo partition(p1);
SNO SNAME
--------- ----------
100 Nithya
105 Saloni
109 Namitha
199 Aruna
3. select * from partitiondemo partition(p2);
SNO SNAME
--------- ----------
204 Priya
250 Mahesh
4. select * from partitiondemo partition(p3);
SNO SNAME
--------- ----------
306 Nandhini
450 Pooja
560 Uma
328 Janshi
900 Rani
904 Kavya
5. select * from partitiondemo partition(p3);
SNO SNAME
--------- ----------
900 Rani
904 Kavya
Now, insert some values again
insert into partitiondemo values(125,'Sound');
select * from partitiondemo partition(p1);
SNO SNAME
--------- ----------
100 Nithya
105 Saloni
109 Namitha
199 Aruna
125 Sound

No comments:

Post a Comment