P. Gassner, G. Lohman, B. Schiefer, and Y. Wang.
Query optimization in the IBM DB2 family.
ACM Transactions on Data Engineering, 16(4):4-18, December 1993.
Presented by: Rangappa
A query typically has many possible execution strategies(plan), and the
process of selecting a suitable strategy(plan) for processing a query is
known as Query Optimization. Query optimizers usually select a plan by
modelling the estimated cost of performing alternative plans and then
choosing the least expensive amongst them.
Since 1970, IBM has been actively involved in research on relational
query optimization. This presentation deals with some of the "Industrial
strength" features of query optimizers in IBM DB2 family of products
that evolved from IBM's research. The topics that would be discussed at
the presentation are
(i) "Industrial Strength" optimization, where we discuss some aspects
of query optimization which are dealt by the optimizers.
(ii) Query transformations to SQL queries that simplify the query
representation and these transformations give the optimizer more
lattitude in selecting an optimal plan.
(iii) Access paths in DB2 optimizer such as access by index and access
by table scan. This section also discusses about join execution
stratergies which the optimizer considers.
(iv) Algorithms for alternative join sequences(join orders) which
determines the algorithmic complexity of query optimization.
(v) Some aspects of the models for cardinality and cost estimation.
(vi) Some advanced features of the DB2 optimizers. In this section we
discuss techniques to avoid expensive operations, modelling advanced
data manager features and other optimization issues.
(vii) Lastly a very brief discussion about run-time query optimization.
The above topics are discussed with reference to the IBM DB2 family of relational database products.