Abstract plans need to name all of a query’s tables in a non-ambiguous way, such that a table named in the abstract can be linked to its occurrence in the SQL query. In most cases, the table name is all that is needed. If the query qualifies the table name with the database and owner name, these are also needed to fully identify a table in the abstract plan. For example, this example used the unqualified table name:
select * from t1
The abstract plan also uses the unqualified name:
(t_scan t1)
If a database name and/or owner name are provided in the query:
select * from pubs2.dbo.t1
Then the abstract plan must also use the qualifications:
(t_scan pubs2.dbo.t1)
However, the same table may occur several times in the same query, as in this example:
select * from t1 a, t1 b
Correlation names, a and b in the example above, identify the two tables in SQL. In an abstract plan, the table operator associates each correlation name with the occurrence of the table:
( g_join
( t_scan ( table ( a t1 ) ) )
( t_scan ( table ( b t1 ) ) )
)
Table names can also be ambiguous in views and subqueries, so the table operator is used for tables in views and subqueries.
For subqueries, the in and subq operators qualify the name of the table with its syntactical containment by the subquery. The same table is used in the outer query and the subquery in this example:
select * from t1 where c11 in (select c12 from t1 where c11 > 100)
The abstract plan identifies them unambiguously:
( g_join
( t_scan t1 )
( i_scan i_c11_c12 ( table t1 ( in ( subq 1 ) ) ) )
)
For views, the in and view operators provide the identification. The query in this example references a table used in the view:
create view v1 as select * from t1 where c12 > 100
select t1.c11 from t1, v1
where t1.c12 = v1.c11
Here is the abstract plan:
( g_join
( t_scan t1 )
( i_scan i_c12 ( table t1 ( in ( view v1 ) ) ) )
)
