What is a
PARTITION in Oracle?
With the
help of Partitioning, It allows DBAs to split large tables into more manageable
"sub-tables", to improve
database performance, manageability and availability.
What
actually happens in partitioning ?
With table partitioning,
a table can be physically divided into multiple smaller tables, called
partitions, while logically it stays one table. This means your code stays the
same, but full partition scans will be executed instead of a full table scan.
When to
Partition a Table ?
- If Tables
size greater than 2 GB , then it always considered for partitioning.
- Tables
containing historical data, in which new data is added into the newest
partition.
Example :
If you have
a historical table where only the current month's data is updatable and the
other 11 months are read only, then that table will be partitioned.
-When the
contents of a table need to be distributed across different types of storage
devices.
Benefits of
partitioning
Partitioning
allows tables, indexes, and index-organized tables to be subdivided into
smaller pieces, enabling these database objects to be managed and accessed at a
finer level of granularity.
Partitioning
Reduces the downtime in case of data failure. And a failure of a particular
partition will no way affect other partitions.
Maintenance
operations can be carried out on selected partitions, while other partitions
are available to users, So this will also reduce the downtime.
What is the
advantage of partitions, by storing them in different Tablespaces??
1 Reduces the possibility of data corruption
in multiple partitions.
2 Back-up and recovery of each partition can
be done independently.
What are the
Types of partitioning ?
1 Range partitions
2 List partitions
3 Hash partitions
4 Sub partitions
1 Range partitions
The table is
divided in ranges, typically used for ‘date’ ranges
It is
partitioned in such a way that each partition contains rows for
which the partitioning expression value lies within a given range.
2 List partitions
Here, a
limited set of possible values is given ; such that the rows containing the
same value are grouped.
Enables you
to explicitly control how rows map to partitions by specifying a list of
discrete values for the partitioning key in the description for each
partition.
3 Hash partitions
Happens when
a random distribution occurs.
Hash
partitioning maps data to partitions based on a hashing algorithm
that Oracle applies to the partitioning key that you identify.
4 Sub partitions
INDEX
Partitioning
• Non partitioned: No partitioning applied
• Globally partitioned: The index is not partitioned on the same
key as the table, offer higher flexibility.
• Locally partitioned: The index is partitioned on the same key
as the partitioned table,
Very good information thank you for sharing this article. Know more about SQL Online Training
ReplyDelete