Slideshow:
Steps in the
Query Optimization process
Step 1: Convert an (SQL) query into a query parse tree (a.k.a.: parsing)
Steps in the
Query Optimization process
Step 2: Convert a parse tree into an initial logical query plan:
Steps in the
Query Optimization process
Step 3: Transform the initial query plan into an optimal query plan:
The Logical (Optimal) Query Plan
Steps in the
Query Optimization process
Step 4: select an Physical Query Operator (= implementation algorithm):
The Physical Query Plan
- Steps in
Query Optimization:
- Convert an
(SQL) query into
a query parse tree:
- Convert a
parse tree into
an
initial logical query plan:
- Transform the
initial query plan into an
optimal query plan:
The cost measure used to decide on which query plan is better is:
- Size (= # tuples) of all the intermediate result relations generated by the logical query plan
- Select the
Physical query operator (algorithm !)
for each
relational algebra operator in the
optimal query plan:
Note:
- The physical query operators are the algorithms that we have discussed above !!!
The cost measure used to decide on which query plan is better is:
- # disk IO operations
used by the physical operator (algorithm)
- Under the
condition that
there are sufficient number of
available buffers for
the algorithm !!!
- Depending on the
number of buffers
available,
we must also decide
other whether we can
pipeline the
result between
operators
Or: materialize the result on disk !!!!
- Depending on the
number of buffers
available,
we must also decide
other whether we can
pipeline the
result between
operators
- Convert an
(SQL) query into
a query parse tree:
- Logical Query Plan:
- Logical Query Plan = the optimal sequence of relational algebra operations to perform the query
- Physical Query Plan:
- Physical Query Plan = the optimal sequence of relational algebra algorithms to perform the query
- The Physical Query Plan consists of:
- Physical Operators
(= algorithms used to
execute some
relational algebra operation,
e.g., one-pass join,
index-join, etc)
- The order in which the
physical operations are
performed
(a tree)
- Way to
obtain the
input for
each physical operator:
- Pipleline (using iterator)
- Materialzation
- Physical Operators
(= algorithms used to
execute some
relational algebra operation,
e.g., one-pass join,
index-join, etc)