SQL/MX 2.x Query Guide (G06.24+, H06.03+)

Contents

HP NonStop SQL/MX Query Guide—523728-003

2. Accessing SQL/MX Data (continued)

Controlling the Number of Key Columns Used by MDAM 2-17

MDAM’s Use of DENSE and SPARSE Algorithms 2-17

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-6

Collecting Statistics for Multiple Columns 3-7

Testing the Results of UPDATE STATISTICS 3-7

Testing the Results for SQL/MP Tables 3-7

Testing the Results for SQL/MX Tables 3-8

4. Reviewing Query Execution Plans

Displaying Execution Plans 4-1

Using the EXPLAIN Function 4-1

Using the DISPLAY_EXPLAIN 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 DISPLAY_EXPLAIN to Review the Execution Plan 4-6

Optimization Tips 4-9

Verifying DAM Access 4-13

Using the Visual Query Planner

4-13

Visual Query Planner Requirements

4-13

Getting Help for Visual Query Planner 4-14

Graphically Displaying Execution Plans 4-14

Reviewing Run-Time Statistics 4-20

Simple Query Example 4-21

Using Measure 4-21

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