SQL/MX 3.1 Query Guide (H06.23+, J06.12+)

Contents
HP NonStop SQL/MX Release 3.1 Query Guide—663851-001
ii
3. Keeping Statistics Current
Storage-Key Access 2-1
Index-Only Access 2-2
Alternate Index Access 2-4
Full Table Scan 2-5
Understanding Unexpected Access Paths 2-9
MultiDimensional Access Method (MDAM) 2-13
Specifying MDAM 2-13
Comparing MDAM With Single Subset Access 2-14
How MDAM Processes Queries 2-15
Influencing the Optimizer to Use MDAM
2-17
Controlling the Number of Key Columns Used by MDAM
2-18
MDAM’s Use of DENSE and SPARSE Algorithms 2-18
3. Keeping Statistics Current
Histogram Statistics 3-1
Updating Histogram Statistics 3-2
Sampling and UPDATE STATISTICS 3-5
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
TP663851.fm Page ii Monday, October 17, 2011 11:48 AM