Basics of Partition Tables in Oracle

Basics of Partition Tables

Partitioning is a method of splitting an object (a table or index) into separate parts (called partitions) based on some criterion that is assigned to the partition. The criterion might be a date range, a number range, or any other possible value. Imagine, for example, that you have a huge table that is driving you crazy because queries on it are really slow. You get so mad at this table that you take a logical hatchet and begin to slice the table up into many smaller tables is a partition. Each table has the some columns, and each stores a certain amount of data. Oracle collectively deals with these physical partitions as a single logical object – called the partitioned table of index that you can access.

In Oracle, you can partition the following items; tables, indexes, index organized tables, materialized views, and indexes on materialized views. Oracle doesn’t support partitioning of clusters or indexes on clusters.

Why partition tables and indexes? As time goes on, certain objects tend to grow quickly, some becoming very large indeed. This can lead to several problems.

Queries take longer and longer to run on non-partitioned large tables.
More time is needed to backup the tablespace in which the table resides.
More time is needed to recover the tablespace, because the tablespace grows larger.
The table becomes harder to manage. Object management issues, such as storage parameters and de-fragmentation, become more complicated as an object gets bigger.
You cannot assign different parts of the non-partitioned table to other tablespaces. This ability might for better performance and faster backup and recovery.

Partitioning is one method of dealing effectively with these problems. It is a particularly effective strategy with certain types of very large databases. Partitioning is very good feature for maintaining smart security, an example, you can take one portion of table read only or disable.

You can get more Oracle DBA articles on Remote Oracle Support