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