Friday, October 28, 2011
ORACLE SQL TUTORIAL: PARTITIONS
A
single logical table can be split into a number of physically separate pieces
based on ranges of key values. Each of the parts of the table is called a
partition.
A
non-partitioned table can not be partitioned later.
TYPES
Ø Range
partitions
Ø List
partitions
Ø Hash
partitions
Ø Sub
partitions
ADVANTAGES
Ø Reducing
downtime for scheduled maintenance, which allows maintenance operations to be
carried out on selected partitions while other partitions are available to
users.
Ø Reducing
downtime due to data failure, failure of a particular partition will no way
affect other partitions.
Ø Partition
independence allows for concurrent use of the various partitions for various
purposes.
ADVANTAGES
OF PARTITIONS BY STORING THEM IN DIFFERENT TABLESPACES
Ø Reduces
the possibility of data corruption in multiple partitions.
Ø Back
up and recovery of each partition can be done independently.
DISADVANTAGES
Ø Partitioned
tables cannot contain any columns with long or long raw datatypes, LOB types or
object types.
RANGE
PARTITIONS
a)
Creating range partitioned table
SQL>
Create table student(no number(2),name varchar(2)) partition by range(no)
(partition
p1 values less than(10), partition p2
values less than(20), partition p3 values less
than(30),partition p4 values less
than(maxvalue));
** if you are using maxvalue for the last
partition, you can not add a partition.
b)
Inserting records into range partitioned table
SQL> Insert into student values(1,’a’); -- this will go to p1
SQL> Insert into student
values(11,’b’); -- this will go
to p2
SQL> Insert into student
values(21,’c’); -- this will go
to p3
SQL> Insert into student
values(31,’d’); -- this will go
to p4
c)
Retrieving records from range partitioned table
SQL>
Select *from student;
SQL>
Select *from student partition(p1);
d)
Possible operations with range partitions
v Add
v Drop
v Truncate
v Rename
v Split
v Move
v Exchange
e)
Adding a partition
SQL>
Alter table student add partition p5 values less than(40);
f)
Dropping a partition
SQL> Alter table student drop partition
p4;
g)
Renaming a partition
SQL> Alter table student rename
partition p3 to p6;
h)
Truncate a partition
SQL>
Alter table student truncate partition p6;
i)
Splitting a partition
SQL>
Alter table student split partition p2 at(15) into (partition p21,partition
p22);
j) Exchanging
a partition
SQL>
Alter table student exchange partition p1 with table student2;
k) Moving
a partition
SQL>
Alter table student move partition p21 tablespace saketh_ts;
LIST
PARTITIONS
a)
Creating list partitioned table
SQL>
Create table student(no number(2),name varchar(2)) partition by list(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));
b) Inserting records into list partitioned table
SQL>
Insert into student values(1,’a’); --
this will go to p1
SQL>
Insert into student values(6,’b’); --
this will go to p2
SQL>
Insert into student values(11,’c’); --
this will go to p3
SQL>
Insert into student values(16,’d’); --
this will go to p4
c) Retrieving
records from list partitioned table
SQL>
Select *from student;
SQL>
Select *from student partition(p1);
d) Possible
operations with list partitions
v Add
v Drop
v Truncate
v Rename
v Move
v Exchange
e)
Adding a partition
SQL>
Alter table student add partition p5 values(21,22,23,24,25);
f)
Dropping a partition
SQL>
Alter table student drop partition p4;
g)
Renaming a partition
SQL> Alter table student rename partition p3 to p6;
h)
Truncate a partition
SQL>
Alter table student truncate partition p6;
i)
Exchanging a partition
SQL>
Alter table student exchange partition p1 with table student2;
j)
Moving a partition
SQL> Alter table student move partition p2
tablespace saketh_ts;
HASH
PARTITIONS
a) Creating
hash partitioned table
SQL>
Create table student(no number(2),name varchar(2)) partition by hash(no)
partitions
5;
Here
oracle automatically gives partition names like
SYS_P1
SYS_P2
SYS_P3
SYS_P4
SYS_P5
b) Inserting
records into hash partitioned table
it will insert the records based on hash
function calculated by taking the partition key
SQL> Insert into student values(1,’a’);
SQL> Insert into student values(6,’b’);
SQL> Insert into student
values(11,’c’);
SQL> Insert into student
values(16,’d’);
c) Retrieving
records from hash partitioned table
SQL>
Select *from student;
SQL>
Select *from student partition(sys_p1);
d) Possible
operations with hash partitions
v Add
v Truncate
v Rename
v Move
v Exchange
e)
Adding a partition
SQL>
Alter table student add partition p6 ;
f)
Renaming a partition
SQL> Alter table student rename
partition p6 to p7;
g)
Truncate a partition
SQL>
Alter table student truncate partition p7;
h)
Exchanging a partition
SQL>
Alter table student exchange partition sys_p1 with table student2;
i)
Moving a partition
SQL> Alter table student move partition sys_p2
tablespace saketh_ts;
SUB-PARTITIONS WITH RANGE AND HASH
Subpartitions
clause is used by hash only. We can not create subpartitions with list and hash
partitions.
a)
Creating subpartitioned table
SQL> Create table student(no number(2),name
varchar(2),marks number(3))
Partition by range(no) subpartition by
hash(name) subpartitions 3
(Partition p1 values less
than(10),partition p2 values less than(20));
This
will create two partitions p1 and p2 with three subpartitions for each
partition
P1 – SYS_SUBP1
SYS_SUBP2
SYS_SUBP3
P2 – SYS_SUBP4
SYS_SUBP5
SYS_SUBP6
** if you are using maxvalue for the last
partition, you can not add a partition.
b) Inserting
records into subpartitioned table
SQL> Insert into student values(1,’a’); -- this will go to p1
SQL> Insert into student
values(11,’b’); -- this will go
to p2
c) Retrieving
records from subpartitioned table
SQL>
Select *from student;
SQL>
Select *from student partition(p1);
SQL>
Select *from student subpartition(sys_subp1);
d) Possible
operations with subpartitions
v Add
v Drop
v Truncate
v Rename
v Split
e)
Adding a partition
SQL>
Alter table student add partition p3 values less than(30);
f)
Dropping a partition
SQL>
Alter table student drop partition p3;
g)
Renaming a partition
SQL> Alter table student rename partition p2 to p3;
h)
Truncate a partition
SQL>
Alter table student truncate partition p1;
i)
Splitting a partition
SQL>
Alter table student split partition p3 at(15) into (partition p31,partition
p32);
DATA MODEL
Ø ALL_IND_PARTITIONS
Ø ALL_IND_SUBPARTITIONS
Ø ALL_TAB_PARTITIONS
Ø ALL_TAB_SUBPARTITIONS
Ø DBA_IND_PARTITIONS
Ø DBA_IND_SUBPARTITIONS
Ø DBA_TAB_PARTITIONS
Ø DBA_TAB_SUBPARTITIONS
Ø USER_IND_PARTITIONS
Ø USER_IND_SUBPARTITIONS
Ø USER_TAB_PARTITIONS
Ø USER_TAB_SUBPARTITIONS
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
Disclaimer
The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.
0 Responses to “ORACLE SQL TUTORIAL: PARTITIONS”
Post a Comment