In this post I will be discussing about partitioning, the
advantages of partitioning and the different types of portioning that are
available in Oracle and when to use which type of partitioning.
Now a days many of the tables in organization are very large
in size and thus it becomes difficult to manage them. In order to make them
more manageable we decompose the tables in different partitions.
The next question that comes to mind is if my current
application is using a non-partitioned table and I convert it to partitioned do
I need to make changes to the application as well. The answer to it NO there is
no requirement to make any changes to the application. The DML statements and
other applications will use the data stored in the partitions in a similar manner
as it does for a non-partitioned table. However partitioning provides you the
option to use DDL statement to manage individual partitions instead of the
entire table.
So what are the advantages of partitioning?
1. As we just discussed you can use DDL to manage individual
partitions instead of the entire table which makes the administrative
activities less time consuming.
2. The query execution might speed up as the result can now
be obtained from a subset of a particular partition. This feature is referred
to as PARTITION PRUNING.
3. Join operation can also speed up if it involves fetching
data from partitions instead of the entire table.
4. Recovery can now be performed in partition level instead
of table level thereby increasing the availability of the system. This means
that even when one of the partitions of a table is unavailable we can still
obtain the data from other partitions. Same holds true for Backup operations.
5. With partitioning we can take advantage of parallel
query, parallel DDL, parallel DML operations.
6. Although the different partitions of a table must have
similar logical characteristics they can have different physical
characteristics such as COMPRESSION, LOGGING etc.
7. If your table has LOB type columns then you can create a
separate partition to store the LOB data only separate from the other
partitions that stores your normal data types.
When should we go for partitioning?
1. Any table of size 2GB or more is considered to be a good
candidate for partitioning.
2. If your database strategy is such that data for a
specific table is to be sent to different storage then partitioning is needed.
3. If you have a historical table in which old data is of
read only type but the new data is to be stored in a separate partition then
partitioning is needed.
Restrictions of partitioning are as follows:
1. All the partitions of a table must have same logical
characteristics such as column name, character type, length etc.
2. You cannot have partitions on table having LONG or LONG
RAW data type.
How does Oracle determine in which partition to put a
particular row?
This is determined by using a column of the table which is
referenced as the Partitioning Key. For example in a SALES table we can specify
that the REGIONS column will used to determine that the record will be sent to
TS1 if its values is NORTH, to TS2 if its values is EAST, to TS3 if its value
is WEST and to TS4 if its value is SOUTH. This means that REGIONS column here
is behaving as the Partitioning Key.
Next we will discuss the different types of partitioning:
2. Hash partitioning
3. List partitioning
4. Interval Partitioning
5. Composite Range-Range Partitioning
6. Composite Range-Hash Partitioning
7. Composite Range-List Partitioning
8. Composite List-Range Partitioning
8. Composite List-Hash Partitioning
8. Composite List-List Partitioning
