Let me analyze something about partition:

Let me analyze something about partition:

Partitioning: Very large tables & indexes by letting you decompose them into smaller more manageable pieces called Partition.

Partition very useful for many different type of application particularly to handle very large volume data & OLTP environment.

Advantages:

1. Partition enables data management operation such as data loads, index creation, and rebuilding & Backup/recovery at the partition level rather then entire table.
2. Partition improves query performance.
3. Partition increases the availability of mission critical database.

Method of Partition:

1. Range Partition.
2. List Partition.
3. Hash Partition.
4. Composite Partition.

What is Rage Partition?

Range partition maps data to the partition based on ranges of partition key values that you establish based on partition. It’s most common type of we are using DATE column.

Ex:

….

…

..

PARTITION by RANGE (hiredate)

(

Partition jan2000 values less then (TO_DATE('02/01/2000','MM/DD/YYYY')),

Partition feb2000 values less then (TO_DATE('03/01/2000','MM/DD/YYYY')),

)

What is List Partition?

List partition enable explicitly control how rows map to the partition.

Example:

…

…

..

Partition by LIST (sales)

(

Partition sales_in values (‘INDIA’),

Partition sales_us values (‘USA’)

)

What is Hash Partition?

Hash partition enable easy partition of data that does not lend itself LIST & RANGE partition.

Composite Partition:

Composite partitioning partitions data using the range method, and within each partition, sub partitions it using the hash or list method

When To Partition Table:

Here are some pre-request to implement partition table.

1. Table size should be more than 2 Gb.
2. Tables contain Historical data’s, in which new data’s added in new partition.