SQL/MX 2.x Query Guide (G06.24+, H06.03+)

HP NonStop SQL/MX Query Guide—523728-003
8-1
8 Parallelism
Using SQL/MX parallel processing within a large query maximizes the efficiency and
performance of such queries. Parallel execution can assume multiple forms. You can
partition the data or the query execution (or both) and then process the obtained
partitions in parallel. This section describes the types of parallelism supported by
SQL/MX.
•
Types of Parallelism in SQL/MX on page 8-1
•
Parallel Execution Principles on page 8-2
•
Parallel Plan Generation on page 8-6
•
Explaining Parallel Plans on page 8-12
•
Influencing Parallel Plans on page 8-24
The SQL/MX optimizer examines both parallel and nonparallel execution plans and
chooses the plan that has the lowest total cost, even if it is a nonparallel plan.
Good candidates for parallelism are operators that return few rows, after processing
large amounts of data, against tables that are partitioned. A good candidate for a
parallel plan is a query such as SELECT COUNT(*) FROM T1,T2 WHERE
T1.A=T2.B. In this example, large amounts of data must be retrieved, and an
aggregate must be computed. However, only one row is returned at the end of the
query.
Types of Parallelism in SQL/MX
•
Partitioned Parallelism
•
Pipelined Parallelism
•
Independent Parallelism
The SQL/MX architecture provides pipelined and independent parallelism without
special processing. An individual query plan produced by SQL/MX can contain any
combination of pipelined, independent, or partitioned parallelism.
Partitioned Parallelism
Partitioned parallelism, the main type of parallelism in SQL/MX, is the ability to divide
the data to be processed into partitions (fractions) and to work on each one in parallel.
In a partitioned parallel plan, multiple operators all work on the same plan. Results are
merged by using multiple pipelines, enabling SQL/MX to preserve the sort order of the
input partitions. Partitioning is also called data parallelism because the data is the unit
that gets partitioned into independently executable fractions. Partitioned parallelism
can occur in DAM processes (called DAM parallelism) and ESP processes (called ESP
parallelism). See DAM and ESP Parallelism on page 8-5.