The abstract plan language is a relational algebra that uses these operators:
distinct – a logical operator describing duplicates elimination.
distinct_sorted – a physical operator describing available ordering-based duplicates elimination.
distinct_sorting – a physical operator describing sorting-based duplicates elimination.
distinct_hashing – a physical operator describing hashing-based duplicates elimination.
group – a logical operator, describing vector aggregation.
group_sorted – a physical operator describing the available ordering-based vector aggregation.
group_hashing – a physical operator describing hashing-based vector aggregation.
group_inserting – a physical operator describing clustered index insertion-based vector aggregation.
join – the generic join and a high-level logical join operator that describes inner, outer and existence joins, using nested-loop joins, merge joins, or hash joins.
nl_join – specifying a nested-loop join, including all inner, outer, and existence joins.
m_join – specifying a merge join, including inner and outer joins.
h_join – specifying a hash join, including all inner, outer, and existence joins.
union – a logical union operator. It describes both the union and the union all SQL constructs.
append_union_all – a physical operator implementing union all. It appends the child result sets, one after the other.
merge_union_all – a physical operator implementing union all. It merges the child result sets on the subset of the projection that is ordered in each child, and preserves that ordering.
merge_union_distinct – a physical operator implementing union [distinct]. A merge-based duplicates removal algorithm.
hash_union_distinct – a physical operator implementing union [distinct]. A merge-based duplicates removal algorithm.
scalar_agg – a logical operator, describing scalar aggregation.
scan – a logical operator that transforms a stored table in a flow of rows, an abstract plan derived table. It allows partial plans that do not restrict the access method.
i_scan – a physical operator implementing scan. It directs the optimizer to use an index scan on the specified table.
t_scan – a physical operator implementing scan. It directs the optimizer to use a full table scan on the specified table.
m_scan – a physical operator implementing scan. It directs the optimizer to use a multiindex table scan on the specified table, either index union, index intersection, or both.
store – a physical operator describing the materialization of an abstract plan derived table in a stored worktable.
store_index – a physical operator describing the materialization of an abstract plan derived table in a clustered index stored worktable; the optimizer chooses the useful key columns.
sort – a physical operator describing the sorting of an abstract plan derived table; the optimizer chooses the useful key columns.
nested – a filter describing the placement and structure of nested subqueries.
xchg – a physical operator describing the on-the-fly repartitioning of an abstract plan derived table. The abstract plan gives the target degree, but the optimizer chooses the useful target partitioning.
These additional abstract plan keywords are used for grouping and identification:
sequence – groups the elements when a sequence requires multiple steps.
hints – groups a set of hints for a partial plan.
prop – introduces a set of scan properties for a table: prefetch, lru|mru and parallel.
table – identifies a table when correlation names are used, and in subqueries or views.
work_t – identifies a worktable.
in – used with table to identify tables named in a subquery (subq) or view (view).
subq – used under the nested operator to indicate the attachment point for a nested subquery, and to introduce the subqueries’ abstract plan.
All legacy abstract plan operators, such as g_join, are still accepted for their new counterparts.
