The syntax for CONTROL QUERY SHAPE provides the ability to force certain types of joins for parallel plans, as described next. For more information on Type1 (matching partition algorithm) and Type2 (parallel access to the inner table) joins, see Section 8, Parallelism.
•
Forcing a Type1 joinThe matching partitions algorithm can be forced by specifying Type1 as part of the join specification. To force a Type1 join without repartitioning, all criteria must match for both tables. To force a Type1 join when the table criteria does not match, Figure 5-5. Query Tree for the Forced Plan
root hash_partial_groupby_root split_top partition_access hash_partial_groupby_leaf file_scan DAM fragment Master Executor VST614.vsd
Forcing Execution Plans
HP NonStop SQL/MX Query Guide —640323-001 5- 15
Forcing Parallel Plans
use a CUT for the children, or use an ESP_EXCHANGE above the children for any necessary repartitioning.
This example shows forcing a Type1 join:
CONTROL QUERY SHAPE ESP_EXCHANGE( MERGE_JOIN( EXCHANGE(SCAN('DEPT')), EXCHANGE(SCAN('EMP')), TYPE1) );
The example uses the logical specification for the lower exchange operators to enable the optimizer to choose parallel access (SPLIT_TOP_PA operator) or serial access (PARTITION_ACCESS operator), as shown in Figure 5-6.
For more information about Type1 joins, see Parallelism on page 8-1.
•
Forcing a Type2 joinThe join with parallel access to the inner table algorithm can be forced by using Type2 as a part of the join specification. To force a Type2 nested join, the left child must be partitioned. To force a Type2 hash join, the left child must be partitioned, and you need to specify either CUT or ESP_EXCHANGE above the right child to handle the broadcast replication.
The next statement forces a Type2 hash join, as shown in Figure 5-7 on page 5-16.
CONTROL QUERY SHAPE ESP_EXCHANGE( HYBRID_HASH_JOIN ( EXCHANGE(SCAN('DEPT')), ESP_EXCHANGE(EXCHANGE(SCAN('EMP'))), TYPE2) );
Figure 5-6. Logical Specification and Lower Exchange Operators
root esp exchange
merge_join
exchange exchange scan 'DEPT' scan 'EMP'
Forcing Execution Plans
HP NonStop SQL/MX Query Guide —640323-001 5- 16
Forcing Parallel Plans
For more information about Type2 joins, see Parallelism on page 8-1.
•
Deferring to the optimizer to choose exchange or sort operatorsIn the preceding forced join examples, you needed to specify the choice of
exchange and sort operators as part of the CONTROL QUERY SHAPE statement. To eliminate this step and simplify the process of writing a valid CONTROL
QUERY SHAPE statement, you can use one of the options listed below with the CONTROL QUERY SHAPE statement. Using these options enables you to focus on join orders, join types, and other plan types while deferring the choice of exchange or sort operators to the optimizer:
°
IMPLICIT EXCHANGE enables the optimizer to add exchange nodes at any location necessary to make the CONTROL QUERY STATEMENT valid. The optimizer chooses the optimal placement of the exchange nodes. You can still explicitly add in exchange nodes and the compiler must add them.°
IMPLICIT SORT enables the optimizer to add sort nodes at any location necessary to make the CONTROL QUERY STATEMENT valid. The optimizer chooses the optimal placement of the sort nodes.You can still explicitly add in sort nodes and the compiler must add them.°
IMPLICIT EXCHANGE_AND_SORT enables the optimizer to add both exchange and sort nodes at any location necessary to make the CONTROL QUERY STATEMENT valid. The optimizer chooses the optimal placement of the exchange and sort nodes. You can still explicitly add in enforcers and the compiler must add them.Figure 5-7. Type2 Hash Join
root
hybrid_hash_join
scan 'DEPT' exchange scan 'EMP' exchange esp_exchange
esp_exchange
Forcing Execution Plans
HP NonStop SQL/MX Query Guide —640323-001 5- 17
Forcing Parallel Plans
This statement enables the optimizer to add exchange nodes:
CONTROL QUERY SHAPE IMPLICIT EXCHANGE HYBRID_HASH_JOIN (
SCAN('DEPT'), SCAN('EMP'), TYPE2);
Forcing Execution Plans
HP NonStop SQL/MX Query Guide —640323-001 5- 18
HP NonStop SQL/MX Query Guide —640323-001 6 -1
6
Query Plan Caching
Use the information in this section to understand query plan caching:
•
Types of Cacheable Queries on page 6-2•
Choosing an Appropriate Size for the Query Cache on page 6-6•
Query Plan Caching Statistics on page 6-6•
SYSTEM_DEFAULTS Table Settings for Query Plan Caching Attributes on page 6-7Overview
Query Plan Caching is a feature of the SQL/MX compiler that provides the ability to cache the plans of certain queries. This feature improves performance when the plan can be produced from the cache rather than through a full compilation. The
performance improvement is typically 60 to 80 percent (compile time) for simple TP-style queries.
Certain default settings used with the CONTROL QUERY DEFAULT statement apply to query plan caching. For more information, see SYSTEM_DEFAULTS Table Settings for Query Plan Caching Attributes on page 6-7.
The query plan caching feature has been designed to operate transparently. No SQL/MX application source code changes are required. When given a query, NonStop SQL/MX produces the same plan with or without query caching. This correctness requirement implies that the SQL/MX compiler honors CONTROL QUERY DEFAULT and CONTROL TABLE statements even when query caching is active. To illustrate this behavior, suppose that:
•
The table timeout setting for table T is set to infinite (-1).•
The SQL/MX compiler is asked to compile “SELECT * FROM T”•
The SQL/MX compiler compiles and caches a plan for “SELECT * FROM T”•
A CONTROL TABLE statement changes the timeout setting for table T to fiveseconds
•
The SQL/MX compiler is asked to compile “SELECT * FROM T” againThe SQL/MX compiler cannot use the previously cached plan for “SELECT * FROM T” because the plan has an infinite timeout setting for Table T. If the compiler were to use the cached plan, the compiler would effectively ignore the CONTROL TABLE
statement that changed the timeout for Table T to five seconds. Therefore, the compiler can compile and cache one or more plans for one query with each plan associated with a different set of control settings.
A query that is compiled repeatedly, each time with a new set of control settings, does not result in a cache hit. A query that is compiled subsequently, with a set of control
Query Plan Caching
HP NonStop SQL/MX Query Guide —640323-001 6 -2