The Advantages & Disadvantages of Oracle Partition

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.

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.

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.

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.