SQL/MX 3.2 Query Guide (H06.25+, J06.14+)

Contents

HP NonStop SQL/MX Release 3.2 Query Guide—663851-002

2. Accessing SQL/MX Data

Access Methods 2-1

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

Specifying MDAM 2-12

Comparing MDAM With Single Subset Access

2-13

How MDAM Processes Queries

2-14

Influencing the Optimizer to Use MDAM 2-16

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

TP663851.fm Page ii Thursday, August 2, 2012 4:47 PM