Table Partitions

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 

Flashback Query

FLASHBACK QUERY

Used to retrieve the data which has been already committed with out going for recovery.

Flashbacks are of two types

Ø  Time base flashback

Ø  SCN based flashback (SCN stands for System Change Number)

Ex:

1) Using time based flashback

     a) SQL> Select *from student;

          -- This will display all the rows

     b) SQL> Delete student;

     c) SQL> Commit;              -- this will commit the work.

     d) SQL> Select *from student;

         -- Here it will display nothing

     e) Then execute the following procedures

         SQL> Exec dbms_flashback.enable_at_time(sysdate-2/1440)

     f) SQL> Select *from student;

         -- Here it will display the lost data

         -- The lost data will come but the current system time was used

     g) SQL> Exec dbms_flashback.disable

          -- Here we have to disable the flashback to enable it again

2) Using SCN based flashback

     a) Declare a variable to store SCN

          SQL> Variable s number

     b) Get the SCN

          SQL> Exec :s := exec dbms_flashback.get_system_change_number

     c) To see the SCN

         SQL> Print s

     d) Then execute the following procedures

          SQL> Exec dbms_flashback.enable_at_system_change_number(:s)

          SQL> Exec dbms_flashback.disable

Case and Default

                                    CASE AND DEFAULT

CASE

Case is similar to decode but easier to understand while going through coding

Ex:

SQL> Select sal,

          Case sal

                    When 500 then ‘low’

                    When 5000 then ‘high’

                    Else ‘medium’

          End case

          From emp;

       SAL          CASE

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

       500          low

      2500         medium

      2000         medium

      3500         medium

      3000         medium

      5000         high

      4000         medium

      5000         high

      1800         medium

      1200         medium

      2000         medium

      2700         medium

      2200         medium

      3200         medium

DEFAULT

Default can be considered as a substitute behavior of not null constraint when applied to new rows being entered into the table.

When you define a column with the default keyword followed by a value, you are actually telling the database that, on insert if a row was not assigned a value for this column, use the default value that you have specified.

Default is applied only during insertion of new rows.

Ex:

     SQL> create table student(no number(2) default 11,name varchar(2));

     SQL> insert into student values(1,'a');

     SQL> insert into student(name) values('b');

    

     SQL> select * from student;

        NO   NAME

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

         1             a

        11            b

       SQL> insert into student values(null, ‘c’);

      SQL> select * from student;

        NO   NAME

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

         1             a

        11            b

                     C

-- Default can not override nulls.