SQL/MP Query Guide

Contents
HP NonStop SQL/MP Query Guide—524488-003
vi
6. Analyzing Query Performance
6. Analyzing Query Performance
Guidelines for Tuning Queries 6-2
Preparing Your Queries 6-3
Using DISPLAY STATISTICS 6-3
Simple Query Example 6-4
Simple Query With ORDER BY Example 6-6
Using Measure 6-7
Process Execution 6-7
Statement Execution 6-7
Evaluating Measure Data 6-8
Using EXPLAIN 6-8
Generating an EXPLAIN Plan 6-9
Interpreting an EXPLAIN Plan 6-12
Reviewing Sample EXPLAIN Plans 6-16
EXPLAIN Plan for Simple SELECT 6-16
EXPLAIN Plan for Primary Access 6-18
EXPLAIN Plan for Index-Only Access 6-19
EXPLAIN Plans for Bounded Predicates 6-20
Query With Lower Bound 6-20
Query With Lower and Upper Bound 6-22
EXPLAIN Plan for Key Predicates 6-23
EXPLAIN Plan for DISTINCT 6-24
EXPLAIN Plan for ORDER BY 6-25
EXPLAIN Plans for GROUP BY 6-26
SELECT With GROUP BY Using a Serial Plan 6-26
SELECT With GROUP BY Using a Parallel Plan 6-28
EXPLAIN Plans for Subqueries 6-29
Noncorrelated Subquery 6-29
Correlated Subquery 6-31
EXPLAIN Plans for CASE 6-33
CASE With Multiple Conditions 6-33
CASE With Aggregates 6-34
CASE for Finding the Highest Value in a Row 6-35
CASE for Converting Long, Narrow Tables Into Short, Wide Ones 6-36
CASE for Ignoring the Largest and Smallest Values in a Set 6-38
EXPLAIN Plans for String Functions 6-39
SUBSTRING 6-39
TRIM and CHAR_LENGTH 6-40
EXPLAIN Plans for MDAM 6-42