Oracle Partitions

Need for partition:

Oracle tables which are very huge say(> 2GB) , which inserts data everyday and that have historical and important details for reference, requires Partitions.

Partitions are nothing but smaller segments within the tables for easier maintenance of the tables.

When the content of a table need to be distributed across different types of storage devices, then we need to create partitions.

And finally for performance issue, we need partition. We prefer losing a weekly data instead of the whole table contents :)

Partitioned Index:

In addition to partitioning not only the tables, the Primary keys of the big tables are partitioned as well:

The reasons for partitioning the index are:

• Avoid rebuilding the entire index when data is removed.
• Perform maintenance on parts of the data without invalidating the entire index.

• Reduce the impact of index skew caused by an index on a column with a monotonically increasing value.

Partitions can be created weekly or monthly basis depending of the data inflow:

We can create a dbms_job for creating the weekly(or monthly) partitions. The sample statement for creating partition name is :

We create the below table to show the demonstration about partition:

This sql will create the name of the partition in the format "201545_20151107". This means, that the partition has been created for the week no 45 of the year 2015 and the week ending 07.11.2015. This way you can create a job to create 3 partition names every week. This job can be scheduled to run every Sunday.

Once the names of the partition are created,then we need to create partition in the big tables with the created names.

For example if the table name is TBL_PERSONINFORMATION then partition names can be created as "TBL_PI_201545_20151107". 

Table creation script: 

CREATE TABLE TBL_PERSONALINFORMATION

(

  PERSONID    VARCHAR2(20 CHAR)                 NOT NULL,

  FORENAME    VARCHAR2(1000 CHAR),

  LASTNAME    VARCHAR2(1000 CHAR)               NOT NULL,

  AGE         NUMBER,

  SEX         VARCHAR2(1 BYTE),

  ADDRESS     VARCHAR2(2000 CHAR),

  DATE_ADDED  DATE

)

PARTITION BY RANGE (DATE_ADDED)

( 

  PARTITION TBL_PI_201543_20151024 VALUES LESS THAN (TO_DATE(' 2015-10-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) , 

  PARTITION TBL_PI_201544_20151024 VALUES LESS THAN (TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) , 

  PARTITION TBL_PI_201545_20151024 VALUES LESS THAN (TO_DATE(' 2015-11-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

 );

Inserting data into the table:

insert into TBL_PERSONALINFORMATION partition (TBL_PI_201543_20151024) values ('10','AA','BB',19,'F','AAA BBB St.',sysdate);

After inserting into the table at the appropriate partition, the table looks like below:

Each partition has sufficient number of rows for our demo.

Before deleting Partition:

Few important points to consider before deleting the partitions:

• All the FKs must be disabled before deleting

• Delete the partitions using 'ALTER TABLE.. DROP PARTITION' syntax

• Enable the FKs back

• Rebuild the indexes of the partitioned table
• Delete the data from the referential tables to maintain consistency.

Deleting the Partition:

This way, I have deleted the partition "TBL_PI_201543_20151024".

Quiz Time

Do you know why we disable Foreign Keys before deleting Partitions?

To maintain referential integrity with other tables

As per the syntax

To avoid deleting referential data

online polls