AQP join order

The SQE engine implements AQP join order recommendations in the following ways:

Subsequent to run

When each query completes, a fast check is done on key points of the query execution to compare actual selected records with the estimates. If there is a significant discrepancy, then a stand-alone task is notified to do a deeper analysis of the query execution.

The query plan and the execution statistics are passed to the task. A separate task is used for the in-depth analysis so the user job is not impacted while the deep analysis is done. Each step of the join is analyzed, looking for characteristics of starvation join. Starvation join shows a significant reduction in the number of rows produced compared to the previous step. The definition of what is considered significant depends on a number of factors.

If the criteria for starvation join are met, the actual number of records selected at key points of the query are compared to estimates. If there is a significant discrepancy between the actual and estimated record counts, the table at that join position is identified as a 'forced primary table'. This table is saved with the query plan in the system plan cache. When the query runs in the future, the optimizer retrieves the original plan from the system plan cache. The optimizer sees the forced primary table recommendation, and optimizes the query using this recommendation.

The forced primary recommendation is used in two ways by the optimizer:

• The forced primary table is placed first in the join order by the join order optimization strategy.
• The forced primary table is used by the strategy for LPG optimization. The preceding example is a star join since table T1 is joined to the other tables in the query. t1.c3 is the column used to join T1 to T4. If an index exists over this join column, then it might be advantageous to do preselection against table T1 using the records selected from table T4. The forced primary table recommendation is used as a hint for the optimizer to consider this technique.

Concurrent to run

The preceding logic to identify starvation join can also run in a thread in parallel to the executing query. The AQP handler thread is created for longer running queries. The thread monitors the query execution and can run the same logic described earlier against partial data from the query execution.

If the partial results show starvation join and significant differences with the record count estimates, the query is reoptimized in the thread. When the new plan is ready, the execution of the original plan is stopped and the new plan started. This scheme for correcting join problems 'on the fly' can only be carried out before any records are selected for the final result set.