Identifying tables

Abstract plans must name all of a query’s tables in a nonambiguous 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 uses the unqualified table name:

select * from t1

The abstract plan also uses the unqualified name, (t_scan t1). If a database name or owner name are provided in the query:

select * from pubs2.dbo.t1

The abstract plan must use 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:

(join 
        (t_scan (table (a t1))) 
        (t_scan (table (b t1))) 
)

You can also use a briefer abstract plan, which uses only the correlation names:

(join
  (t_scan a)
  (t_scan b)
)

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 the tables unambiguously:

(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:

     (join 
         (t_scan t1) 
         (i_scan i_c12 (table t1 (in (view v1))))
     )

In abstract plans generated by Adaptive Server, the view or subquery-qualified table names are generated only for the tables where they are needed to remove name ambiguity. For other tables, only the name is generated.

In abstract plans created by the user, view or subquery-qualified tables names are required in case of ambiguity; both syntaxes are accepted otherwise.