Scalability

The ability to split a large database object into smaller pieces transparently simplifies efficient management of very large databases. You can identify and manipulate individual partitions and subpartitions to manage large database objects. Consider the following advantages of partitioned objects:

• Backup and recovery can be performed on a low level of granularity to manage the size of the database.

• Part of a database object can be placed in compressed storage while other parts can remain uncompressed.

• Partitioning can store data transparently on different storage tiers to lower the cost of retaining vast amounts of data. For more information, refer to Managing and Maintaining Time-Based Information.

It takes longer to scan a big table than it takes to scan a small table. Queries against partitioned tables may access one or more partitions that are small in contrast to the total size of the table. Similarly, queries may take advantage of partition elimination on indexes. It takes less time to read a smaller portion of an index from disk than to read the entire index. Index structures that share the partitioning strategy with the table, such as local partitioned indexes, can be accessed and maintained on a partition-by-partition basis.

The database can take advantage of the distinct data sets in separate partitions if you use parallel execution to speed up queries, DML, and DDL statements. Individual parallel execution servers can work on their own data sets, identified by the partition boundaries.

With partitioning, users are more likely to query on isolated and smaller data sets. Consequently, the database can return results faster than if all users queried the same and much larger data sets. Data contention is less likely.

You can perform complex queries faster using smaller data sets. If smaller data sets are being accessed, then complex calculations are more likely to be processed in memory, which is beneficial from a performance perspective and reduces the application's I/O requirements. A larger data set may have to be written to the temporary tablespace to complete a query, in which case additional I/O operations against the database storage occurs.