Chapter 21. The Optimizer

IN THIS CHAPTER

• Physical Data Independence

• How the Optimizer Works

• Filter Factors

• Access Path Strategies

• Other Operations Performed by the Optimizer

• Access Path Strategy Compatibility

The optimizer is the heart and soul of DB2. Before any SQL statement is run, the optimizer analyzes the SQL and determines the most efficient access path available for satisfying the statement. It accomplishes this by parsing the SQL statement to determine which tables and columns must be accessed. It then queries statistics stored in the DB2 Catalog to determine the best method of accomplishing the tasks necessary to satisfy the SQL request.

Statistics used by the optimizer include information about the current status of the tables, indexes, columns, and tablespaces (including partitioning information) that need to be accessed. The optimizer plugs this information into a series of complex formulas that it uses as it builds optimized access paths, as shown in Figure 21.1. In addition to the DB2 Catalog statistics, the optimizer will take into account other system information, such as the CPU being used and the size of your buffer pools. This approach allows the optimizer to estimate the number of rows that qualify for each predicate, and then use the proper algorithm to access the required data.

Figure 21.1. The DB2 optimizer.

The optimizer basically works like an expert system. An expert system is a set of standard rules that when combined with situational data can return an expert opinion. For example, a medical expert system takes the set of rules determining which medication is useful for which illness , combines it with data describing the symptoms of ailments, and applies that knowledge base to a list of input symptoms. The DB2 optimizer renders expert opinions on data retrieval methods for SQL queries based on the situational data housed in the DB2 Catalog. In this chapter, you discover the methods and strategies used by the optimizer as it creates optimized access paths for SQL statements.