For most SQL queries, there are many possible query execution plans. SQL describes the desired result set, but does not describe how that result set should be obtained from the database. Consider a query that joins three tables, such as this:
select t1.c11, t2.c21 from t1, t2, t3 where t1.c11 = t2.c21 and t1.c11 = t3.c31
There are many different possible join orders, and depending on the indexes that exist on the tables, many possible access methods, including table scans, index scans, and the reformatting strategy. Each join may use either a nested-loop join or a merge join. These choices are determined by the optimizer’s query costing algorithms, and are not included in or specified in the query itself.
When you capture the abstract plan, the query is optimized in the usual way, except that the optimizer also generates an abstract plan, and saves the query text and abstract plan in sysqueryplans.
