6.1 Query Optimization Overview

Overview :

When compared to other database systems, query optimization is a strength of the relational systems. It can be said so since relational systems by themselves do optimization to a large extent unlike the other systems which leave optimization to the programmer. Automatic optimization done by the relational systems will be much more efficient than manual optimization due to several reasons like :

• uniformity in optimization across programs irrespective of the programmer's expertise in optimizing the programs.
• system's ability to make use of the knowledge of internal conditions (eg: volume of data at the time of querying) for optimization. For the same query, such conditions may be different at different times of querying. (In a manual system, this knowledge can be utilised only if the query is re-written each time, which is not practically possible.)
• system's ability to evaluate large number of alternatives to find the most efficient query evaluation method.

In this chapter we shall look into the process of automatic query optimization done by the relational systems.