### Physical Operators used in Physical Query Plans

• Physical query plan

• Physical query plan:

• Physical query plan = a tree of "physical query" operators that accomplishes the execution of a query

• Physical query operators

• Physical query operators:

 Physical query operators = the programs (algorithms) that implement an (Relational Algebra) operation

• Physical Operators used

• The follow is a list of the operators that can be used in a Physical Query Plan:

Symbol Name of operator Operation
table-scan(R) Table scan Reads blocks of the input relation R in - one block at a time.
index-scan(R) Index scan Reads blocks of the input relation R in using some index on the relation R
σcond Selection Selects the tuples that satisfies the condition cond
πattrs Projection Extracts the attributes in attrs from a tuple
Note: result of π is a bag (possible duplicates !!!)
cond Theta Join Joins 2 relations on the condition cond
Natural Join Equi-joins 2 relations on the attributes than have the same name
× Product Computes the (cartesian) product of 2 relations
δ Duplicate Elmination Removes the duplicate tuples from a bag of tuples (the result is a set)
γL Grouping Form gorups of tuples on common attribute values and apply the function L on each group
S Set Union Computes the set union of 2 sets (no duplicates)
B Bag Union Computes the bag union of 2 sets (allows duplicates)
S Set Intersection Computes the set intersection of 2 sets (no duplicates)
B Bag Intersection Computes the bag intersection of 2 sets (allows duplicates)
S Set Difference Computes the set difference of 2 sets (no duplicates)
B Bag Difference Computes the bag difference of 2 sets (allows duplicates)

• Note:

 We will discuss how (= algorithm) to implement each operator efficiently

• Implementation of the (Relational Algebra) operations

• Important facts:

• A physical plan operator can have different implementations:

 Some implementations of the operator are very IO efficient but require a large amount of buffers (= memory) Other implementations are less IO efficient but require a smaller amount of buffers (= memory)

• Resource allocation:

• A computer system has a limited amount of buffers (= memory)

• The amount of available buffers will determine which implementation of an operator that we can use to process the query

 If you don't have enough memory buffers, the system will trash Trashing = a state of the computer system where the system performs an execessive number of disk IO (because data can only be operated while in memory and there are insufficient memory buffers -- the system is swapping disk blocks very frequently)