What is partitioning in Oracle and its benefits?

Answer 1

Oracle Partitioning enhances the manageability, performance, and availability of database applications. Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity.

Oracle provides a comprehensive range of partitioning schemes to address every database schema. Moreover, since it is entirely transparent in SQL statements, partitioning can be used with any application, from packaged OLTP applications to Data Warehouses.

The primary purpose of partitioning is faster query access. This is accomplished via partition pruning (elimination), a method where Oracle can query the data dictionary and determine the content or definition of a given partition without having to query that partition's data, as it otherwise would in a non-partitioned table. In this way, Oracle can very quickly exclude large portions of data before the query search begins and not have to search through certain partitions at all in order to resolve a query. Rather, very focused subsets of data can be quickly isolated to be further refined.