Tuning the Database - Expert Oracle RAC Performance Diagnostics and Tuning - page 273

• Distribution of workload based on resource availability

• Accessing data from a database using indexes

• Tuning parameters to help improve performance

Data Partitioning
Partitioning of data objects not only provides opportunities for workload distribution and access optimization, but
also increases isolation and may therefore have the advantage of reducing maintenance windows, recovery times,
and the impact of failures.
Workload or I/O distribution improves query performance by allowing access to a subset of partitions, rather
than the entire table. Instead of all users' requests being funneled into one segment containing all the data, they are
now distributed or spread among the various data segments that contain this data.
Oracle provides different methods and criteria to partition the data. Based on the how data is stored
(business rules), volume of data, data distribution, cardinality, and more importantly how the data will be retrieved
back for analysis, etc., one or a combination of these methods can be used.
Partitioning is specified on a column or set of columns that becomes the partitioning key. This key will determine
placement of data into the various partitions. During DML operations, data is directed to the appropriate partition
based on the value of the partition key. A partition key

• Can consist of an ordered list of 1 to 16 columns.

• Can contain columns that are NULLABLE.

ROWID , or a column of type ROWID . • Cannot contain a LEVEL,

■ partitioning is an option that is included in the enterprise edition of the Oracle database product and is not
licensed for the standard edition. in either case, installation and using this data requires an additional license.

Note

Partitioning is useful for many different types of applications, particularly applications that manage large
volumes of data. OLTP systems often benefit from improvements in manageability and availability, while data
warehousing systems benefit from performance and manageability.
Oracle provides several partitioning methods:

• Range partitioning, where data is partitioned based on a range of column values. Each

partition is defined by a value list for the partition key.

• Hash partitioning, where records are assigned to partitions using a hash function on values

found in columns designated as the partitioning key.

• List partitioning, where each partition is defined with a predefined list of values from a specific

column, which is the partitioning key. Each partition contains rows that contain the value
contained in that column.

• Composite partitioning, which is the result of “subdividing” the range and hash or list partitioning.

Under composite partitioning, a table is first partitioned using range, called the primary partition,
and then each range partition is sub-partitioned using the hash or list value. A sub-partition of a
partition has independent physical entities, which may reside in different tablespaces.
Unlike range, list, and hash partitioning, with composite partitioning, sub-partitions, rather than
partitions, are units for backup, recovery, Parallel DML (PDML), space management, etc.
This means that a DBA can perform maintenance activities at the sub-partition level.