OBJECT VIEWS WITH REFERENCES
To implement the objects
and the ref constraints to the existing tables, what we can do? Simply drop the
both tables and recreate with objects and ref constrains.
But you can achieve this
with out dropping the tables and without losing the data by creating object
views with references.
Ex:
a) Create the following tables
SQL> Create table student1(no number(2) primary key,name
varchar(2),marks
number(3));
SQL> Create table student2(no number(2) primary key,hno number(3),city
varchar(10),id number(2),foreign
Key(id) references student1(no));
b) Insert the records into both tables
SQL> insert into student1(1,’a’,100);
SQL> insert into student1(2,’b’,200);
SQL> insert into student2(11,111,’hyd’,1);
SQL> insert into student2(12,222,’bang’,2);
SQL> insert into student2(13,333,’bombay’,1);
c) Create the type
SQL> create or replace type stud as object(no number(2),name
varchar(2),marks
number(3));/
d) Generating OIDs
SQL> Create or replace view student1_ov of stud with object
identifier(or id) (no) as
Select * from Student1;
e) Generating references
SQL> Create or replace view student2_ov as select no,hno,city,
make_ref(student1_ov,id) id from Student2;
d) Query the following
SQL> select *from student1_ov;
SQL> select ref(s) from student1_ov s;
SQL> select values(s) from student1_ov;
SQ> select *from student2_ov;
SQL> select deref(s.id) from student2_ov s;
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
No comments:
Post a Comment