SQL/MX 3.x Query Guide (H06.22+, J06.11+)

HP NonStop SQL/MX Query Guide—640323-001
2
4. Reviewing Query Execution Plans
Performance Issues and Accuracy in Sampling 3-7
Collecting Statistics for Multiple Columns 3-8
Testing the Results of UPDATE STATISTICS 3-8
Testing the Results for SQL/MP Tables 3-8
Testing the Results for SQL/MX Tables 3-9
4. Reviewing Query Execution Plans
Displaying Execution Plans 4-1
Using the EXPLAIN Function 4-1
Using the EXPLAIN Statement Shortcut 4-2
Using the Visual Query Planner 4-2
The Optimizer and Executor 4-3
Description of the EXPLAIN Function Results 4-3
Displaying Selected Columns of the Execution Plan 4-5
Extracting EXPLAIN Output From Embedded SQL Programs 4-6
Using the EXPLAIN Statement to Review the Execution Plan 4-6
Optimization Tips 4-9
Verifying DAM Access 4-13
Using the Visual Query Planner 4-14
Visual Query Planner Requirements 4-14
Getting Help for Visual Query Planner 4-14
Graphically Displaying Execution Plans 4-14
Reviewing Run-Time Statistics 4-21
Simple Query Example 4-22
Using Measure 4-22
5. Forcing Execution Plans
Why Force a Plan? 5-1
Checklist for Forcing Plans 5-2
Displaying the Optimized Plan 5-2
Reviewing the Optimized Plan 5-3
Translating the Operator Tree to Text Format 5-5
Using SHOWSHAPE and SET SHOWSHAPE to View the Text Format 5-5
Using Visual Query Planner to Get the Shape 5-6
Manually Writing the Shape 5-7
Writing the Forced Shape Statement 5-8
Scope of CONTROL QUERY SHAPE 5-8
Shaping Portions of an Operator Tree 5-9
Using Logical and Physical Specifications 5-9
Forcing Shapes on Views 5-10