Databases Reference
In-Depth Information
The database will also automatically decide whether an object being accessed should be
held in the buffer cache (SGA) to speed parallel execution. Oracle refers to this as
in-
memory parallel execution
. We describe the SGA and memory resources later in this
chapter.
Partition-Based Parallelism
A small subset of Oracle's parallel functionality is based on the number of partitions or
subpartitions accessed by the statement to be parallelized. For block-range parallelism,
the piece of data each PE process works on is a range of blocks. For partition-based
parallelism, the pieces of data that drive parallelism are partitions or subpartitions of a
table. The operations in which parallelism is based on the number of partitions or sub‐
partitions include the following:
• Updates and deletes
• Index scans
• Index creation and rebuilds on partitioned tables
Parallelism for partitions and subpartitions of a table
Oracle supports parallel Data Manipulation Language (DML), or the ability to execute
INSERT, UPDATE, and DELETE statements in parallel. This type of parallelism im‐
proves the performance of large bulk operations (for example, an update to all the rows
of a very large table).
Since Oracle8
i
, the degree of parallelism for updates and deletes is tied to the number
of partitions or subpartitions involved. A table with 12 partitions (for example, one
partition for each month of the year) can have a maximum number of 12 PEs for an
update or delete. An update to only one month of data would have no parallelism because
it involves only one partition. If the table were created using Oracle's composite parti‐
tioning (for example, with 4 hash subpartitions by PRODUCT_ID within each month),
the maximum degree of parallelism for the entire table would be 48, or 12 partitions
with 4 subpartitions each. An update to one month of data could have a degree of 4
because each month contains 4 hash subpartitions. If the table is not partitioned, Oracle
cannot perform updates or deletes in parallel.
Oracle can also execute index creation, index rebuilds, and index scans for partitioned
indexes in parallel using the same semantics as parallel DML: one PE process per par‐
tition or subpartition of the index.
Fast full-index scans for nonpartitioned tables
People often assume that the Oracle Database can parallelize index scans only if the
target index is partitioned. However, Oracle has the ability to perform parallel index
scans on nonpartitioned indexes for certain cases. If the index scan operation were
