Slideshow:
The Physical Query plan
The nodes in the physical query plan are physical query operators
The Physical Query Operators
Examples:
(We will learn how to implement these operators efficiently soon...)
The Physical Query Operators
More examples:
(We will learn how to implement these operators efficiently soon...)
- 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 = the programs (algorithms) that implement an (Relational Algebra) operation
- 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
- 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)
- Some implementations
of the operator
are very IO efficient
but
require
a large amount of buffers (= memory)
- A
physical plan operator
can have
different
implementations:
- 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)
- If you don't have
enough memory buffers,
the system will
trash
- A computer system has a
limited amount of
buffers (= memory)