SQL/MP Query Guide

Contents

HP NonStop SQL/MP Query Guide—524488-003

iii

2. The Optimizer (continued)

Processor Assignment by the SQL/MP Optimizer and Executor for Executor Server

Processes (ESPs) 2-5

3. Improving Query Performance Through Query Design

Selecting Columns for Faster Data Access 3-2

Preparing Queries 3-3

How the Optimizer Processes Predicates 3-4

Classification of Predicates 3-4

Transformation of Predicates 3-4

Evaluation of Predicates 3-12

Writing Efficient Predicates 3-15

Positioning With Key Predicates 3-16

Specifying Join Predicates 3-18

Specifying Multivalued Predicates 3-19

Controlling the Expansion of Predicates 3-20

Using OR Operators in Predicates 3-22

Using LIKE Predicates 3-24

How the Optimizer Processes Join Operations 3-24

Nested Join 3-25

Sort Merge Join 3-25

Key-Sequenced Merge Join 3-27

Hash Join 3-29

Determining a Join Strategy 3-34

Writing Efficient Joins 3-39

Using Indexes 3-40

Eliminating Implicit Joins

3-40

Adding Join Predicates

3-41

Using Joins Instead of Subqueries 3-41

Specifying a Join Method 3-43

Specifying a Join Sequence 3-45

How the Optimizer Processes Aggregates and Group-By Operations 3-46

MIN and MAX Optimization 3-46

Evaluation by the Disk Process 3-47

Evaluation by the Executor Component 3-50

Hashed Aggregation and Grouping 3-50

Sorted GROUP BY Operation 3-51

Optimizing Subqueries 3-51

Correlated Subquery 3-51