The Advantages & Disadvantages of Oracle Partition

By Andy Carr

Partitioning data makes the information more manageable in databases.
i Jupiterimages/ Images

Most articles and papers on Oracle say that Oracle partitioning has many advantages. However, there are some drawbacks that don't seem to get much press. Experience has indicated that Oracle partitioning generally works well, but certain conditions highlight specific circumstances in which it's not always the best option -- especially on small databases. Partitioning is mainly targeted toward large database environments.

Performance Advantages

The main advantage -- and the purpose -- of partitioning is said to be to provide performance advantages. It also enables better manageability for various applications. The objective of partitioning is to divide database objects, such as tables, indexes and other objects into smaller, manageable pieces.

Manageability Advantages

The manageable aspects of partitioning in Oracle imply that objects can be accessed and controlled at a lower level than would otherwise be the case. Each business case is different, but different partitioning strategies cope with different business needs. The transparent operations of partitioning means that application development time is reduced, saving costs on the bottom line. You get further savings on storage space, as partitioned table spaces take up less room than traditional table space techniques.

Performance Disadvantages

Indexes can pose a serious problem if they fail. If an index goes down or is damaged, the underlying tables can be damaged beyond repair, making them unusable and unrecoverable. The only solution is to revert to backup -- if you have one -- or to rebuild the index. In a partition context, this can take a very long time.

Manageability Disadvantages

Partitions are much harder to manage than standard tables devoid of them. This is because the partition aspects have to be identified and managed as part of an operation, such as the use of "truncate." It's not enough to use the simple truncate command; the correct command would be "alter table truncate partition." Therefore, partitions are harder to manage.