There are cases when a full plan is not needed. For example, if the only problem with a query plan is that the optimizer chooses a table scan instead of using a nonclustered index, the abstract plan can specify only the index choice, and leave the other decisions to the optimizer.
The optimizer could choose a table scan of t3 rather than using i_c31 for this query:
select *
from t1, t2, t3
where c11 = c21
and c12 < c31
and c22 = 0
and c32 = 100
The following plan, as generated by the optimizer, specifies join order t2, t1, t3. However, the plan specifies a table scan of t3:
(g_join
(i_scan i_c22 t2)
(i_scan i_c11 t1)
(t_scan t3)
)
This full plan could be modified to specify the use of i_c31 instead:
(g_join
(i_scan i_c22 t2)
(i_scan i_c11 t1)
(i_scan i_c31 t3)
)
However, specifying only a partial abstract plan is a more flexible solution. As data in the other tables of that query evolves, the optimal join order can change. The partial plan can specify just one partial plan item. For the index scan of t3, the partial plan is simply:
(i_scan i_c31 t3)
The optimizer chooses the join order and the access methods for t1 and t2.
