SQL/MP Query Guide

Contents
HP NonStop SQL/MP Query Guide—524488-003
iv
3. Improving Query Performance Through Query
Design (continued)
3. Improving Query Performance Through Query
Design (continued)
Noncorrelated Subquery 3-53
Avoiding Full Table Scans 3-54
Minimizing Sort Costs for Ordering and Grouping Operations 3-54
Sort Operations 3-55
Optimizing Combinations of Clauses 3-57
Using Indexes 3-58
Writing Efficient Programmatic Statements 3-60
Single-Row and Multiple-Row SELECT Statements 3-60
Multiple-Row (Cursor) SELECT Statements 3-60
Update and Insert Operations 3-60
Decision Support Considerations 3-61
Online Transaction Processing Considerations 3-62
Batch Considerations 3-63
4. Improving Query Performance With Environmental Options
Keeping Statistics Current 4-2
Optimizing the Access Path 4-4
Primary Access 4-4
Alternate-Index Access 4-5
Using the CONTROL QUERY Directive 4-6
Selecting an Access Path When an Index Is Not Available 4-7
Understanding Unexpected Access Paths 4-8
Specifying an Access Path 4-11
Requesting Parallel Processing 4-13
Using the CONTROL EXECUTOR Directive
4-13
How Parallel Processing Is Implemented
4-14
Requesting Parallel Operations on Partitioned Data 4-14
Specifying Access Option and Lock Characteristics 4-16
Access Option 4-16
Lock Mode 4-19
Lock Granularity 4-19
Waiting For Locks 4-20
Performance Implications 4-20
Reducing Messages With Buffering Options 4-21
Types of Buffering 4-21
Single-Row Access 4-22
Real Sequential Block Buffering (RSBB) 4-23