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

Contents
HP NonStop SQL/MX Release 3.2 Query Guide—663851-002
iii
5. Forcing Execution Plans
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
What Happens if No Plan Is Returned? 5-10
Migrating Forced Shapes From NonStop SQL/MP 5-10
Forcing Group By Operations to the Data Access Manager 5-10
Forcing Parallel Plans 5-13
Considerations for CONTROL QUERY SHAPE 5-17
6. Query Plan Caching
Overview 6-1
Types of Cacheable Queries
6-2
Examples of Cacheable Expressions
6-3
Examples of Queries That Are Not Cacheable
6-4
Choosing an Appropriate Size for the Query Cache 6-6
Query Plan Caching Statistics 6-6
SYSTEM_DEFAULTS Table Settings for Query Plan Caching Attributes 6-7
QUERYCACHE Function 6-9
QUERYCACHEENTRIES Function 6-11
Querying the Query Plan Caching Virtual Tables
6-13
Reviewing the Query Plan Caching Statistics With the DISPLAY_QC and
DISPLAY_QC_ENTRIES Commands 6-15
TP663851.fm Page iii Thursday, August 2, 2012 4:47 PM