# Physical Operators for Access Plans

Notation

 R A relation O An operand (collection of records generated by physical operator) OE External operand OI Internal operand C A condition CJ A join condition Idx An index {Ai} A set of attributes

Operators to scan tables

 TableScan (R) Scan of R IndexScan (R, Idx) Scan of R with index Idx SortScan (R, {Ai}) Ordered scan of R on attributes {Ai}

Operators for projection

 Project (O, {Ai}) Projection of O records without duplicate elimination Distinct (O) Duplicate elimination of the ordered records of O

Operators for restriction

 Filter (O, C) Restriction without indexes of O records IndexFilter (R, Idx,C) Restriction with an index of R records IndexOnlyFilter (R, Idx,{Ai},C) Restriction of R using only the index Idx. The operator returns the values of attributes {Ai} for each record of R that satisfies C, with {Ai} a subset of the attributes on which the index is defined.

Operator for sorting

 Sort (O, {Ai}) Sorting O records on {Ai}

Operator for grouping

 GroupBy (O, {Ai}, F) Grouping of O records on attributes {Ai} using the aggregate functions in F. The set F contains the aggregate functions used in the SELECT and in the HAVING clauses. The operator returns records with attributes {Ai} and the aggregate functions in F. The records of O are sorted on {Ai}

Operators for set operations

 Union (OE,OI) Except (OE,OI) Intersect (OE,OI) Set operations with the operand records sorted and without duplicates UnionAll (OE,OI) Union without duplicates elimination

Operators for joins

 NestedLoop (OE,OI, CJ) Join with nested loop PageNestedLoop (OE,OI, CJ) Join with page nested loop IndexNestedLoop (OE,OI, CJ) Join with index nested loop. the internal operand OI is an IndexFilter(R, Idx, CJ'), an IndexOnlyFilter (R, Idx, {Ai}, CJ') or a Filter(O, C') with operand an IndexFilter or an IndexOnlyFilter. For each record r of OE, the condition CJ' of an IndexFilter or an IndexOnlyFilter is the join condition with the attributes of OE replaced by their values in r. MergeJoin (OE,OI, CJ) Join with merge-join. The operand records are sorted on join attributes, a key in OE.