Abstract plans allow you to specify the desired execution plan of a query. Abstract plans provide an alternative to the session-level and query level options that force a join order, or specify the index, I/O size, or other query execution options. The session-level and query-level options are described in Chapter 8, “Creating and Using Abstract Plans.”
There are several optimization decisions that cannot be specified with set commands or clauses included in the query text. Some examples are:
Algorithms that implement a given relational operator; for example, NLJ versus MJ versus HJ or GroupSorted versus GroupHashing versus GroupInserting
Subquery attachment
The join order for flattened subqueries
Reformatting
In many cases, including set commands or changing the query text is not always possible or desired. Abstract plans provide an alternative, more complete method of influencing optimizer decisions.
Abstract plans are relational algebra expressions that are not included in the query text. They are stored in a system catalog and associated to incoming queries based on the text of these queries.
