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?
