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

Contents
HP NonStop SQL/MX Release 3.1 Query Guide—663851-001
iii
5. Forcing Execution Plans
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
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
7. SQL/MX Operators
Operators 7-5
BLOCKED_UNION Operator
7-5
CALL Operator 7-6
TP663851.fm Page iii Monday, October 17, 2011 11:48 AM